Bug #46638 backport mysql_upgrade --skip-write-binlog into 5.0
Submitted: 10 Aug 2009 21:19 Modified: 4 Oct 2010 9:13
Reporter: Russell Glaue Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.91 OS:Any
Assigned to: CPU Architecture:Any
Tags: disable-log-bin, mysql_upgrade, sql_log_bin

[10 Aug 2009 21:19] Russell Glaue
Description:
The --disable-log-bin option (as used in mysqlbinlog) should be implemented in mysql_upgrade.

Consider that there are two mysql server instances participating in a master-master replication relationship. And you are going to upgrade both to a newer minor or major version.

According to the documentation, http://dev.mysql.com/doc/refman/5.0/en/replication-upgrade.html , the best practice is to turn off replication so the changes applied through mysql_upgrade are not applied.

However, now consider you wish to execute mysql_upgrade against a mysql server while it is online, as illustrated in these steps:
 1) Shutdown MySQL
 2) Change to new MySQL Server binaries
 3) Startup MySQL
 4) Execute mysql_upgrade

It seems the only option is to turn off binary logging for the entire server while I execute mysql_upgrade. Which also means blocking write access to the server while it runs so that statements I do want logged for replication do not occur. 

However, if the --disable-log-bin option were available, I would not have to turn off replication. I would be able to execute mysql_upgrade while the MySQL server is receiving SQL write activity.
The --disable-log-bin option would include "set local sql_log_bin=0" before executing any SQL to enact upgrade modifications.

See also: http://forums.mysql.com/read.php?11,274796,274796#msg-274796

How to repeat:
Install MySQL 5.0.20 (or any early 5.0.x release) on two servers in a master-master replication relationship.

Upgrade one instance to MySQL 5.0.84 (or any newer version).

Execute mysql_upgrade against this instance to complete the 5.0.84 upgrade.

Realize what you had to do in order to keep the changes executed by mysql_upgrade from replicating to the other instance. 

Realize this process would be 10 times easier and faster if mysql_upgrade supported the --disable-log-bin option.

Suggested fix:
Change mysql_upgrade to support the --disable-log-bin option.

This option would essentially execute "set local sql_log_bin=0" before any other SQL used to upgrade the mysql instance.

OR (optionally or additionally)

Add documentation to the MySQL manual that explains what an administrator can do to upgrade mysql schema and tables manually.
[11 Aug 2009 0:52] MySQL Verification Team
Thank you for the bug report.
[25 Nov 2009 14:20] Simon Mudd
I've come across a similar issue with a 5.0 to 5.1 upgrade on a master where the slave had already been upgraded.

Actually I question whether you EVER want to replicate the upgrade statements and actually write them to the binlog. As such I'd think it was more appropriate that the default behaviour is to NOT write to the binlog and perhaps with a special option writing to the binlog would be possible.
[3 Feb 2010 6:54] Sveta Smirnova
Bug #50862 was marked as duplicate of this one.
[3 Feb 2010 9:30] MySQL Verification Team
This looks like a request to backport fix for bug #43579 ...
[3 Feb 2010 18:09] Michael McLagan
I upgraded from 5.1.41 to 5.1.43 and the slave threads stopped with:

Last_Errno: 1580
Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log MODIFY COLUMN
server_id INTEGER UNSIGNED NOT NULL'

I read Bug #43579 and it seems to me that simply turning off binlog during an upgrade is not the best way to handle this -- since it really only affects logging, only updates to log tables should be removed from the binlog.  The option to disable it for all tables seems like shooting a fly with a howitzer.

A better option would be something like:

  SET GLOBAL slow_query_log = 'OFF'
  SET GLOBAL sql_log_bin = 0
  ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL
  SET GLOBAL sql_log_bin = @old_logbin_state
  SET GLOBAL slow_query_log = @old_log_state

Even if that required opening a separate session for just those commands affecting logs.
[10 Jun 2010 13:54] Russell Glaue
I just upgraded to MySQL 5.0.91 and can confirm that this issue still exists.

My work-around was the following steps:
1. Request maintenance window for server downtime from clients
2. shut down the master MySQL server
3. on slaves, stop slave process on all slaves
4. upgrade master MySQL server binaries
5. startup master MySQL with skip-networking and skip-slave-start
6. execute mysql_upgrade
7. shutdown master MySQL
8. startup master MySQL normally, record new binlog file
9. on slaves, change master to new master_log_file from step 8, position 98
10. on slaves, start slave process on all slaves
11. perform steps 1-10 for all slaves.

Had this disable-binlog feature been available, these could have been the steps:
(No maintenance window necessary)
1. upgrade master MySQL binaries
2. restart master MySQL
3. execute mysql_upgrade --disable-binlog
4. execute flush privileges
5. perform steps 1-4 for all slaves.

Note, this is for a minor version upgrade. i.e. 9.0.84 to 9.0.91
[11 Jun 2010 14:28] Russell Glaue
It was documented earlier in this ticket that bug #50862 is a duplicate of this one. But it seems to be more of a duplicate to bug #43579 .

This bug #46638 can be resolved by back porting the patch from commit 84260 to bug #43579

http://lists.mysql.com/commits/84260
--
3111 Li-Bing.Song@sun.com	2009-09-23
...snip...
Master and slave should be upgraded separately. All statements executed by mysql_upgrade will not be binlogged. 
--write-binlog and --skip-write-binlog options are added into mysql_upgrade. These options control whether sql statements are binlogged or not.
--

This patch is for MySQL 5.1 and 6.0.
I ask that it be backported to 5.0, or else have this bug #46638 closed as wont-fix.
[4 Oct 2010 8:54] Susanne Ebrecht
Bug #34060 is set as duplicate of this bug here.
[4 Oct 2010 9:13] Susanne Ebrecht
Many thanks for your request.

We won't backport this into 5.0.