Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.40, 10.0.14
-
Fix Version/s: 10.0.15
-
Component/s: Data Definition - Alter Table
-
Labels:None
-
Environment:Linux
Fedora 20
Fedora 21
CentOS 6.x
CentOS 7.x
Description
Can't define CURRENT_TIMESTAMP as default value for added column
Example:
CREATE TABLE `table2` ( `i1` INT(10) UNSIGNED NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=latin1; INSERT INTO table2 (i1) VALUES (1), (2), (3), (4), (5); ALTER TABLE `table2` ADD `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; SELECT * FROM table2;
i1 d1
------ ---------------------
1 0000-00-00 00:00:00
2 0000-00-00 00:00:00
3 0000-00-00 00:00:00
4 0000-00-00 00:00:00
5 0000-00-00 00:00:00
Expected that d1 filled with CURRENT_TIMESTAMP values instead '0000-00-00 00:00:00' because '0000-00-00 00:00:00' is incorrect for this filed value.
I can prove it:
INSERT INTO table2 (i1, d1) VALUES (6, '0000-00-00 00:00:00') -- return Error Code: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'd1' at row 1
OR
CREATE TABLE `table1` ( `i1` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`i1`) ) ENGINE=INNODB DEFAULT CHARSET=latin1; INSERT INTO table1 (i1) VALUES (1), (2), (3), (4), (5); ALTER TABLE `test`.`table2` ADD FOREIGN KEY (`i1`) REFERENCES `table1`(`i1`); -- return Error Code: 1292 Incorrect datetime value: '0000-00-00' for column 'd1' at row 1
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
We even have a test case for this. But it's for MyISAM, where is succeeds. When I tried It failed for InnoDB.
Tested with 10.1.5, InnoDB fails differently but after fix both refuse to set constant default.
set sql_mode = ''; CREATE TABLE t1 ( `i1` INT(10) UNSIGNED NOT NULL, `d1` TIMESTAMP NULL DEFAULT NULL ) ENGINE=myisam; INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5); ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT '2015-01-01 10:20:10'; select * from t1; i1 d1 1 2015-06-29 21:40:41 2 2015-06-29 21:40:41 3 2015-06-29 21:40:41 4 2015-06-29 21:40:41 5 2015-06-29 21:40:41 drop table t1; set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE'; CREATE TABLE t1 ( `i1` INT(10) UNSIGNED NOT NULL, `d1` TIMESTAMP NULL DEFAULT NULL ) ENGINE=myisam; INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5); ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT '2015-01-01 10:20:10'; select * from t1; i1 d1 1 2015-06-29 21:40:41 2 2015-06-29 21:40:41 3 2015-06-29 21:40:41 4 2015-06-29 21:40:41 5 2015-06-29 21:40:41 drop table t1;
Jan, please try InnoDB engine.
<n>Query: CREATE TABLE t1 ( `i1` INT(10) UNSIGNED NOT NULL, `d1` TIMESTAMP NULL DEFAULT NULL ) 0 row(s) affected Execution Time : 0.038 sec Transfer Time : 1.057 sec Total Time : 1.096 sec -------------------------------------------------- Query: INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5) 5 row(s) affected Execution Time : 0.049 sec Transfer Time : 0.042 sec Total Time : 0.091 sec -------------------------------------------------- Query: ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT '2015-01-01 10:20:10' Error Code: 1138 Invalid use of NULL value Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.055 sec
SELECT VERSION()
version() ---------------- 10.1.5-MariaDB
I've just test this example on MariaDB 10.1.5 with InnoDB engine, and I see that error is present.
True, I have not yet pushed my fix for InnoDB/XtraDB,
The problem has a history.
It actually worked in early versions of MariaDB 10.0, while in MySQL it was broken (see
MDEV-3939). Apparently, we did or merged something bad in 10.0.4, while MySQL fixed the problem in 5.6.11 (see http://bugs.mysql.com/bug.php?id=68040), so now we have the opposite situation, it works in MySQL and fails in MariaDB.