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

[30 Aug 2007 18:55] Omer Barnir
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.

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
                body: BEGIN
             definer: root@localhost
             created: 2007-08-20 00:04:47
            modified: 2007-08-20 00:04:47
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
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
[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:


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:

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.

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