| Bug #76610 | SET DEFAULT doesn't change the default. | ||
|---|---|---|---|
| Submitted: | 7 Apr 2015 12:31 | Modified: | 4 Aug 2016 16:51 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.5.30, 5.6.16, 5.6.22, 5.5.43, 5.6.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Apr 2015 12:31]
Daniël van Eeden
[7 Apr 2015 18:47]
Daniël van Eeden
// 5.5.30 (affected)
mysql [localhost] {msandbox} (test) > create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
// 5.6.22 (affected)
mysql-5.6.22-debug-log [test] > create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.02 sec)
mysql-5.6.22-debug-log [test] > ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01', LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql-5.6.22-debug-log [test] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
// 5.7.6 (not affected)
mysql-5.7.6-m16 [test] 2> create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.02 sec)
mysql-5.7.6-m16 [test] 2> ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01', LOCK=NONE;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql-5.7.6-m16 [test] 2> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[8 Apr 2015 5:25]
MySQL Verification Team
Hello Daniël, Thank you for the report and test case. Observed that 5.5.43, 5.6.25 are affected. Thanks, Umesh
[8 Apr 2015 5:25]
MySQL Verification Team
// 5.5.43
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.5.43 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.43-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
// 5.6.25
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.25 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.25-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[8 Apr 2015 5:26]
MySQL Verification Team
// 5.7.8, 5.8.0 seems to work as expected
// 5.7.8
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.8-rc-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
// 5.8.0
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.8.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.8.0-m17-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[8 Apr 2015 11:01]
Ståle Deraas
Posted by developer: This is a duplicate of http://bugs.mysql.com/bug.php?id=45669 and fixed in 5.7.2.
[9 Apr 2015 7:46]
Daniël van Eeden
It indeed seems to be a duplicate and fixed in 5.7. What would happen if I replicate from 5.7.7 to 5.6.23 and then do this on the master, would that result in a different default on master and slave?
[10 Apr 2015 14:04]
Ståle Deraas
Posted by developer: Daniël, I think the answer to your question is yes.
[13 Apr 2015 23:24]
Daniël van Eeden
A master with 5.6.24
====================
mysql [localhost] {msandbox} ((none)) > use test;
Database changed
mysql [localhost] {msandbox} (test) > create table t1 (id int, c1 timestamp not null default current_timestamp, primary key(`id`));
Query OK, 0 rows affected (0.06 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ALTER c1 SET DEFAULT '1970-01-01 01:00:01', LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
A slave with 5.7.7
==================
mysql [localhost] {msandbox} ((none)) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
So running this ALTER statement with a 5.6 master and a 5.7 slave can result in data drift on the slave. As it is a best practice to first upgrade slaves this is a senario which can easily happen.
Some possible solution:
- Backport the fix to 5.6
- Let the ALTER statement on 5.6 fail
- Let the slave detect the version of the master (version number from Format_Desc?) and create a 5.6 compatibility mode
[15 Apr 2015 11:33]
Ståle Deraas
Posted by developer: Daniel, Doing any of the suggested changes to 5.6, will pose the same problems as you see now with 5.5 -> 5.6(with change) and 5.6(without change)-> 5.6 with change. Making a compatibility mode for all bugfixes we do, will simply just not scale.
[4 Aug 2016 16:51]
Paul DuBois
Posted by developer: 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, and incorrect values could be inserted into the column.
