ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value

Question

So i've recently completed an application for a study project. It's all good, and all I have left is putting the application to production.

I'm using MySQL with Node.js(I know, we don't like that, but someone's gotta try it). I have a socket that adds a chat message to the mysql Message Table, which contains the text, date time etc. The date time is set to new Date().

Now as I placed the application in a production server(reinstalling dependencies, mysql etc.), I suddenly get this error when I write messages:

Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2017-06-01T09:45:06.253Z' for column 'message_datetime' at row 1

I did not get that error in development, so I asked myself if I downloaded different versions of mysql... and I did:

Development:

mysql  Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.3

Production

mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

and the message table looks like this:

CREATE TABLE message ( message_id INT AUTO_INCREMENT, message_sender_id VARCHAR(80) NOT NULL, message_datetime DATETIME, message_text TEXT, message_chat_id INT NOT NULL, PRIMARY KEY(message_id), FOREIGN KEY(message_chat_id) REFERENCES chat(id) ON DELETE CASCADE ) ENGINE=InnoDB;

So what are the differences? Why is 'yyyy-mm-ddThh:mm:ss.%%%Z' suddenly not a valid date format? How do I fix this?

Thankful for any help!

Answer 1

Apparently, the datetime value is not a valid MySQL Datetime. But there is a work around modifying the Server SQL Modes.

For some reason, in my development server, the MySQL default mode configurations were completely removed. Therefore there were no restrictions on how I could insert the datetime.

mysql> select @@sql_mode;
    +------------+
    | @@sql_mode |
    +------------+
    |            |
    +------------+
    1 row in set (0.00 sec)

On the production server on the other hand, there was a ton of restrictions that told the mysql server what kinds of datetime formats to accept.

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

This is not a safe method, but I changed the MySQL restriction modes to no_engine_substitution, and voila, everything works like a charm (almost). You have to change the GLOBAL and SESSION modes for this to work.

The standard SQL mode is 'NO_ENGINE_SUBSTITUTION', so we'll put the mode to that. There are more modes you could add tough:

SET GLOBAL sql_mode = '<mode>';
SET SESSION sql_mode = '<mode>';

Now GLOBAL and SESSION mode should be set to NO_ENGINE_SUBSTITUTION

mysql> SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> SELECT @@SESSION.sql_mode;
+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

Answer 2

Same answer (given by @Jesper) works for error

ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

i.e. my

select @@GLOBAL.sql_mode; -- and
select @@SESSION.sql_mode;

gives

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

When I updated them to

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

my SQL inserts executed without a glitch

This error is because of Strict SQL Mode. So Only removing STRICT_TRANS_TABLES from sql_mode is enough. for example

SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';