MySQL Bugs: #45669: Column default value not reflected in SHOW CREATE output

Bug #45669	Column default value not reflected in SHOW CREATE output
Submitted:	23 Jun 2009 8:09	Modified:	19 Jun 2013 18:17
Reporter:	Rafal Somla	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5,1, 5.4, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a table is created without explicit default value for a column and then the default value is set with ALTER TABLE, then the new default value does not show in the output of SHOW CREATE TABLE.

Note: This could affect backup system as it uses SHOW CREATE TABLE to serialize table's metadata. But I've checked that the default value setting is correctly restored. The only anomaly is that if a view is created based on a table for which default value was set with ALTER statement, then after restore this default value is not correctly reported in information_schema.columns table for that view.

How to repeat:
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 ( c0 TIMESTAMP DEFAULT '20000101120000');
--query_vertical SHOW CREATE TABLE db1.t1;
CREATE TABLE t2 ( c0 TIMESTAMP );
--query_vertical SHOW CREATE TABLE db1.t2;
ALTER TABLE  t2 ALTER COLUMN c0 SET DEFAULT '20071222140633';
--query_vertical SHOW CREATE TABLE db1.t2;

INSERT INTO t1 VALUES ();
SELECT * FROM t1;

INSERT INTO t2 VALUES ();
SELECT * FROM t2;

======================================================================
Note that the second SHOW CREATE TABLE db1.t2 does not show the default value. But if default value is specified in CREATE TABLE t2 statement, as is the case for t1, then the new default value will show in the output of SHOW CREATE TABLE.

A test script illustrating problem with restored views. See differences between footprint.pre and footprint.post after running it.

CREATE DATABASE db1;
USE db1;
CREATE TABLE t2 ( c0 TIMESTAMP );
ALTER TABLE  t2 ALTER COLUMN c0 SET DEFAULT '20071222140633';
CREATE VIEW v2 AS SELECT * FROM t2;

--error 0,1
--remove_file check_objects.sql
--write_file check_objects.sql
USE information_schema;
SELECT * FROM columns WHERE table_schema LIKE 'db%';
EOF

--exec $MYSQL --vertical <check_objects.sql >footprint.pre

BACKUP DATABASE db1 TO 'db1.bak';
DROP DATABASE db1;
RESTORE FROM 'db1.bak';

--exec $MYSQL --vertical <check_objects.sql >footprint.post

--diff_files footprint.pre footprint.post

DROP DATABASE db1;
--exit

Thank you for the report.

Verified as described.

I seems to be a problem with a specific ALTER syntax. Here it seems that there are no issue:

CREATE DATABASE db1;
USE db1;
CREATE TABLE t2 ( c0 TIMESTAMP );
ALTER TABLE `db1`.`t2` CHANGE `c0` `c0` TIMESTAMP DEFAULT '20071222140633' NOT NULL;
SHOW CREATE TABLE t2;
/*
CREATE TABLE `t2` (
  `c0` timestamp NOT NULL DEFAULT '2007-12-22 14:06:33'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
(but ON UPDATE will then disappear of course unless specified)

BTW: I am almost sure that Baron Schwartz reported the same or a similar issue around 1 year ago.

And doesn't it affect 'mysqldump' and similar backups (if DROP TABLE+CREATE TABLE is in the script)?

Data will of course be backed up and restored, but new data entered after restore will be affected.

I think this is more than 'S3'!

Baron reported similar bug #33887

It looks like an issue specific for TIMESTAMP type (and possibly other *timetypes*).

with VARCHAR:

CREATE DATABASE db1;
USE db1;
CREATE TABLE t2 ( c0 VARCHAR(50) );
ALTER TABLE  t2 ALTER COLUMN c0 SET DEFAULT '20071222140633';
SHOW CREATE TABLE t2;
/*
CREATE TABLE `t2` (
  `c0` varchar(50) DEFAULT '20071222140633'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
ALTER TABLE  t2 ALTER COLUMN c0 SET DEFAULT '20091222140633';
SHOW CREATE TABLE t2;
/*
CREATE TABLE `t2` (
  `c0` varchar(50) DEFAULT '20091222140633'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

Bug #63476 was marked as duplicate of this one.

Noted in 5.7.2 changelog.

If ALTER TABLE was used to set the default value for a TIMESTAMP or
DATETIME column that had CURRENT_TIMESTAMP as its default when it was
created, the new default was not shown by SHOW CREATE TABLE.