MySQL Bugs: #56944: mysql_upgrade doesn't upgrade all tables when ran with --skip-write-binlog optio

Bug #56944	mysql_upgrade doesn't upgrade all tables when ran with --skip-write-binlog optio
Submitted:	22 Sep 2010 17:15	Modified:	24 Sep 2010 20:14
Reporter:	Toomas Aas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.1.50	OS:	Linux (FreeBSD, Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	mysql_upgrade, upgrade

Description:
mysql_upgrade doesn't properly upgrade tables mysql.user and mysql.db when ran with --skip-write-binlog switch.

How to repeat:
Problem was discovered during migration from old server running 5.0.77 to new server running 5.1.50.

1. Dump the databases on old server:
mysqldump -u root -p --all-databases --add-drop-database --allow-keywords \
> /home/username/mysql-databases.sql

2. Transfer the dump to new server

3. Install 5.1.50 on new server

4. Initialize the database directory:
mysql_install_db --datadir=/db/mysql --user=mysql

5. Start the MySQL server:
service mysql-server start

6. Load the dump:
mysql -u root < /home/username/mysql-databases.sql

7. run mysql_upgrade, since previous step loaded the 'mysql' database from 5.0.77. Hoping to save some space from the log, I ran it with --skip-write-binlog switch:
mysql_upgrade -p --skip-write-binlog
(output doesn't indicate any problems)

8. restart the MySQL server:
service mysql-server restart

9. Look at the logfile hostname.err and observe these messages:
00922 15:07:29 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
100922 15:07:29 [ERROR] mysql.user has no `Event_priv` column at position 29
100922 15:07:29 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
Check the tables mysql.db and mysql.user to verify that log is telling the truth :)

10. re-run mysql_upgrade without the --skip-write-binlog switch:
mysql_upgrade -p

11. check the tables mysql.db and mysql.user, observe that the errors noted in step 9 are gone.

12. Restart MySQL server once more and note that there are no error messages that were seen in step 9.

Suggested fix:
Not known

I am able to repeat this.

How to reproduce:

Setup 5.0 replication (one master, one slave).
Doesn't matter ... I tested with both running on same machine.

Now upgrade the slave to 5.1 by using mysql_upgrade.

After that upgrade the master.

Shutdown master and add log-error into my.cnf.

Restart master.

Read error log.

I am able to repeat it with MySQL 5.1.50 but I am not able to repeat it with our actual source repository.

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