MySQL Bugs: #74211: No upgrade path for explicit_defaults_for

Bug #74211	No upgrade path for explicit_defaults_for_timestamp = 1
Submitted:	3 Oct 2014 11:29	Modified:	6 Feb 9:33
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.14, 5.7, 8.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	configuration, explicit_defaults_for_timestamp, static, upgrade path

Description:
MySQL 5.6 came up with good things like making timestamp configuration a bit more flexible.
By default the old behaviour is enabled which makes some sense and a warning happens telling you that you should change that.

141003 01:04:19 mysqld_safe Starting mysqld daemon with databases from /path/to/datadir
2014-10-03 01:04:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

When upgrading with a replication chain and having 5.5 masters and 5.6 slaves it's important to make sure that settings are consistent etc so getting this value right is quite important (it's not clear if this setting on the master is available via the replication stream).

I've just noticed that if you want to change it you can't:

root@myhost [mydb]> set global explicit_defaults_for_timestamp = 1;
ERROR 1238 (HY000): Variable 'explicit_defaults_for_timestamp' is a read only variable

This is on 5.6.14-log but I'd guess this behaviour hasn't changed.

How to repeat:
See above.

Suggested fix:
Suggestions:

1. Make this setting dynamic, if necessary add warnings when the setting is changed. if this requires any replication to be disabled then add the necessary checks etc.
2. Ideally for 5.7 make the default setting = 1 so that the new usage can actually be used. Add again appropriate warnings so that if this needs to be set differently (because the 5.7 slave has a 5.6 master) then the DBA will be aware of this. Also make sure that this is as safe as it can be in the replication stream. 5.6 --> 5.7 or 5.7 --> 5.7. Probably the slave needs to maintain the same setting as the master's thread (at a session level).

The documentation is great but this new setting which is a good idea as it stands now really can not be enabled (safely?) without shutting down boxes and as such most people with installations which are not new won't use them. Additionally if setting up boxes like this and "new instances" then the same configuration can't be used for the reasons outlined so this makes it even less likely that we'll use the new good setting.

So please look in 5.7 at making this new functionality easier to use.

I think that actually there is a subtle underlying bug if the master and slave are for any reason configured with different settings and timestamp columns are added or modified on a table.

The change on the master will take into account the local settings of this value, and I believe that the change on the slave will take into account its local setting. This could result in master and slave having different table definitions which would be most unhelpful.

So unless there is special configuration in the replication threads to take into account the master's version, or the master's configuration there's an ability to transparently make the table definitions on master and slave diverge.

Adjust to include 5.7 as also affected.

MySQL sandbox shows this is the case. The example is with a 5.7.5 master/slave but this will be equally broken with any 5.6/5.7 combination:

master [localhost] {msandbox} (test) > show global variables like '%timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
| log_timestamps                  | UTC   |
+---------------------------------+-------+
2 rows in set (0.00 sec)

master [localhost] {msandbox} (test) > create table ts ( ts timestamp ) ;
Query OK, 0 rows affected (0.04 sec)

master [localhost] {msandbox} (test) > show create table ts\G
*************************** 1. row ***************************
       Table: ts
Create Table: CREATE TABLE `ts` (
  `ts` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > exit
Bye

[myuser@localhost rsandbox_5_7_5]$ node1/use test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.5-labs-preview-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} (test) > show global variables like '%timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
| log_timestamps                  | UTC   |
+---------------------------------+-------+
2 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > show create table ts\G
*************************** 1. row ***************************
       Table: ts
Create Table: CREATE TABLE `ts` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > 

So this shows clearly that mixing this setting between a master and a slave may break any table creations involving timestamps. Upgrading an existing replication chain is therefore impossible without stopping all servers and changing the configuration of all of them at the same time. That's not practical for many people including myself.

Changed bug title to reflect the problem.
* Note if you provide a new feature and want us to use it provide a way to migrate to it's usage
* make sure that this migration path includes replication environments where not all servers can be shut down at the same time.
* make sure this migration path is replication safe

