SET GLOBAL sql_log_bin – We need your feedback!

Jeremy Cole recently blogged about the feature SET GLOBAL sql_log_bin. In his blog post, Jeremy suggested that there is no strong use-case for this feature, and that it is too easy to run accidentally (i.e. SET GLOBAL instead of SET [SESSION]). The result of this incorrect usage is that slaves will drift out of sync from masters.

We discussed Jeremy’s request in the MySQL team, and we agree that the syntax should produce an error in MySQL 5.7. However, we have not yet determined which steps should be taken for MySQL 5.5 and MySQL 5.6. The two proposals we would like to float are:

  1. The statement SET GLOBAL sql_log_bin=N produces a warning.
  2. The statement SET GLOBAL sql_log_bin=N produces an error.

Option #1 does not specifically solve Jeremy’s reported issue, because even though the command provides a warning, it will still execute. Thus, the data-drift from accidental usage will still occur; the operator will simply be aware of the problem sooner.

Option #2 does not follow our standard policy regarding functionality changes in GA releases. That is to say that we are discussing making a special exception since this behaviour is regarded as dangerous and almost certainly unintended.

We are seeking input from the community as to which is the better of these two options:

  • Which is your preference – option #1 or option #2?
  • Do you agree that this specific situation warrants a behaviour change in GA releases?

Please leave a comment, or get in touch!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Justin Swanhart

    I think it should be an error in all versions that are still getting regular releases. There is no good reason to allow this command. It should be considered a bug that it has “worked” and that bug should be fixed, pronto.

  • Derek Downey

    I think this one warrants an error. I wouldn’t mind the ability to dynamically turn on binary logging (not require restart), but turning it off should be disallowed. Either that or have a different syntax to enable/disable global binlog.

    • Just to clarify – this feature can not safely be used to dynamically turn on binary logging (although that is a valid feature request).

      The reason for this, is that each session caches the GLOBAL settings as it is created. So you would need to start the server with binary logging enabled + global sql_log_bin=0, then set sql_log_bin=1 and kill all connections.

      • Derek Downey

        Right, the ‘feature request’ would be to make the log_bin and log_slave_updates dynamic to fully avoid the restart. Then enable binary logging (with a different syntax!)+kill connections. That’s all really invasive and if it ever became reality should be treated as ‘very dangerous’™ like ‘RESET MASTER’ :).

        • I can see a good use case for both being dynamic, but as for complexity… I am sure there is quite a bit involved.

  • Will Gunty

    Option 2, for sure. This is very dangerous and I believe it warrants an exception to the normal policies.

  • +1 on option 2

  • Shlomi Noach

    Definitely error. I don’t see that it makes sense to invoke this statement.

  • Geoffrey Anderson

    Definitely Option 2. This is a pretty horrific dynamic variable that appears to have harmed more than help.

  • Kedar

    Sure Option 2.

  • Antony T Curtis

    I think it should be an error: It would not do what anyone would reasonably expect or unreasonably expect.

  • Jeremy Tinley

    Definitely an error.

  • +1 on making it an error. The purpose of not changing functionality in a minor release is to avoid surprises that break people’s systems. But anyone who would be surprised by this particular change is already breaking their own system (perhaps unknowingly).

    • Thanks Bill. This is a great way perspective/wording of it:
      No changes in GA is a policy to avoid surprises. The current behavior is very much a surprise, so option #2 serves the purpose of avoiding surprises.

      I like it.

  • Przemek

    Good opportunity to make people curse less on MySQL, option 2!

  • Option 2. It’s existence is more terrifying then a shared admin account with super

  • Tom Callahan

    +1 for it erroring. Otherwise, it may never be detected by someone who has it hardcoded in a script/program somewhere.

  • Art van Scheppingen

    +1 on the error.
    The warning is not blocking the command and harm is already done. Even worse: I have seen many times that people in general don’t read the warnings at all and are surprised afterwards that the results are not what they expected them to be.

  • Daniël van Eeden

    +1 for error. If you’re so advanced that you really need this you can patch it yourself.

  • Akshay Suryavanshi

    I vote for making it an Error..

  • Option 2, issuing the command SET GLOBAL sql_log_bin=N produces an error.

  • Option 2, issuing the command SET GLOBAL sql_log_bin=N produces an error.

  • Ammon Sutherland

    +1 for error, and I like Bill Karwin’s reasoning. Also, I will generalize here and say that the sort of people trying to set it now are not the same people who usually stop to investigate a warning.

  • sandeep

    Definitely . Its an error.

  • çħøå∂®øçƙɛ®

    error pls

  • Yoshiaki Yamasaki

    Option 2, issuing the command SET GLOBAL sql_log_bin=N produces an error.

  • A little late to the party, but definitely +1 on Option #2. I imagine anyone using this isn’t aware of the danger

  • Simon J Mudd

    I also agree that option #2 in this case is better. Warnings in MySQL are sometimes easy to overlook and breaking things is something you really don’t want to do, so avoiding such breakage would be good.
    Please also update the documentation to clearly explain the changed behaviour, when it behaved and what the intention of these settings are so that this sort of problem won’t come up again.

  • Ky Patterson

    +1 error. I’m late to the party but wanted to point out that a very likely scenario here is:
    * feature is being used to speed up a lengthy operation like a mass data load
    * operation is being handled by a clueless developer (e.g. me) not a DBA
    * clueless dev typically doesn’t change server variables and so has no idea what “GLOBAL” does
    * clueless dev is using copy-pasted advice from random google search
    * clueless dev is writing a clueless script which will never notice a warning

  • Definitely option #2

  • Jon Stephens

    Bug#67433 has been fixed and closed. No more SET GLOBAL SQL_LOG_BIN as of MySQL 5.5.41/5.6.22/5.7.6. See the bug report for details. Enjoy!