Bug #43579 mysql_upgrade tries to alter log tables on replicated database
Submitted: 12 Mar 2009 10:32 Modified: 19 Dec 2009 8:49
Reporter: Vemund Østgaard Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:6.0.10, 5.1 OS:Any
Assigned to: Libing Song CPU Architecture:Any

[12 Mar 2009 10:32] Vemund Østgaard 
Description:
A test is doing upgrade from 6.0.9 to 6.0.10 for a replicated database.

When the mysql_upgrade program is run, it tries to alter the character set to utf8 for the log tables general_log and slow_log. On the master logging is turned off to make this possible, but these queries get replicated to the slave as well and fail there with:

090312 13:18:43 [ERROR] Slave SQL: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE general_log CONVERT TO CHARACTER SET utf8', Error_code: 1580
090312 13:18:43 [ERROR] Slave: You cannot 'ALTER' a log table if logging is enabled Error_code: 1580
090312 13:18:43 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 482179

How to repeat:
It should reproduce like this:

1. Start a 6.0.9 master and a 6.0.9 slave with replication between.
2. Restart both master and slave with 6.0.10 mysqld binary.
3. Run mysql_upgrade on the master.
[12 Mar 2009 11:19] Vemund Østgaard 
My original description was wrong with regards to what version to upgrade from. The test was upgrade from 5.1.32 to 6.0.10.
[20 Mar 2009 10:33] Vemund Østgaard 
To make it clearer what happens.

The mysql_upgrade script is run against the master only, with the expectation that whatever it does gets replicated to the slave.

The mysql_upgrade script tries to turn off logging and alter the general_log and slow_log tables like this:

 SET GLOBAL slow_query_log = 'OFF'
 ALTER TABLE slow_log CONVERT TO CHARACTER SET utf8
 SET GLOBAL slow_query_log = @old_log_state

The ALTER TABLE statement goes into the binlog, but the SET GLOBAL statements do not. This causes the situation on the slave where it tries to execute ALTER TABLE on these log tables without turning off logging first.
[20 Mar 2009 12:50] Vemund Østgaard 
The same problem is in essence also observed when upgrading to 5.1.33, but it is a different ALTER TABLE statement that is executed on the general_log and slow_log tables:

 SET GLOBAL slow_query_log = 'OFF'
 ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL
 SET GLOBAL slow_query_log = @old_log_state
[16 Sep 2009 0:37] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83395

3111 Li-Bing.Song@sun.com	2009-09-16
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded singly. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed
[16 Sep 2009 6:40] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83414

3111 Li-Bing.Song@sun.com	2009-09-16
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded singly. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed
[17 Sep 2009 9:31] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83564

3111 Li-Bing.Song@sun.com	2009-09-17
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded separately. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed
[18 Sep 2009 1:23] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83660

3111 Li-Bing.Song@sun.com	2009-09-18
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded separately. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed
[21 Sep 2009 14:15] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83956

3111 Li-Bing.Song@sun.com	2009-09-21
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      Master and slave should be upgraded separately. All statements executed by
      mysql_upgrade will not be binlogged. It can be done by that msql_upgrade
      assigns 0 to session.sql_log_bin before any statements are executed
[23 Sep 2009 8:14] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84260

3111 Li-Bing.Song@sun.com	2009-09-23
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      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.
[28 Sep 2009 6:24] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84784

3138 Li-Bing.Song@sun.com	2009-09-28
      BUG#43579 mysql_upgrade tries to alter log tables on replicated database 
      
      All statements executed by mysql_upgrade are binlogged and then are replicated to slave.
      This will result in some errors. The report of this bug has demonstrated some examples.
      
      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.
[28 Sep 2009 7:02] Libing Song 
Pushed to mysql-5.1-bugteam and mysql-pe
[6 Oct 2009 9:00] Bugs System 
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:li-bing.song@sun.com-20090928062419-v40rklb7g178tjza) (merge vers: 5.1.40) (pib:11)
[6 Oct 2009 10:57] Jon Stephens 
Documented bugfix in the 5.1.40 changelog as follows:

        By default, all statements executed by the mysql_upgrade program
        on the master are written to the binary log, then replicated to
        the slave. In some cases, this can result in problems; for
        example, it attempted to alter log tables on replicated
        databases (this failed due to logging being enabled).

        As part of this fix, a new mysql_upgrade option --write-binlog
        is added, whose inverse --skip-write-binlog can be used to
        disable binary logging while the upgrade is in progress.

Also noted new option in mysql_upgrade program description in 5.1 Manual.

Set to NDI, waiting for push to 5.4.
[22 Oct 2009 6:33] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 6:43] MySQL Verification Team 
bug #47543 is a duplicate of this
[22 Oct 2009 7:05] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 16:24] Jon Stephens 
Bugfix and new mysql_upgrade option also documented in the 5.5.0 changelog. Closed.
[18 Dec 2009 10:28] Bugs System 
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:44] Bugs System 
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:59] Bugs System 
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:13] Bugs System 
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[19 Dec 2009 8:49] Jon Stephens 
No new changelog entries required. Closing.
[3 Feb 2010 12:11] MySQL Verification Team 
This isn't fixed in 5.0.  For 5.0 we have bug #46638
[4 Oct 2010 9:12] Susanne Ebrecht 
Bug #34060 is set as duplicate of this bug here.