This lack of an upgrade path and a default setting not to enable this setting means I'm sure most people won't be using it. That is unfortunate and makes the effort involved in providing the more flexible behaviour rather wasted.

Hi Simon,

Thank you for your feedback.

In response to part of your suggestion #2; we intend to change the default to explicit_defaults_for_timestamp = 1 in MySQL 8.0.2.

It is also now a dynamic variable with session/global scope (at least since 5.6?):

master [localhost] {msandbox} ((none)) > set global explicit_defaults_for_timestamp=1;
Query OK, 0 rows affected (0.00 sec)

I have tested the upgrade case of:
1) Master with OFF; slave1 with ON; slave2 with OFF
2) Master with ON; slave1 with ON; slave2 with OFF

In both cases I was able to reproduce the same CREATE TABLE on all three servers.  This is the result of the binary log stream, which includes this meta data:

# at 340
#170629 11:17:28 server id 1  end_log_pos 412 CRC32 0xcac995d1 	Anonymous_GTID	last_committed=1	sequence_number=2	original_committed_timestamp=1498749448581689	immediate_commit_timestamp=1498749448581689
# original_commit_timestamp=1498749448581689 (2017-06-29 11:17:28.581689 EDT)
# immediate_commit_timestamp=1498749448581689 (2017-06-29 11:17:28.581689 EDT)
/*!80001 SET @@session.original_commit_timestamp=1498749448581689*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 412
#170629 11:17:28 server id 1  end_log_pos 525 CRC32 0xa57a041f 	Query	thread_id=7	exec_time=0	error_code=0	Xid = 34
SET TIMESTAMP=1498749448/*!*/;
SET @@session.explicit_defaults_for_timestamp=0/*!*/;
CREATE TABLE t5(a timestamp)
/*!*/;
# at 525
#170629 11:17:52 server id 1  end_log_pos 597 CRC32 0x169b2dda 	Anonymous_GTID	last_committed=2	sequence_number=3	original_committed_timestamp=1498749472151571	immediate_commit_timestamp=1498749472151571
# original_commit_timestamp=1498749472151571 (2017-06-29 11:17:52.151571 EDT)
# immediate_commit_timestamp=1498749472151571 (2017-06-29 11:17:52.151571 EDT)
/*!80001 SET @@session.original_commit_timestamp=1498749472151571*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 597
#170629 11:17:52 server id 1  end_log_pos 710 CRC32 0x303083d6 	Query	thread_id=7	exec_time=0	error_code=0	Xid = 37
SET TIMESTAMP=1498749472/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
CREATE TABLE t6(a timestamp)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

I'm changing the status to 'Needs Feedback'.

Please let me know if there is a particular upgrade scenario where you can see the change in default not working.  We will be happy to investigate further.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Morgan.

Sorry for the delay in responding.

The variable is dynamic but it changes behaviour of the system concerned. In a replicated environment where I'm pushing changes of timestamp type columns all the time by changing the setting I change the behaviour of the server where the change is applied. Yes, that's dynamic. However, what happens if I change that setting say on a master and don't change it on a slave? The slave will not behave the same as the master and this will cause a breakage as the content or behaviour of the master will differ from the slave.

This means I need to either stop all changes happening to my system, so I can change the setting on all servers in the replication chain, or I need to apply the change at _exactly_ the same time on all servers. Given I'd expect the session variable to be used on existing connections I'm not aware of any way change session settings for a running session that I don't own.

So to me this means that there's no sane way to do an upgrade. Sure, if you have 2 servers that don't do much this is trivial. If you have > 100 busy servers in a replication chain then I'd say the challenge is real and the chances of causing breakage or data divergence between master and slave are harder.

Perhaps I'm missing something but I'm not sure what.

We upgraded a slave to 8.0.2 recently and replication broke for the reasons outlined above. This required us to modify the 8.0.2 slave to have the 5.7 setting. Which leaves us back in the same state we were in before.

What I'm not sure about is setting this value first on the master and leaving the old value on the slave. If this only causes replication breakage and there's no silent "different behaviour" which is accepted by the slave (so maybe this works in RBR but not SBR) then if it triggers an error on the slave I can wait for the error, change the configuration and restart replication. That's very messy but workable but given I've not seen such a suggestion I'm not sure that it's something that's been considered or checked.

To be honest I have the feeling that you simply have not tested this because this bug report has been open for some time, you make the change in 8.0.2 and it breaks the setup with a 5.7 master and 8.0.2 with both master and slave using their default settings.

This commit seems to be related.

https://github.com/mysql/mysql-server/commit/af205c05d8a75ed64bf99a7d6bd055923647d401 (which should be in 5.7.8-rc or 5.7.9)

The value for explicit_defaults_for_timestamp seems to be in the binlog (8.0.3 server)
==========================================================
# at 3167
#171012 16:45:50 server id 1  end_log_pos 3305 CRC32 0x797bd1dc 	Query	thread_id=30	exec_time=1	error_code=0	Xid = 181
SET TIMESTAMP=1507819550/*!*/;
SET @@session.explicit_defaults_for_timestamp=0/*!*/;
CREATE TABLE t5 (id serial primary key, c1 timestamp)
/*!*/;
==========================================================

