Details
-
Task
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
None
Description
Given a slave server with read_only = on and binlog_format = statement which is used to perform complex queries involving some temporary table (which work as expected despite read_only mode)
However, even if no other data has (and could have) been altered on the slave, all statements dealing with the temporary table appear in the slave's binlog. It doesn't help to cover them in a transaction which is rolled back afterwards.
In contrast, with binlog_format = row nothing is written to the binlog as expected. But on a read-only slave the binlog format shouldn't matter, especially since "statement" is the default.
(Side note: the binlog record increases the GTID sequence number which might lead to issues when GTID strict mode is turned on).
Attachments
Issue Links
- relates to
-
MDEV-29796 Auto-generated DELETE for MEMORY/HEAP table can break GTID-based replication
-
- Needs Feedback
-
It's the other way round – binlog format matters as much as always, because read_only option does not anyhow affect binary logging, whatever the format is. It prohibits execution of user's SQL updates (with some exceptions); but whatever is executed, is written into binary log as usual. Updates to temporary tables are normally written to the binary log in statement mode and not written in row mode, so they continue working in the same exact way, just like super-user updates are written normally into the binary log, regardless the read_only mode.
If you are running your logic with temporary tables as a user with SUPER privilege, you can use SET sql_log_bin=0 to prevent binlog updates.