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.
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.