2

It works with ADDTIME, but not when inserting.

MariaDB [(none)]> SELECT ADDTIME('2016-10-12T23:00:00Z', '1');
+--------------------------------------+
| ADDTIME('2016-10-12T23:00:00Z', '1') |
+--------------------------------------+
| 2016-10-12 23:00:01                  |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00Z');
ERROR 1292 (22007): Incorrect datetime value: '2016-10-12T23:00:00Z' for column 'start' at row 1

MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00');
Query OK, 1 row affected (0.00 sec)

Inserting with a trailing Z does work in another db that is 10.1.19. What has changed? It's valid ISO8601 as far as I know.

2

MariaDB does not recognize your date format. In previous versions it autocorrefted value for you, but starting from version 10.2.4 MariaDB changed SQL mode to stricter and raises error instead of warning for faulty SQL queries. If it's production, then temporary solution would be changing sql_mode to be not so strict. Change file /etc/my.cnf (on CentOS)

[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

And restart mariadb service. I suggest you changing your query for MariaDB to fully understand it. More info on sql_mode here: https://mariadb.com/kb/en/mariadb/sql-mode/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.