20

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!

Jesper
  • 2,044
  • 3
  • 21
  • 50
  • https://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'."_ – CBroe Jun 01 '17 at 10:31
  • 1
    _"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. – CBroe Jun 01 '17 at 10:32
  • @CBroe 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 – Jesper Jun 01 '17 at 10:37
  • 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. :) – reor Mar 21 '18 at 21:55

2 Answers2

22

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)
Jesper
  • 2,044
  • 3
  • 21
  • 50
  • You should accept your answer to show that you've found a solution. – RobG Jun 01 '17 at 22:38
  • @RobG well, I gotta wait 1 day & 4 hours to do that – Jesper Jun 02 '17 at 07:09
  • 2
    @Jesper Looks like you have s small typo here. The second command should be ```mysql> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';``` – Sanya Tobi Feb 18 '18 at 09:21
  • 1
    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... – jBuchholz Feb 19 '18 at 22:42
  • @Scorpioo590 how does one ALLOW_INVALID_DATES on their MySQL? – Angelos Pikoulas Feb 20 '19 at 21:51
  • As stated here: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html, you set the sql_mode with `SET GLOBAL sql_mode = ',...'` See also this post for a more detailed description: https://stackoverflow.com/questions/2317650/setting-global-sql-mode-in-mysql – jBuchholz Feb 21 '19 at 12:29
3

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';
Adeel Raza Azeemi
  • 571
  • 5
  • 16
  • 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';` ); ``` – jasenmichael May 03 '21 at 07:07