echothrust/echofish

Field 'updated_at' doesn't have a default value

tconstans opened this issue · 7 comments

Hello,

i had to tweak rsyslog logFormat template to fixed this message appearing in log. "updated_at" is explicitly set in template:

$template dbFormat,"INSERT INTO archive_bh (host, facility, priority, level, received_ts, program, msg,pid,tag,updated_at) VALUES ( '%fromhost-ip%', '%syslogfacility%', '%syslogpriority%','%syslogseverity%', '%timereported:::date-mysql%', TRIM('%programname%'), TRIM('%msg%'),'', '%syslogtag%', '%timereported:::date-mysql%' );\n",sql

fresh install of echofish 0.4 on RHEL6.5

Hello Thomas,

Upon further investigation it appears that MySQL changed the default SQL_MODE to be more restrictive on some newer versions (v5.6 i think includes that). What version of MySQL or MariaDB is included on RHEL6.5 ?

What is the output produced by the following query?

select @@session.sql_mode,@@global.sql_mode;

Also can you check your my.cnf for lines containing the sql_mode keyword?

The updated_at is supposed to take the '0000-00-00 00:00:00' datetime value. This field will only get updated when modifications take place on the logs (this functionality is to be included on later versions of echofish).

Thanks again for the report

Pantelis

Hello Pantelis,

My fault, i thought about this sql strict mode stuff.

I generated my mysql configuration file using percona wizard
(https://tools.percona.com/wizard) which enforced strict sql / strict
innodb settings.

They were turned on when i did the setup, i turned them off while
troubleshooting, but it still failed.

You should mention sql_mode recommended settings in installation
documentation.

Thank for your cooperation

Best regards

Le 23/04/2014 09:18, Pantelis a écrit :

Hello Thomas,

Upon further investigation it appears that MySQL changed the default
SQL_MODE to be more restrictive on some newer versions (v5.6 i think
includes that). What version of MySQL or MariaDB is included on RHEL6.5 ?

What is the output produced by the following query?

|select @@session.sql_mode,@@global.sql_mode;
|

Also can you check your my.cnf for lines containing the |sql_mode| keyword?

The |updated_at| is supposed to take the '0000-00-00 00:00:00' datetime
value. This field will only get updated when modifications take place on
the logs (this functionality is to be included on later versions of
echofish).

Thanks again for the report

Pantelis


Reply to this email directly or view it on GitHub
#6 (comment).

Thomas Constans
Services en informatique libre
T: 33(0)6 23 37 87 85
@: http://opendoor.fr
#: https://twitter.com/ThomasConstans

Yes you are right, this is going to be included on the docs.

I tested the wizard and it appears that it adds a lot of modes, if you're up for a test change the following line

sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

into

sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES

restart the mysqld and see if that eliminates the errors.

Pantelis

Le 23/04/2014 10:01, Pantelis a écrit :

|sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES
|

restart the mysqld and see if that eliminates the errors.

It does not, i still get the error message

Thomas Constans
Services en informatique libre
T: 33(0)6 23 37 87 85
@: http://opendoor.fr
#: https://twitter.com/ThomasConstans

Hi Thomas,

Pantelis supplied the percona-generated config, which i installed on a test server to pin down the options that cause this to happen (sql-mode and innodb-strict-mode). We managed to get it working with a minor schema modification:

ALTER TABLE archive_bh MODIFY COLUMN updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

Please test this fix and revert to the old rsyslog logFormat template.

regards,
George

Your fix works

Best regards

Le 23/04/2014 14:11, gadamo a écrit :

ALTER TABLE archive_bh MODIFY COLUMN updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

Thomas Constans
Services en informatique libre
T: 33(0)6 23 37 87 85
@: http://opendoor.fr
#: https://twitter.com/ThomasConstans

Thank you, this schema enhancement will be included in the next EF release.