MySQL Bugs: #77474: Clarification in documentation for Timestamp initilization

Bug #77474	Clarification in documentation for Timestamp initilization
Submitted:	24 Jun 2015 16:06	Modified:	13 Jul 2015 16:07
Reporter:	Mike W	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Referencing:

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

The following is described:

"Note

The following examples that use DEFAULT 0 do not work if the NO_ZERO_DATE SQL mode is enabled because that mode causes “zero” date values (specified, for example, as 0 '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONAL SQL mode includes NO_ZERO_DATE."

However this is not true according to the NO_ZERO_DATE SQL Mode documentation:

The above note links to: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_zero_date

Which states:

" The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

    If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

    If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

    If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning. 

As of MySQL 5.6.17, NO_ZERO_DATE is deprecated and setting the sql_mode value to include it generates a warning. In MySQL 5.7, this mode does nothing. Instead, its effect is included in the effects of strict mode. "

How to repeat:
mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.16-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> set sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 ( t1 timestamp default 0, d1 datetime default 0 );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `d1` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> insert into t1 values();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'd1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values();
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'd1' at row 1

Suggested fix:
The NO_ZERO_DATE documentation appears to be accurate. The initialization documentation Note should read:

"Note

The following examples that use DEFAULT 0 generate warnings if the NO_ZERO_DATE SQL mode is enabled. Additionally, Errors will occur if NO_ZERO_DATE is enabled and STRICT_ALL_TABLES or STRICT_TRANS_TABLES  because that mode causes “zero” date values (specified, for example, as 0 '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONAL SQL mode includes NO_ZERO_DATE."

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26 Source distribution PULL 2015/06/05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > CREATE DATABASE LX;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE LX
Database changed
mysql 5.6 > show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql 5.6 > set sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1681 | 'NO_ZERO_DATE' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > create table t1 ( t1 timestamp default 0, d1 datetime default 0 );
Query OK, 0 rows affected, 2 warnings (0.20 sec)

mysql 5.6 > SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 't1' at row 1 |
| Warning | 1264 | Out of range value for column 'd1' at row 1 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql 5.6 > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `d1` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.6 > insert into t1 values();
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql 5.6 > SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'd1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > set sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > insert into t1 values();
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'd1' at row 1
mysql 5.6 >

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Will update to refer to the SQL mode docs for more details:

The following examples use DEFAULT 0, a default that can produce
warnings or errors depending on whether strict SQL mode or the
NO_ZERO_DATE SQL mode is enabled. Be aware that the TRADITIONAL SQL
mode includes strict mode and NO_ZERO_DATE. See
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html.