MySQL cluster 5.6 doesn’t convert invalid TIMESTAMP value to ‘0000-00-00 00:00:00’

datetimeMySQLmysql-clustertimestamp

According to the document:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the
“zero” value of the appropriate type ('0000-00-00' or '0000-00-00
00:00:00').

but it doesn't happen with MySQL Cluster 5.6.

Here're the details:

Server version: 5.6.11-ndb-7.3.2-cluster-commercial-advanced-log MySQL Cluster Server – Advanced Edition (Commercial)

mysql> desc temp.banana1;
+--------+-----------+------+-----+---------------------+-----------------------------+
| Field  | Type      | Null | Key | Default             | Extra                       |
+--------+-----------+------+-----+---------------------+-----------------------------+
| f_time | timestamp | YES  |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| p_time | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+--------+-----------+------+-----+---------------------+-----------------------------+

mysql> insert into banana1 values('1970-01-01 00:00:00','1970-01-01 00:00:01');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'f_time' at row 1

Server version: 5.5.27-ndb-7.2.8-cluster-gpl-log MySQL Cluster Community Server (GPL)

mysql> desc temp.banana;
+--------+-----------+------+-----+---------------------+-----------------------------+
| Field  | Type      | Null | Key | Default             | Extra                       |
+--------+-----------+------+-----+---------------------+-----------------------------+
| f_time | timestamp | YES  |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| p_time | timestamp | YES  |     | 2012-10-31 13:00:00 |                             |
+--------+-----------+------+-----+---------------------+-----------------------------+

mysql> insert into banana values('1970-01-01 00:00:00','1970-01-01 00:00:00');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'f_time' at row 1 |
| Warning | 1264 | Out of range value for column 'p_time' at row 1 |
+---------+------+-------------------------------------------------+

mysql> select * from temp.banana;
+---------------------+---------------------+
| f_time              | p_time              |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+

UPDATE Fri Oct 4 16:43:35 ICT 2013

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

mysql> SET @@global.sql_mode= '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

mysql> insert into banana1 values('1970-01-01 00:00:00','1970-01-01 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'f_time' at row 1

Best Answer

From mysql.com:

With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES.

To disable it you have 2 options.

  1. Via SQL: execute SET @@global.sql_mode = ''; query.
  2. Via my.cnf: change sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" to sql-mode = "". Restart MySQL.