MySQL Bugs: #67433: Using SET GLOBAL SQL_LOG

Bug #67433	Using SET GLOBAL SQL_LOG_BIN should not be allowed
Submitted:	31 Oct 2012 5:49	Modified:	9 Oct 2014 18:29
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5 (All)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	global, option, replication, session, sql_log_bin

Description:
Background of the change

As of MySQL 5.5 (prior to the first actual release), the SQL_LOG_BIN variable was made to be both global and session-scoped, instead of only session-scoped as it was in 5.1 and older versions. This change was not particularly documented, although the system variable table was updated to show it as "Global, Session" instead of "Session in the documentation:

For MySQL 5.1:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sql_log_bin

For MySQL 5.5:

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_log_bin

No particular mention is made about the expected behavior if the user runs SET GLOBAL SQL_LOG_BIN=0 while binary logging is enabled, despite a page being dedicated to SQL_LOG_BIN here:

http://dev.mysql.com/doc/refman/5.5/en/set-sql-log-bin.html

The change was committed in the following commit/merge (sorry, I'm not sure if there's a better way to show this in Launchpad):

http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/2993#mysql-test/suite/binlog/...

Alternately it can be seen in Twitter's MySQL GitHub repository here:

https://github.com/twitter/mysql/commit/c0d8d3d853505af0ca9ca330f26be5c08160acf2

What is SQL_LOG_BIN used for?

Normally, SET SESSION SQL_LOG_BIN is used by DBAs to run a non-conflicting command locally only, ensuring it does not get replicated. This is often used for non-destructive operations such as compacting tables, swapping tables, additive schema changes, and the like. This has been the case for many years, and is understood to be a safe and easy way to do maintenance on replicated systems without requiring simultaneous downtime on all slaves.

Opinion about this change

I believe this new behavior to be incorrect and extremely dangerous. When SQL_LOG_BIN is changed globally, it does not immediately take effect for any sessions. It takes effect by becoming the session-level default inherited at the start of each new session, and this setting is kept and cached for the duration of that session. Setting it intentionally is unlikely to have a useful effect under any circumstance; setting it unintentionally, such as while intending to use SET [SESSION] is potentially disastrous. Accidentally using SET GLOBAL SQL_LOG_BIN will not show an immediate effect to the user, instead not having the desired session-level effect, and thus causing other potential problems with local-only maintenance being binlogged and executed on slaves.

However the bigger problem is with the long term/permanent damage which may result in irrecoverable or difficult data loss. This effect could be seen days or weeks later if SET GLOBAL SQL_LOG_BIN is executed on a machine which is currently acting as a slave and later gets promoted to master and suddenly starts directly taking writes. (The slave thread may not show any difference due to being continually running and not receiving the global setting into its session.)

Neither myself nor anyone on my DBA team could figure out any reason to actually use SET GLOBAL SQL_LOG_BIN to constructive use. In any case if there is some valid use case for it, it is likely that implementing a less dangerous command to be used for that use case would be better.

How to repeat:
SET GLOBAL SQL_LOG_BIN=0;

Suggested fix:
In my opinion there are two possible solutions:

1. Revert the change and again make SQL_LOG_BIN a session-only variable, generating an error if it is used with SET GLOBAL. This is my preference, due to the dangers of the current behavior described above and the possibility for data loss.

2. Generate a very specific and strongly worded warning when SQL_LOG_BIN is used with SET GLOBAL; add additional documentation to the manual describing the behavior in this case and what may need to be done to correct the situation. While this doesn't prevent the scenarios above, it at least may help the user out of trouble.

Thank you for the report.

Lack of documentation verified as described.

Regarding to "revert" I don't agree: with global access it is possible to make a server master online, without bringing it down.

This has nothing to do with a server being a master or not, and it doesn't really even have the effect you imply, since it would require all sessions to disconnect, and slave threads to be restarted before logging would begin. This also grossly disagrees with the replication changes occurring in 5.6 where binary logging may need to be enabled for slaves in order for GTID-based recovery to be possible.

If the intended effect is to be able to enable and disable binary logging in an online fashion, a much safer method should be added instead of this.

This was fixed in Twitter MySQL here:

https://github.com/twitter/mysql/commit/9302f22d9da38558908f0e2c8ae24af75c0b8a8b

http://blog.jcole.us/2014/08/08/stupid-and-dangerous-set-global-sql_log_bin/

Discussed here:
http://www.tocker.ca/2014/08/14/set-global-sql_log_bin-we-need-your-feedback.html

In which version this issue is fixed!

I agree this should be fixed,  its evil.     Furthermore,  setting it to on isnt enough to fix binlogging after its off.   The clients need to reconnect for their session to take the new value.

Thanks for your feedback. The description of sql-log-bin has been updated in the 5.5, 5.6 and 5.7 documentation as follows:

The sql_log_bin variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this variable. Set this variable to 0 for a session to temporarily disable binary logging while making changes to the master which you do not want to replicate to the slave.

