"SET GLOBAL sql_log_bin" causes data loss
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
mysql-5.5 (Ubuntu) |
Won't Fix
|
Medium
|
Unassigned | ||
Precise |
Fix Released
|
Medium
|
Unassigned | ||
Trusty |
Fix Released
|
Medium
|
Unassigned | ||
mysql-5.6 (Ubuntu) |
Fix Released
|
Medium
|
Unassigned |
Bug Description
SRU INFORMATION
[Background]
This is an API-breaking change proposed by upstream to the 5.5 stable
branch, targeted for Precise and Trusty. This does not fall under the
existing micro-release exception for mysql-5.5, and so must be
considered separately. In consideration of the micro-release exception
requirements, upstream have kindly drawn Ubuntu's attention to this
issue, and are interested in a decision for acceptance in an Ubuntu SRU
before making the change upstream, so that any change made is consistent
across upstream, Ubuntu and any other distributions that wish to follow
suit.
Depending on the time it takes to make a decision, this may also affect
mysql-5.6 in Utopic if this is after feature freeze or release.
We would like a +1 or a -1 from the SRU team (or Technical Board if
appropriate) for each of the two options presented below, preferably
well before any commits are made upstream or any uploads to Ubuntu if
possible.
This request currently comes from MySQL only, but we presume it is not
an issue if Ubuntu makes a single decision on this change for all
variants, if/when other variants wish to follow suit.
[Impact]
The command "SET GLOBAL sql_log_bin = ..." is not useful, causes slaves
to lose sync and thus can cause data loss, but the similar command "SET
sql_log_bin = ..." is useful, often issued interactively, and is close
enough to the "SET GLOBAL ..." command that the latter can be issued by
mistake.
[Proposed Development Fix]
Either: 1) log a warning when the command is used; or 2) refuse to run
the command when requested and generate an error.
Comments from the MySQL community, in response to the upstream request
for feedback
[http://
are overwhelmingly in favour of option 2.
[Proposed Stable Fix]
Same as development fix.
[Regression Potential]
Research
[http://
suggests that there is no known use case for this command, that the only
use of the command is by mistake, causes problems and/or data loss, and
this has happened in the real world in production environments.
[Original Description]
Per:
http://
Upstream MySQL is proposing to change the following syntax to produce an error in current GA releases (5.5 and 5.6):
mysql> SET GLOBAL sql_log_bin = x;
The rationale for this is:
- The syntax will cause slaves to drift out of sync from masters.
- The syntax has no known "correct" use.
- The syntax is too close to SET sql_log_bin=x - which does have a correct use.
- Since this command is often run interactively, it is too easy to incorrectly type the incorrect command (and destroy data).
- Producing a warning instead of an error does not prevent accidental command typing.
Upstream has a MicroReleaseExc
Some additional information. I asked Morgan (upstream):
> For 12.04 or 14.04 specifically, are there real benefits of making this
> change? Even if people are doing it wrong already, is it right to
> suddenly break them if they haven't already noticed a problem? What
> impact will the breakage have to them, if any? Will they be angry about
> that?
We are not aware of any "correct" use case for SET GLOBAL sql_log_bin=x.
i.e. Some users may use it to emulate disabling the binary log, but
since this only applies to new sessions it is not reliable, and is not
fit for this purpose. Another situation might be to start the server
with binary logging disabled, then enable it with SET GLOBAL
sql_log_bin=0, but the same unreliability applies.
> For example, say someone is doing it wrong at the moment, but it doesn't
> impact him directly. With this change, a script he's running suddenly
> breaks, and that failure cascades into some kind of production downtime.
> Could this happen?
That is not impossible, but very unlikely. If their current script does
this, their slaves are now out of sync.
Hence the dilemma where we are needing to be more drastic.