Description:
Following a downgrade from 5.1 to 5.0 (regardless if the system was created in 5.1 or was originaly upgraded from 5.0), all stored procedures and function fail to operate in the downgraded system. This applies to SP/functions that were created in 5.1 or were created in 5.0 prioir to upgrade. This also causes any object that referances SP/functions -- such as a view that includes a function call (e.g. CREATE VIEW V1 AS SELECT f(...) ... ) to fail being loaded when the mysqldump dump file is imported to the 5.0 system.
The problem is a result of the fact that the upgrade to 5.1 adds four columns to the mysql.proc table.
- character_set_client
- collation_connection
- db_collation
- body_utf8
Once the system is downgraded, these four extra columns cause the 5.0 server to view the mysql.proc table as corrupted.
As a result, SP/functions cannot be
- created
- executed
- used in other objects
- displayed
- droped
- etc.
If the four columns are manualy removed after downgrade (using ALTER TABLE mysql.proc COLUMN <col_name>), the SP/functions will function as expected. However this cannot be considered a workaround since some of the 'damage' has already happened during the import stage (as described above.
How to repeat:
1) Create a 5.0 system
2) Using the client create a function, for example
USE TEST;
CREATE FUNCTION f50() RETURNS INT RETURN 42;
3) Verify the function works
4) Upgrade the system to 5.1
5) run the mysql_upgrade utility
6) Using the 5.1 client
a. check that the function stil works
USE TEST;
SELECT f50();
b. create similar function
CREATE FUNCTION f51() RETURNS INT RETURN 7;
and verify that it works
c. create a view referancing one of the functions:
CREATE VIEW v51 AS SELECT f51();
and verify that it works
7) Export the database using the 5.1 version of mysqldump (include
the --all-databases oprion)
8) Downgrade the system and import the dump file created in #7.
>> The import will fail to load the view with:
ERROR 1457 (HY000) at line xxx: Failed to load routine test.f51. The table
mysql.proc is missing, corrupt, or contains bad data (internal code -5)
9) Log into the 5.0 client
>> Trying to execute either function (e.g SELECT test.f50() ) will result in
the same error as above.
>> Trying to create a new function will return
ERROR 1307 (HY000): Failed to CREATE FUNCTION
>> Performing a SHOW CREATE FUNCTION test.f50 will return:
FUNCTION f50 does not exis
>> SELECT * from mysql.proc wil show the two function definitions
10) Altering the proc table and dropping the 5.1 added columns will cause the checkes mentioned in #9 to work properly.
Downgrade the system to the original
Suggested fix:
Either add a mechanizm that prevents the extra column definition and data from being loaded from the dump file if the server version in less than 5.1 (51000) or change the 5.0 server to ignore extra columns if exist in the mysql.proc table.
Description: Following a downgrade from 5.1 to 5.0 (regardless if the system was created in 5.1 or was originaly upgraded from 5.0), all stored procedures and function fail to operate in the downgraded system. This applies to SP/functions that were created in 5.1 or were created in 5.0 prioir to upgrade. This also causes any object that referances SP/functions -- such as a view that includes a function call (e.g. CREATE VIEW V1 AS SELECT f(...) ... ) to fail being loaded when the mysqldump dump file is imported to the 5.0 system. The problem is a result of the fact that the upgrade to 5.1 adds four columns to the mysql.proc table. - character_set_client - collation_connection - db_collation - body_utf8 Once the system is downgraded, these four extra columns cause the 5.0 server to view the mysql.proc table as corrupted. As a result, SP/functions cannot be - created - executed - used in other objects - displayed - droped - etc. If the four columns are manualy removed after downgrade (using ALTER TABLE mysql.proc COLUMN <col_name>), the SP/functions will function as expected. However this cannot be considered a workaround since some of the 'damage' has already happened during the import stage (as described above. How to repeat: 1) Create a 5.0 system 2) Using the client create a function, for example USE TEST; CREATE FUNCTION f50() RETURNS INT RETURN 42; 3) Verify the function works 4) Upgrade the system to 5.1 5) run the mysql_upgrade utility 6) Using the 5.1 client a. check that the function stil works USE TEST; SELECT f50(); b. create similar function CREATE FUNCTION f51() RETURNS INT RETURN 7; and verify that it works c. create a view referancing one of the functions: CREATE VIEW v51 AS SELECT f51(); and verify that it works 7) Export the database using the 5.1 version of mysqldump (include the --all-databases oprion) 8) Downgrade the system and import the dump file created in #7. >> The import will fail to load the view with: ERROR 1457 (HY000) at line xxx: Failed to load routine test.f51. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5) 9) Log into the 5.0 client >> Trying to execute either function (e.g SELECT test.f50() ) will result in the same error as above. >> Trying to create a new function will return ERROR 1307 (HY000): Failed to CREATE FUNCTION >> Performing a SHOW CREATE FUNCTION test.f50 will return: FUNCTION f50 does not exis >> SELECT * from mysql.proc wil show the two function definitions 10) Altering the proc table and dropping the 5.1 added columns will cause the checkes mentioned in #9 to work properly. Downgrade the system to the original Suggested fix: Either add a mechanizm that prevents the extra column definition and data from being loaded from the dump file if the server version in less than 5.1 (51000) or change the 5.0 server to ignore extra columns if exist in the mysql.proc table.