Bug #56242 5.0 > 5.1 upgrade with stored procedures very cumbersome/faulty
Submitted: 25 Aug 2010 7:46 Modified: 10 Jan 2014 17:19
Reporter: Roel Van de Paar Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.1 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[25 Aug 2010 7:46] Roel Van de Paar
Description:
#1 Define a stored procedure in 5.0.90
#2 mysqldump the entire database, including the stored procedure
#3 Reload the dump in 5.1.49. This does not work:

ERROR 1547 (HY000) at line 561: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

This is because it first dumped the 5.1 mysql.proc table (20 columns) and reloaded the 16-column mysql.proc table (16 columns) and mysql 5.1 expects 20 columns.

So, one tries the --force option:

**** Note: by this error alone the "--force" option has become mandatory! (it does not proceed to load data without the --force option; tested) i.e. a user who has a few terabytes of data in a single dump file with a few stored procedures near the end of the dump (but before some remaining data that also needs to be loaded) is going to be in big trouble if he/she did not use --force on the first attempt! ****

roel@roel-ubuntu-vm:/mysql/mysql-5.1.49-linux-i686-glibc23$ ./bin/mysql -uroot -h127.0.0.1 -P5149 --force -A roelt < out.txt 
ERROR 1547 (HY000) at line 561: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 563: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

#4 The stored procedure is now "there" but not visible:

mysql> show create procedure csvtest;
ERROR 1547 (HY000): Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

#5 Running mysql_upgrade gives a set of strange warnings for mysql_fix_privilege_tables:

Running 'mysql_fix_privilege_tables'...
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (latin1). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (latin1_swedish_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
OK

And though mysql_upgrade fixes the issue at hand, here is what the documentation says about this:

"To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions."
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html

However the above is not really talking about mysql_upgrade, as can be seen higher on the same page:

"To upgrade to 5.1 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading."

So, we are advised to use mysqldump to dump/reload the procedures - i.e. mysql_upgrade should not be used in favor of mysqldump (which means something like: dump 5.0 > reload in 5.1 > dump procedures in 5.1 > reload procedures in 5.1)

But that does not work:

roel@roel-ubuntu-vm:/mysql/mysql-5.1.49-linux-i686-glibc23$ ./bin/mysqldump -uroot -h127.0.0.1 -P5149 --all-databases --no-data --routines > out2.txt
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted (1547)

How to repeat:
As per the above.
[25 Aug 2010 7:53] MySQL Verification Team
the description is an annoying symptom of the problem. the problem is that newer versions of mysql refuse to handle older versions system tables.  can we please maintain backward compatibility?
[6 Sep 2012 16:31] jpk dev1
Is the going to be resolved or is it?
[10 Jan 2014 17:19] Paul DuBois
5.0 and 5.1 are both end-of-life (EOL) products. We won't spend time on this.

(Actually, I've already spent quite a bit of time on it, but the problem is quite difficult. Now that both series are EOL, I'm closing this report.)