Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5.40, 10.0.14
-
None
-
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 |
Attachments
Issue Links
- relates to
-
MDEV-3939 Different behavior comparing to MySQL 5.6 on adding an auto-initialized timestamp/datetime column to InnoDB table
- Closed
- links to