MySQL Bugs: #18834: ALTER TABLE ADD INDEX on table with two timestamp fields

Bug #18834	ALTER TABLE ADD INDEX on table with two timestamp fields
Submitted:	6 Apr 2006 7:59	Modified:	14 Mar 2008 20:21
Reporter:	Thomas Sondag	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.19, 5.1 BK	OS:	Linux (Linux RedHat AS3)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:

create table test1 (id_test1 bigint not null, PRIMARY KEY (id_test1)) ENGINE = INNODB;
create table test2 (id_test2 bigint not null,time timestamp NOT NULL, time2 timestamp, id_test1 bigint NOT NULL) ENGINE = INNODB;
ALTER TABLE test2 ADD INDEX test2_id_test1_idx (id_test1);

- ERROR 1067 (42000): Invalid default value for 'time2' 

But :

create table test1 (id_test1 bigint not null, PRIMARY KEY (id_test1)) ENGINE = INNODB;
create table test2 (id_test2 bigint not null,time timestamp NOT NULL, time2 timestamp, id_test1 bigint NOT NULL,FOREIGN KEY (id_test1) REFERENCES test1(id_test1)) ENGINE = INNODB;

Work fine (the INDEX is created automatically)

So I suspect a bug in the alter table function

How to repeat:
create table test1 (id_test1 bigint not null, PRIMARY KEY (id_test1)) ENGINE = INNODB;
create table test2 (id_test2 bigint not null,time timestamp NOT NULL, time2 timestamp, id_test1 bigint NOT NULL) ENGINE = INNODB;
ALTER TABLE test2 ADD INDEX test2_id_test1_idx (id_test1);

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described on 5.0.21-BK:

mysql> create table test2 (id_test2 bigint not null,time timestamp NOT NULL, time2 timestamp, id_test1 bigint NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE test2 ADD INDEX test2_id_test1_idx (id_test1);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.00 sec)

ALTER TABLE just works. So, please, check the newer version, 5.0.19.

Thanks for your fast answer,

So I try with version 5.0.19
I retry the test and I find that the problem come with sql-mode=TRADITIONAL (in my.cnf).
It work fine without this mode.

Thank you for the report.

Verified on Linux using last BK sources as described in last comment:

mysql>  set @@sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2 (id_test2 bigint not null,time timestamp NOT NULL, time2
    -> timestamp, id_test1 bigint NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE test2 ADD INDEX test2_id_test1_idx (id_test1);
ERROR 1067 (42000): Invalid default value for 'time2'
mysql>

5.1 branch affected too. Engine does not matter.

There is duplicate bug #23439.

This is not an ALTER TABLE problem -- the problem is in CREATE TABLE.

The problem is that
  CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP)
defaults c2 to zero date, which is prohibited in the TRADITIONAL
sql mode.

I.e.:

  CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP)

equals to:

  CREATE TABLE t1(
    c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0)

References:
  - SQL modes description:
    http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

  - TIMESTAMP description:
    http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

Test case:

> set sql_mode = traditional;

> CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
  -- Success. This is the problem -- it should have failed.

> CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
  -- Error. That's expected.

Bug#24443 has been marked as a duplicate of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42287

ChangeSet@1.2551, 2008-02-14 18:13:40+03:00, anozdrin@quad. +3 -0
  A patch for Bug#18834: ALTER TABLE ADD INDEX on table with
  two timestamp fields.
    
  The actual problem here was that CREATE TABLE allowed zero
  date as a default value for a TIMESTAMP column in NO_ZERO_DATE mode.
    
  The thing is that for TIMESTAMP date type specific rule is applied:
    column_name TIMESTAMP == column_name TIMESTAMP DEFAULT 0
  whever for any other date data type
    column_name TYPE == column_name TYPE DEFAULT NULL
    
  The fix is to raise an error when we're in NO_ZERO_DATE mode and
  there is TIMESTAMP column w/o default value.

Pushed into 5.1-runtime.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        CREATE TABLE allowed 0 as the default value for a
        TIMESTAMP column when the server was running in
        NO_ZERO_DATE mode.

Bugfix now also noted in 5.1.23-ndb-6.3.11 changelog.