| Bug #30731 | Creation context of stored routine .. is invalid warning after 5.0-->5.1 upgrade | ||
|---|---|---|---|
| Submitted: | 30 Aug 2007 18:55 | Modified: | 2 Oct 2007 14:00 |
| Reporter: | Omer Barnir (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.1.21 | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[2 Sep 2007 6:53]
Konstantin Osipov
Alik, the fix suggested by Omer seems to make sense. Let's consider setting the values of these columns to system defaults in 5.1 fix_privilege_tables script.
[13 Sep 2007 0:35]
Paul DuBois
Issue has been noted under "Server Changes" at: http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html See also http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html (under "Bugs fixed")
[2 Oct 2007 8:05]
Alexander Nozdrin
As Paul noted, this is intended behavior: <quote> Incompatible change: Several issues were identified for stored programs (stored functions and procedures, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format. To address these problems, the representation for these objects was changed in MySQL 5.1.21. However, the fixes affect all stored programs and views. (For example, you will see warnings about “no creation context.”) 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. Invoke mysqldump with a --default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally defined. </quote> We can not set proper values during upgrade, because this information was lost. It should have been preserved at the time a stored program is created. This warning signals the user that his objects are not properly constructed.
[2 Oct 2007 14:00]
Omer Barnir
Correcting bug status to closed since the documentation was clarified following the logging of this bug
Description: Following an upgrade from 5.0 to 5.1 all references to 5.0 defined stored precedures and functions will include a "Creation context of stored routine .. is invalid" warning. Example: mysql> call initial_empty(); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------+ | Warning | 1598 | Creation context of stored routine `procdb`.`initial_empty` is invalid | +---------+------+------------------------------------------------------------------------+ 1 row in set (0.00 sec) 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 these columns are created with a defaul value of NULL. so after a n upgrade to 5.1 (live or using export/import they end up with a NULL value Such as in: mysql> select * from mysql.proc where name='initial_empty'\G *************************** 1. row *************************** db: procdb name: initial_empty type: PROCEDURE specific_name: initial_empty language: SQL sql_data_access: CONTAINS_SQL is_deterministic: NO security_type: DEFINER param_list: returns: body: BEGIN END definer: root@localhost created: 2007-08-20 00:04:47 modified: 2007-08-20 00:04:47 sql_mode: comment: character_set_client: NULL collation_connection: NULL db_collation: NULL body_utf8: NULL 1 row in set (0.00 sec) and that is generating the warning. A stored procedure/function created in 5.1 has these values set to the system defals. Note manualy altering the mysql.proc table and setting the value of 'character_set_client' to a valid value (sunce as 'latin1'. prevents the error form apearing. Note: this step was done for perposes of analyzing the bug, it should NOT be used as a workaround. How to repeat: 1) Create a 5.0 system and using cd <5.0 install>/mysql-test perl ./mysql-test-run.pl --start-and-exit --vardir=//tmp/var 2) Using the mysql client create a stored procedure USE test CREATE PROCEDURE sp_test BEGIN END; 3) Stop the system and start a 5.1 system pointing to the same database cd <5.1 install>/mysql-test perl ./mysql-test-run.pl --start-dirty --vardir=//tmp/var 4) Run mysql-upgrade on the system <5.1 install>/bin/mysql_upgrade --datadir=/tmp/var --basedir=<5.1 install> --force --socket=/tmp/var/tmp/master.sock --user=root 5) Using the mysql client run USE test CALL sp_test(); >> you wil see '1 warning' SHOW WARNINGS; will dispay: +---------+------+------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------+ | Warning | 1598 | Creation context of stored routine `procdb`.`initial_empty` is invalid | +---------+------+------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Set proper vales in the proc table during upgrade