[MDEV-3939] Different behavior comparing to MySQL 5.6 on adding an auto-initialized timestamp/datetime column to InnoDB table Created: 2012-12-15  Updated: 2014-10-16  Resolved: 2013-01-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-452 Add full support for auto-initialized... Closed
relates to MDEV-6880 Can't define CURRENT_TIMESTAMP as def... Closed

 Description   

I don't think there is anything to fix in MariaDB, as it's other implementation that seems to be wrong, but since there was interest in differences in behavior, here it goes.

Test case:

--source include/have_innodb.inc
 
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1);
 
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 
SELECT * FROM t1;

on maria/10.0-mdev452 and maria/10.0-base revno 3447, it returns

CREATE TABLE t1 (i INT) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1);
 
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 
SELECT * FROM t1;
 
i	ts
 
1	2012-12-15 06:22:48

on mysql-5.6.9 and previous versions of MySQL and MariaDB, it returns

CREATE TABLE t1 (i INT) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1);
 
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 
SELECT * FROM t1;
 
i	ts
 
1	0000-00-00 00:00:00

Same is true for a NULL-able TIMESTAMP column and for DATETIME column with auto-init.

The difference only happens with InnoDB; with MyISAM, both MariaDB and MySQL insert the auto-initialized value.

I can file a bug for MySQL, although it will probably get very low priority, since that's how it's been in previous versions, too.



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-12-17 ]

Apparently this is a bug in MySQL 5.6. More specifically it is an incomplete fix for MySQL's

Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT_TIMESTAMP INSERTS ZERO

They fixed only for MyISAM, and never even tested for InnoDB. Since I implementated the feature from scratch,
we don't have this bug. We have this test case in 10.0, and it works correctly with both MyISAM and InnoDB.

I suggest that you submit a bug against MySQL, it is a good point that our reimplementation is of better
quality.

This is the test case I derived from your test above:

CREATE TABLE t1i (i INT) ENGINE=InnoDB;
INSERT INTO t1i VALUES (1);
ALTER TABLE t1i ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SELECT * FROM t1i;

CREATE TABLE t1m (i INT) ENGINE=MyISAM;
INSERT INTO t1m VALUES (1);
ALTER TABLE t1m ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SELECT * FROM t1m;

In addition, please add that their own test case for MySQL Bug#11745578 works incorrectly
with InnoDB.

Comment by Timour Katchaounov (Inactive) [ 2012-12-17 ]

Verified the bug on various versions of MySQL/MariaDB, found the related MySQL patch,
determined this is a MySQL-only bug.

Comment by Elena Stepanova [ 2012-12-17 ]

I'll re-open it temporarily so that I don't forget to report it to MySQL as suggested

Comment by Elena Stepanova [ 2013-01-06 ]

Filed in MySQL bug base as http://bugs.mysql.com/bug.php?id=68040

Generated at Thu Feb 08 06:52:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.