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
• Status: Verified
• 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

[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 ''