As of MySQL 5.5, sql_log_bin can be set as a global or session variable. Setting sql_log_bin globally is only detected when a new session is started. Any sessions previously running are not impacted when setting sql_log_bin globally.

Warning

Incorrect use of sql_log_bin with a global scope means any changes made in an already running session are still being recorded to the binary log and therefore replicated. Exercise extreme caution using sql_log_bin with a global scope as the above situation could cause unexpected results including replication failure.

First: The warning noted above is extremely weak and does very little to capture the danger of this command.

However, is this all that is being done to close this bug? It's not being actually fixed?

This feature recently came up in an incident post-mortem at Google, and I would like to share the following from it:

"During the search, the developer finds MySQL Bug #67433 filed by Jeremy Cole. And even though the bug says that sql_log_bin is evil and dangerous, it also has a note from MySQL developer hinting that it was introduced exactly for this scenario -- for switching slave to master and back online (allowing binlogging on master and prohibiting it on slaves). So it was decided to use it anyway."

Aside from this feature itself being dangerous, doing nothing about it is dangerous, and commenting without understanding the change, bug, or feature is irresponsible. As it is, even really smart people who manage to find this bug will potentially continue to think that it's okay to use this "feature" and will assume that it's going to work in some reasonable way, while that just isn't true.

FYI, it happens like this in latest 5.6:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.22-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> set global sql_log_bin=0;
ERROR 1231 (42000): Variable 'sql_log_bin' can't be set to the value of '0'
mysql>

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
The fix for this issue is in MySQL 5.5.41, 5.6.22, 5.7.6.

Starting with these releases SET GLOBAL SQL_LOG_BIN is no longer allowed, and attempting to execute this statement raises an error.

Full text of the changelog entry to follow shortly.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Changelog entries for this fix:

5.5.41/5.6.22 version:

    The global scope for the sql_log_bin system variable has been
    deprecated, and this variable can now be set with session scope
    only. The statement SET GLOBAL SQL_LOG_BIN now produces an
    error. It remains possible to read the global value of
    sql_log_bin, but you should act now to remove any dependencies
    on reading this value from your applications, as the ability to
    do so will be removed in a future MySQL release.

5.7.6 version:

    The global scope for the sql_log_bin system variable has been
    deprecated, and this variable can now be set with session scope
    only. The statement SET GLOBAL SQL_LOG_BIN now produces an
    error. It remains possible to read the global value of
    sql_log_bin, but doing so produces a warning. You should act now
    to remove any dependencies on reading this value from your
    applications, as the ability to do so will be removed in the
    next major MySQL release following MySQL 5.7.

No status change (this is just following up; bug is already closed).

Thanks for the comments and clarifications. This is good news!

$ bzr log -r 4718
------------------------------------------------------------
revno: 4718
committer: Bill Qu <bill.qu@Oracle.com>
branch nick: mysql-5.5
timestamp: Wed 2014-09-24 09:44:48 +0800
message:
  Bug #15868071 	USING SET GLOBAL SQL_LOG_BIN SHOULD NOT BE ALLOWED
  
  Normally, SET SESSION SQL_LOG_BIN is used by DBAs to run a
  non-conflicting command locally only, ensuring it does not
  get replicated.
  Setting GLOBAL SQL_LOG_BIN would not require all sessions to
  disconnect. When SQL_LOG_BIN is changed globally, it does not
  immediately take effect for any sessions. It takes effect by
  becoming the session-level default inherited at the start of
  each new session, and this setting is kept and cached for the
  duration of that session. Setting it intentionally is unlikely
  to have a useful effect under any circumstance; setting it
  unintentionally, such as while intending to use SET [SESSION]
  is potentially disastrous. Accidentally using SET GLOBAL
  SQL_LOG_BIN will not show an immediate effect to the user,
  instead not having the desired session-level effect, and thus
  causing other potential problems with local-only maintenance
  being binlogged and executed on slaves; And transactions from
  new sessions (after SQL_LOG_BIN is changed globally) are not
  binlogged and replicated, which would result in irrecoverable
  or difficult data loss.
  This is the regular GLOBAL variables way to work, but in
  replication context it does not look right on a working server
  (with connected sessions) 'set global sql_log_bin' and none of
  that connections is affected. Unexperienced DBA after noticing
  that the command did "nothing" will change the session var and
  most probably won't unset the global var, causing new sessions
  to not be binlog.
  Setting GLOBAL SQL_LOG_BIN allows DBA to stop binlogging on all
  new sessions, which can be used to make a server "replication
  read-only" without restarting the server. But this has such big
  requirements, stop all existing connections, that it is more
  likely to make a mess, it is too risky to allow the GLOBAL variable.
  
  The statement 'SET GLOBAL SQL_LOG_BIN=N' will produce an error
  in 5.5, 5.6 and 5.7. Reading the GLOBAL SQL_LOG_BIN will produce
  a deprecation warning in 5.7.

good change.

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_log_bin still lists it as global

thats really a very old post :/