MySQL Bugs: #33124: Error 1067 adding column to table with 2 not null TIMESTAMPs when NO_ZERO

Bug #33124	Error 1067 adding column to table with 2 not null TIMESTAMPs when NO_ZERO_DATE
Submitted:	10 Dec 2007 21:12	Modified:	1 Feb 2013 21:12
Reporter:	Gordon Shannon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.27-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	strict, timestamp

Description:
With sql_mode set to disallow zero dates, create a table with 2 timestamp columns, both set to not null, without explicitly setting any defaults.  It creates the table and you can insert/select, etc.  Now try to add a column.  No matter what you try to add, you get "ERROR 1067 (42000): Invalid default value for 'xxx'"  where xxx is the 2nd timestamp column. This is surprising.

How to repeat:
set global sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected (0.02 sec)

-- This create should fail due to invalid "default" default for ts2
create table gs02 (
  id int not null auto_increment, 
  ts1 timestamp not null,
  ts2 timestamp not null,
  primary key (id));

mysql> show create table gs02\G
*************************** 1. row ***************************
       Table: gs02
Create Table: CREATE TABLE `gs02` (
  `id` int(11) NOT NULL auto_increment,
  `ts1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- To prove we can write to it:
insert into gs02 (ts1, ts2) values (null, 20071030000000);

select * from gs02;
+----+---------------------+---------------------+------+------+
| id | ts1                 | ts2                 | ts3  | ts4  |
+----+---------------------+---------------------+------+------+
|  1 | 2007-12-10 14:13:02 | 2007-10-30 00:00:00 | NULL | NULL | 
+----+---------------------+---------------------+------+------+
1 row in set (0.04 sec)

alter table gs02 add vc varchar(10);

ERROR 1067 (42000): Invalid default value for 'ts2'

-- Now it tells me.  Should have told me at the create.

Suggested fix:
In NO_ZERO_DATE mode, throw error on create with implicit or explicit default of zero date.

Better, allow multiple timestamp columns to have CURRRENT_TIMESTAMP defaults.

Thank you for the bug report. 5.1 not presents this behavior:

set global sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
drop table if exists gs02;
create table gs02 (
  id int not null auto_increment, 
  ts1 timestamp not null,
  ts2 timestamp not null,
  primary key (id));
insert into gs02 (ts1, ts2) values (null, 20071030000000);
alter table gs02 add vc varchar(10);

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.54-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> drop table if exists gs02;
Query OK, 0 rows affected (0.00 sec)

mysql> create table gs02 (
    ->   id int not null auto_increment, 
    ->   ts1 timestamp not null,
    ->   ts2 timestamp not null,
    ->   primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into gs02 (ts1, ts2) values (null, 20071030000000);
Query OK, 1 row affected (0.00 sec)

mysql> alter table gs02 add vc varchar(10);
ERROR 1067 (42000): Invalid default value for 'ts2'

[miguel@amanhecer dbs]$ 5.1/bin/mysql -uroot db4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> drop table if exists gs02;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table gs02 (
    ->   id int not null auto_increment, 
    ->   ts1 timestamp not null,
    ->   ts2 timestamp not null,
    ->   primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into gs02 (ts1, ts2) values (null, 20071030000000);
Query OK, 1 row affected (0.00 sec)

mysql> alter table gs02 add vc varchar(10);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Hello!

This issue has the same reason (the fact that we add implicit defaults for TIMESTAMP columns) and is fixed by the same patch as bug#55131 "cannot define timestamp column without an implicit DEFAULT". The latter was fixed in 5.6.6 version of server. I am closing this bug as a duplicate of the latter bug.