[MDEV-25952] Changing global binlog_format only works after restarting CLI Created: 2021-06-17  Updated: 2021-06-20  Resolved: 2021-06-20

Status: Closed
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.5.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Martin Nix Assignee: Alice Sherepa
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Centos 7



 Description   

Default configuration is set to run with binary logs as MIXED (which is pretty typical)

To perform gh-ost operations you must switch to ROW based, entering mysql cli and performing this change does not result in the binary logs being switched in that session (and I believe for existing connections).

As connections can be long lasting this results in the binary logging continuing as MIXED for the greater part of time - gh-ost ignores these binary logs resulting in definitive loss of data



 Comments   
Comment by Alice Sherepa [ 2021-06-18 ]

I guess this is the expected behavior ( https://mariadb.com/kb/en/set/#global-session ),
to change binlog_format dynamically: SET SESSION binlog_format='ROW';

As of gh-ost, I am no expert, but this looks like a workaround:
https://github.com/github/gh-ost/blob/master/doc/migrating-with-sbr.md - having a replica, enable log-slave-updates there + row format

Comment by Martin Nix [ 2021-06-18 ]

Thanks for clarifying Alice much appreciated

We have used gh-ost in that manner and it is a viable workaround but is incredibly wasteful (both in terms of storage and additional I/O) as you have to enable binary logging on the slave also. It works very well otherwise and if you do switch to RBR on the slave, temporarily, to perform your gh-ost operation this works immediately as the connections for replication work are very short lived (so the change is pretty immediate)

I will get onto github about this as I think they should make this a warning in their documentation - it can (and in our case did) result in dataloss if you aren't aware of it

I'm happy for this to be closed out now

Thanks again

Comment by Sergei Golubchik [ 2021-06-20 ]

I'll close it as the described behavior is not a bug, but an intentionally implemented and documented behavior.

It is possible to consider some new feature that will change binlog format for all connections at once. But 1) it'll be a new feature. 2) it must not break existing applications, so should be backward compatible. 3) At the moment I do not see what the syntax should look like and what the implementation could be. Feel free to create a new MDEV with Type=Task for this new feature, if you'd want to start a discussion about how it could be done what the syntax could look like.

Generated at Thu Feb 08 09:41:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.