Bug #33240 ALTER TABLE fails for DATETIME column under certain circumstances
Submitted: 14 Dec 2007 12:43 Modified: 14 Dec 2007 17:21
Reporter: Per Hallström Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.45, 5.0, 5.1, 6.0 BK OS:Any (Mac OS X 10.5.1, Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: after, ALTER TABLE, column, datetime, default value, innodb, NOT NULL
Triage: Triaged: D3 (Medium)

[14 Dec 2007 12:43] Per Hallström
Description:
When specifying "AFTER" in an ALTER TABLE command, DATETIME values appear to default to "0000-00-00 00:00:00" even when no DEFAULT is specified, which causes the ALTER to fail when the sql_mode setting prevents such dates from being used.

See below (how to repeat) for statements that demonstrates the bug. The error message is "ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'col' at row 1".

How to repeat:
/* Any storage engine will do */
CREATE TABLE test.test1 (i INT, d DATETIME NOT NULL);
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL AFTER i; 
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL COMMENT "contains data";
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL COMMENT "contains data" AFTER i;
INSERT INTO test.test1(d) VALUES(NOW());

/* WILL FAIL */
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL AFTER i; 
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL COMMENT "contains data" AFTER i;

/* WILL SUCCEED */
ALTER TABLE test.test1 MODIFY COLUMN d DATETIME NOT NULL COMMENT "contains data";
[14 Dec 2007 12:44] Per Hallström
The "sql_mode" that I mentioned in the initial post is "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".
[14 Dec 2007 17:21] Sveta Smirnova
Thank you for the report.

Verified as described. Problem is NO_ZERO_DATE sql mode.

Workaround: temporarily set sql_mode to ''