MySQL Bugs: #30029: mysql_upgrade fails for 5.0 -> 5.1.21, 5.1.20 -> 5.1.21 upgrades

Bug #30029	mysql_upgrade fails for 5.0 -> 5.1.21, 5.1.20 -> 5.1.21 upgrades
Submitted:	25 Jul 2007 9:55	Modified:	2 Aug 2007 3:32
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.21	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
After the CS patch (BUG#11986 & co) mysql_upgrade fails.

The problem is that:

  - the CS patch introduced new columns in mysql.proc table
    So, in order to create a stored program, mysql.proc should be
    upgraded first.

  - mysql_fix_privilege_tables.sql uses procedures to create general_log
    and slow_log tables if needed. The procedures are used before
    mysql.proc is upgraded.

How to repeat:
- Shut down 5.1.21 server
- Remove 5.1.21 data directory
- Copy data directory from a 5.1.20 installation
- Restart 5.1.21 server
- Run mysql_upgrade

Suggested fix:
Use dynamic SQL instead of stored procedures in mysql_system_tables.sql.

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/31634

ChangeSet@1.2560, 2007-07-26 20:05:01+04:00, anozdrin@ibm. +1 -0
  Fix for BUG#30029: mysql_upgrade fails for 5.0 -> 5.1.21,
  5.1.20 -> 5.1.21 upgrades.
  
  We generate mysql_fix_privilege.sql file, which contains SQL
  statements required to upgrade the system database. This script
  is generated by concatenation of mysql_system_tables.sql and
  mysql_system_tables_fix.sql.
  
  The problem was that
    - in order to create general_log and slow_log tables we use
      stored programs in mysql_system_tables.sql;
    - we upgrade mysql.proc table in mysql_system_tables_fix.sql;
  
  So, if mysql.proc table needs to be upgraded, stored procedures
  can not be used in mysql_system_tables.sql.
  
  In other words, in mysql_system_tables.sql stored programs must
  not be used because they may be unavailable at this point.
  
  The fix is to use dynamic SQL instead of stored programs.
  
  There is no test case for this bug because our test suite
  is not suitable for such test cases. system_mysql_db_fix* test
  cases play with the database "test". Here we need to modify
  the system database and we can not do that in the test suite.

Pushed into 5.1-runtime.

Pushed into 5.1.21-beta

Noted in 5.1.21 changelog.

(Added a note to the Bug#11986 entry that the patch for Bug#11986 broke mysql_upgrade.)

Pushed into 5.1.23-beta