Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 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.
share|improve this answer
    
This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date. –  quanta Oct 4 '13 at 9:41
    
+1 for helpful link but it didn't work. See my updates. –  quanta Oct 4 '13 at 9:46
    
@quanta ok, I,ll try to reproduce the issue. –  HAL9000 Oct 4 '13 at 9:48
    
@quanta is INSERT IGNORE is an option? I have not clustered server, and confirm the issue, you having. INSERT IGNORE allows to pass it. –  HAL9000 Oct 4 '13 at 10:00
    
With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not. –  quanta Oct 4 '13 at 10:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.