ER_TRUNCATED_WRONG_VALUE: invalid date and time value
So, I recently completed a training project application. This is all good, and all I have left is to attach the application to production.
I'm using MySQL with Node.js (I know we don't like this, but someone should try it). I have a socket that adds a chat message to a mysql message table that contains text, date, etc. Date time is set to new Date()
.
Now that I have hosted the application on a production server (reinstalling dependencies, mysql, etc.), I suddenly get this error while writing messages:
Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2017-06-01T09:45:06.253Z' for column 'message_datetime' at row 1
I didn't get this 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
Products
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's the difference? Why is it 'yyyy-mm-ddThh:mm:ss.%%%Z'
suddenly not a valid date format? How to fix it?
Thanks for the help!
source to share
Obviously the datetime value is not a valid MySQL Datetime . But there is work to change the modes of the SQL server .
For some reason, on my development server, the default mode configurations in MySQL have been completely removed. Therefore, there was no restriction on how I could insert the date and time.
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
On the other hand, the production server had a lot of restrictions that told the mysql server which date and time 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
It's not a safe method, but I changed the MySQL constraint modes to no_engine_substitution
, and voila, everything works like no_engine_substitution
(almost). You must change the GLOBAL and SESSION modes for this to work.
The standard SQL mode is "NO_ENGINE_SUBSTITUTION", so we will set this mode. There are several modes you can hard-code:
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
GLOBAL and SESSION modes should now 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)
source to share