Er_truncated_wrong_value: Incorrect Datetime Value

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

Solution 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 |+------------+||+------------+1rowinset (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:

SETGLOBAL 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 |+------------------------+1rowinset (0.00 sec)

mysql>SETGLOBAL sql_mode ='NO_ENGINE_SUBSTITUTION';
mysql>SELECT @@GLOBAL.sql_mode;
+------------------------+| @@GLOBAL.sql_mode      |+------------------------+| NO_ENGINE_SUBSTITUTION |+------------------------+1rowinset (0.00 sec)

Solution 2:

Same answer (given by @Jesper) works for error

ERROR1292 (22007): Truncated incorrect DOUBLE value: ''

i.e. my

select @@GLOBAL.sql_mode; -- andselect @@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';
SETGLOBAL 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 SESSIONsql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';