On 8.0.3 I can't reproduce the issue Simon shows on the mysql sandbox based example.

I was able to reproduce this with:
 - binlog_format = STATEMENT
 - 5.7.18 master with explicit ts defaults disabled
 - 8.0.3 slave with exp_ts_def enabled.
The testcase might require you to do 'STOP SLAVE; START SLAVE' on the slave more than once and insert a record on the master multiple times.

Details:

make_sandbox 5.7.18 -- --master
make_sandbox 8.0.3 -- --slaveof 'master_port=5718'

cd $HOME/sandboxes/msb_5_7_18
./use test
SET GLOBAL binlog_format=STATEMENT;
QUIT;

cd $HOME/sandboxes/msb_8_0_3
./use test
SET GLOBAL binlog_format=STATEMENT;
STOP SLAVE; START SLAVE;
QUIT;

cd $HOME/sandboxes/msb_5_7_18
./use test
CREATE TABLE t1 (
`id` int unsigned auto_increment,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY (`created`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO t1(created) VALUES (NULL);
SELECT @@global.explicit_defaults_for_timestamp; -- returns 0
QUIT;

cd $HOME/sandboxes/msb_8_0_3
./use test
SHOW SLAVE STATUS\G
SELECT @@global.explicit_defaults_for_timestamp; -- returns 1

Result:
Last_Error: Error 'Column 'created' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO t1(created) VALUES (NULL)'

So it looks like the upgrade path for timestamp defaults is:
1. Upgrade all machines to at least 5.7
2. Move to RBR (first slaves, then intermediates, then master)
3. Enable exp. ts defaults on master and slaves (no order needed).

Coming back to this as a lot of time has gone by.

The change in setting modifies behaviour. So applications expecting one behaviour may be surprised by a change in behaviour. Thus even if "RBR works" some consideration will be needed in ensuring the applications that insert timestamps:
- use the right values
- handle seeing the old behaviour vs the new behaviour

Also there's no way to change the default/global behaviour on a running server short of restarting it which on a primary is intrusive to put it mildly. Pushing the change when changing master to a new master will trigger a sudden change in behaviour which might be interesting.
I also wonder what might happen with GR if the settings are not consistent between GR members.

So perhaps there are no issues but an explanation of whether the MySQL team think there's a good upgrade path from one setting to another would be useful.

Why do I care still (after 10 years)? Partly because with 8.4.0 coming out soon and deprecating features, this may change in 8.4.0 and if it doesn't change in 8.4.0 it's likely to change in 9.X so the chances of having to look at this and verify how to ensure that existing clusters running since 5.0 days with the old setting can be migrated and the process for doing that may need to be considered with a bit more urgency if current usage may break with the upgrade. That mainly requires ensuring the applications are doing the right thing and can be modified if that's necessary.