Bug #24443 second timestamp column has an invalid default value

• Submitted: 20 Nov 2006 18:07
• Modified: 4 Feb 2008 14:40
• Reporter: Brian Duggan
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.54, 5.0.32-BK, 5.0.26
• OS: Linux (linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[20 Nov 2006 18:07] Brian Duggan

Description:
When creating a table with a second timestamp on a server with strict sql mode,
the default value is an invalid date (0000-00-00 00:00:00).  The error is not reported until _another_ field in the table is altered.

How to repeat:

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | 
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table foo (aaa timestamp, bbb timestamp, ccc int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table foo modify ccc int(10);
ERROR 1067 (42000): Invalid default value for 'bbb'
mysql> 

Suggested fix:
Make the default for the second one NULL.

Or, at least report the error when the table is created,
not when it's altered in an unrelated way.

[21 Nov 2006 17:28] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.32-BK on Linux:

mysql> set sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_Z
ERO_DATE';
Query OK, 0 rows affected (0.01 sec)

mysql> drop table foo2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table foo2 (aaa timestamp, bbb timestamp, ccc int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table foo2 modify ccc int(12);
ERROR 1067 (42000): Invalid default value for 'bbb'

NO_ZERO_DATE mode leads to this result.

[4 Feb 2008 14:40] Alexander Nozdrin

This is a duplicate of Bug#18834.