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:
To disable it you have 2 options.
SET @@global.sql_mode = '';
query.my.cnf
: changesql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
tosql-mode = ""
. Restart MySQL.