MySQL Bugs: #94340: backwards incompatible changes in 8.0: Error number: 3747

Bug #94340	backwards incompatible changes in 8.0: Error number: 3747
Submitted:	15 Feb 2019 7:59	Modified:	28 Mar 2019 12:56
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.13, 8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	8.0, incompatible changes, minor_release

Description:
See: https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html#error_er_running_appli...

When changing from SBR to RBR we've been able to change the binlog format on a slave while replication was running. (using set global binlog_format = 'row';) It's true that the SQL thread did not use the new setting until the sql thread was restarted but the process worked and did not cause issues: the state was changed.

8.0 GA released in April 2018 started with the same behaviour from 5.7.

8.0.13 changes that behaviour with a new error message. See the URL above. This breaks things by changing behaviour in a minor release.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-errors makes no mention of this new error but there is a reference to the change mentioned under "Functionality Added or Changed".

How to repeat:
Find a slave running 8.0.13+ using SBR. Then run on the slave

SET GLOBAL binlog_format = 'ROW';

You'll see the error:

ERROR 3747: Changing @@global.binlog_format or @@persist.binlog_format is disallowed when any replication channel applier thread is running. You could execute STOP SLAVE SQL_THREAD and try again. while trying to execute: SET GLOBAL binlog_format = 'ROW'

The cause in my case was an attempt to move a number of systems from SBR to RBR. This included multiple minor and major MySQL versions (5.7 + 8.0) and the hierarchy was not flat, that is there were masters, intermediate masters and slaves. Expectation is that the required commands to achieve this and responses from MySQL should be similar. If not automation gets upset. That´s what happened.

Suggested fix:
It is very frustrating to have to adapt automation to handle behavioural differences in minor versions. We already have a number of "not clearly documented" but also "behaviour changes" and "no way to query this meta data on the server itself" settings which when you run SET GLOBAL xxx = yyyy the behaviour differs.

(1) the value changes
(2) for some SQL thread changes: the change is accepted but the value is only used once the SQL thread(s) have been restarted
(3) the change is not allowed because of another related setting which must be "consistent"
(4) I think same as (2) but for the i/o thread or maybe it's both threads, I don't remember now.
(5) new values pop up in minor releases: we have to be careful to not try to set them on older minor versions which don't support / understand them
(6) some values are static and require a server restart

I have requested more meta data to be available since 5.6 (or maybe earlier) to help here. If the information were available the automation scripts could just ask the server: "if I want to change this, can I? and under what circumstances?" Right now scripting has to have hard-coded information to handle this well which often needs adjusting (as you trigger here).

Even if you run in container land which is all the thing these days to spin up a biggish server (> 100GB of RAM) it will take time to warm up and during that time the instance is probably not fully usable. If you can change the server's config online the change happens and no restart is needed so the end result is:
* higher uptime
* less "extra work"
* happier DBAs
* happier customers (our customers)

So here I'd like to see this change reverted. If you want to change _behaviour_ add a allow_incompatible_changes_in_80 type setting (disabled by default) and if set that's fine.

Also please understand why I would like to see the meta data provided by the server. That's the real solution as then my automation doesn't care it can figure out on each server the right way to handle a change to a global setting as it can ask mysqld and do the right thing.

add 8.0 tag

Hi Simon, 

This particular change was needed to allow the use of CREATE/DROP TEMPORARY tables in transaction even with GTID and ROW base replication.

More infor can be found on https://dev.mysql.com/worklog/task/?id=8872

Hi,

I don't buy this. Sorry.

I already run GTID but was using SBR.
So the user story: "So that their is no concern to turn GTID_MODE on." is irrelevant.

So the dev story: "1. If a slave has open temporary tables, and the slave is restarted, the
temporary tables are lost, because they are dropped when the connection
is closed, and subsequently the slave diverges or the applier stops
with an error. No DROP TABLE is written to the slave's binary log."

I would be pretty surprised that if just at the time that I ran this I had a temporary table open. The error message does not talk about temporary tables so it's hard to figure out the underlying reason.

The error I saw did not report this specific issue. If I had an open temporary table I'd like to know about it and maybe the error message should reflect reality.

Maybe it should say something like:

ERROR 9999: Changing @@global.binlog_format or @@persist.binlog_format is disallowed when there are open temporary tables created by the SQL thread(s). You have NN open temporary tables. Please wait for this number to drop to 0 before repeating this command ...
or similar.

I'm aware of all the issues that stopping replication with open temporary tables may trigger which is why I'd be pretty surprised to see this actually being a problem in the case I experienced.

So it looks to me as if the change that's been made has been too strict in its implementation requirements and has not taken into account the ease of migration between formats.

Hello Simon,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Changelog entry added for MySQL 8.0.17:

From MySQL 8.0.13, if any replication channel has open temporary tables, the binary logging format cannot be changed using SET @@global.binlog_format or SET @@persist.binlog_format. Previously, if this operation was attempted after the new restriction was implemented, the wrong error message was returned to the client (referencing a running replication channel applier as the issue, rather than an open temporary table). The appropriate error message is now returned.

Posted by developer:
 
Restriction now highlighted in
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html