Change SQL mode for MariaDB in Fedora 27

After I upgraded my laptop to Fedora 27, I started experiencing some weird issues with most of the projects I am developing locally.  Trying to save anything into the database that involves dates, started throwing the following errors:

Error: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-11-30T13:30:48+02:00' for column 'timestamp' at row 1

A quick look around showed that Fedora 27 ships MariaDB v10.2, while the previous distribution version shipped MariaDB v10.1. Digging through the changes between the two versions didn’t help much, even though there is slight mention of the related change.

SQL_MODE has been changed; in particular, NOT NULL fields with no default will no longer fall back to a dummy value for inserts which do not specify a value for that field.

StackOverflow is much more helpful, as always.  These two threads – one and two – in particular, explained the changes and suggested the fix.  I had to either fix the projects I was working on, or modify my local configuration to use the old SQL mode.  This thread provided some more details, so the final solution was adding the following to the /etc/my.cnf and restarting the MariaDB service:

[mysqld]
sql-mode="NO_ZERO_IN_DATE,NO_ZERO_DATE"

And now we are back to normal.

Leave a Comment