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.