[MDEV-6880] Can't define CURRENT_TIMESTAMP as default value for added column Created: 2014-10-16  Updated: 2015-06-30  Resolved: 2014-11-21

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.40, 10.0.14
Fix Version/s: 10.0.15

Type: Bug Priority: Major
Reporter: Mikhail Gavrilov Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux
Fedora 20
Fedora 21
CentOS 6.x
CentOS 7.x

Issue Links:
Relates
relates to MDEV-3939 Different behavior comparing to MySQL... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2014-10-16 ]

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.

Comment by Sergei Golubchik [ 2014-11-12 ]

We even have a test case for this. But it's for MyISAM, where is succeeds. When I tried It failed for InnoDB.

Comment by Jan Lindström (Inactive) [ 2015-06-29 ]

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;
 
 

Comment by Mikhail Gavrilov [ 2015-06-29 ]

Jan, please try InnoDB engine.

Comment by Mikhail Gavrilov [ 2015-06-29 ] 

<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.

Comment by Jan Lindström (Inactive) [ 2015-06-30 ]

True, I have not yet pushed my fix for InnoDB/XtraDB,

Generated at Thu Feb 08 07:15:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.