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!

Dorsad

dev.mysql.com/doc/refman/5.7/en/datetime.html: "MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'." – Cystectomy

"I did not get that error in development" - that might more be an issue of server configuration (in regard to error tolerance) than that it actually worked and inserted valid dates into the database. – Cystectomy

@Cystectomy I didn't even know there is different error tolerances. I've used vagrant for my development environment, but put up the production server on digital ocean. Maybe some kind of update in the production server decreased the error tolerance then – Dorsad

Thanks for posting this question. I had imagined myself posting a similar question about the 'Z' from JavaScript/Angular being rejected by MySQL and had also resigned myself to saying something like '(I know, we don't like it, but someone's gotta try it.)'. Glad to see that was there too. :) – Pshaw

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)

Dorsad

You should accept your answer to show that you've found a solution. – Manlike

@Manlike well, I gotta wait 1 day & 4 hours to do that – Dorsad

@Dorsad Looks like you have s small typo here. The second command should be mysql> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; – Insole

I had the same problem like you but I also had to add ALLOW_INVALID_DATES in sql_mode for my app to work. But: although MySQL clearly detects the given dates as invalid it has no problem storing them correctly... – Tripura

@Scorpioo590 how does one ALLOW_INVALID_DATES on their MySQL? – Mesothorax

As stated here: dev.mysql.com/doc/refman/8.0/en/sql-mode.html, you set the sql_mode with SET GLOBAL sql_mode = '<sql_mode1>,<sql_mode2>...' See also this post for a more detailed description: #2318150 – Tripura

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';

Greasepaint

thanks this worked for me in an export to json from sqlite to mysql using knex, I added this knex.raw() ``` js knex.raw( SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; ); ``` – Justajustemilieu

Answer 3

I received a similar error, while trying to insert that value in db:

[Nest] 11568 - 12/30/2023, 9:08:27 PM ERROR [ExceptionsHandler] ER_TRUNCATED_WRONG_VALUE: Incorrect date value: '1991-06-22T22:00:00.000Z' for column 'birthday' at row 1 Error: ER_TRUNCATED_WRONG_VALUE: Incorrect date value: '1991-06-22T22:00:00.000Z' for column 'birthday' at row 1

Then I fixed it by creating a new date out of the received datetime string '1991-06-22T22:00:00.000Z':

var normalizedBday = new Date(birthday);

Durr