MySQL Bugs: #12189: Stored Procedures lost during upgrade to 5.0.10 from 5.0.9

Bug #12189	Stored Procedures lost during upgrade to 5.0.10 from 5.0.9
Submitted:	26 Jul 2005 22:00	Modified:	27 Sep 2008 12:54
Reporter:	Mike Hillyer	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.10	OS:	Windows (Windows)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
When upgrading from MySQL 5.0.9 to 5.0.10 all stored procedures were corrupted and unusable. SELECT * FROM mysql.proc showed garbage.

How to repeat:
Install MySQL 5.0.9, add some stored procedures, upgrade to MySQL 5.0.10
SELECT * FROM mysql.proc;

Suggested fix:
Unknown.

confirm it for windows. plan to test it on linux

When re-installing 5.0.9 they all come back. This suggests that 5.0.10 only makes them inaccessible, not corrupt.

Using Windows XP, server 5.0.10a.

This bug should be marked at least S2 (serious).

Yup, it happened to me, too, on Win 2003.  I freaked out, then uninstalled v10, reinstalled v9, and everything was copacetic.  Obviously, some corruption...

mysql> show create procedure c1  ||
+-----------+----------+-------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                |
+-----------+----------+-------------------------------------------------+
| c1        |          | CREATE PROCEDURE `test`.`c1`()
select version() |
+-----------+----------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> call c1();
    -> ||
+---------------------+
| version()           |
+---------------------+
| 5.0.6-beta-standard |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

....

# rpm -Uhv MySQL-server-standard-5.0.10-0.sles9.i586.rpm
warning: MySQL-server-standard-5.0.10-0.sles9.i586.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
Giving mysqld a couple of seconds to exit nicely
   1:MySQL-server-standard  ########################################### [100%]

.....

mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create procedure c1  ;
+-----------+----------+-------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                |
+-----------+----------+-------------------------------------------------+
| c1        |          | CREATE PROCEDURE `test`.`c1`()
select version() |
+-----------+----------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>

So, it looks like mysql windows  version problem only.

The same bug occurs when upgrading to 5.0.11

I am seeing this happen as well.

There have been changes to the mysql.proc table definition. Please see if it helps to run the script mysql_fix_privilege_tables (or do the ALTER proc statements at the end of the corresponding .sql file). This is mentioned in http://dev.mysql.com/doc/mysql/en/news-5-0-10.html :

The mysql.proc table was not being created properly with the proper utf8 character set and collation, causing server crashes for stored procedure operations if the server was using a multi-byte character set. To take advantage of the bug fix, mysql_fix_privileges_tables should be run to correct the structure of the mysql.proc table. 

It should be safe to do with procedures in the table, but as a precaution, it's probably best to save the original contents anyway.

Thanks for the explanation, has been really helpful to me!

We have no plans to fix 5.0.9 to 5.0.10 upgrade issues.