Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does not set values
Submitted: 17 Dec 2008 23:14 Modified: 12 Mar 2010 15:43
Reporter: Alexander Rubin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:5.1 all OS:Any
Assigned to: Lars-Erik Bjørk CPU Architecture:Any
Tags: Collations, mysql_upgrade, proc, upgrade
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[17 Dec 2008 23:14] Alexander Rubin
Description:
mysql_upgrade (ver 5.1) add 3 fields (mysql.proc.character_set_client, column mysql.proc.collation_connection, mysql.proc.db_collation) to mysql.proc table but does not set any values. So when we run stored procedures, which were created with mysql 5.0, the warning is logged into error log. 

See "how to repeat" for an example

How to repeat:
In MySQL 5.0:

mysql> show create table mysql.proc\G
*************************** 1. row ***************************
       Table: proc
Create Table: CREATE TABLE `proc` (
  `db` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  `name` char(64) NOT NULL default '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL default '',
  `language` enum('SQL') NOT NULL default 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL default 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL default 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL default 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` char(64) NOT NULL default '',
  `body` longblob NOT NULL,
  `definer` char(77) character set utf8 collate utf8_bin NOT NULL default '',
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL default '',
  `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

2. create a stored procedure
example:
mysql> delimiter $$
mysql> CREATE DEFINER = 'root'@'localhost' PROCEDURE account_count()
    -> BEGIN
    ->   SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

3. upgrade to mysql 5.1 and run mysql_upgrade
mysql> show create table mysql.proc\G
*************************** 1. row ***************************
       Table: proc
Create Table: CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL DEFAULT '',
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` longblob NOT NULL,
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

== 3 new fields added after comment ==

4. mysql> call account_count();
5. examine error log:
081217 19:10:29 [Warning] Stored routine 'test'.'account_count': invalid value in column mysql.proc.character_set_client.
081217 19:10:29 [Warning] Stored routine 'test'.'account_count': invalid value in column mysql.proc.collation_connection.
081217 19:10:29 [Warning] Stored routine 'test'.'account_count': invalid value in column mysql.proc.db_collation.

6. Observe "null" values in 3 new fields:
mysql> select * from mysql.proc\G
*************************** 1. row ***************************
                  db: test
                name: account_count
                type: PROCEDURE
       specific_name: account_count
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list:
             returns:
                body: BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END
             definer: root@localhost
             created: 2008-12-17 19:08:36
            modified: 2008-12-17 19:08:36
            sql_mode:
             comment:
character_set_client: NULL
collation_connection: NULL
        db_collation: NULL
           body_utf8: NULL

Suggested fix:
Either add values for this new fields or document this as an known issue.
We can set the same collation as the database the procedure is in.
[18 Dec 2008 7:02] Sveta Smirnova
Thank you for the report.

Which exact versions of MySQL 5.0 and 5.1 do you use?

With current development sources I get other results:

$./bin/mysql_upgrade -uroot  --socket=/tmp/mysql_ssmirnova.sock 
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
Running 'mysqlcheck'...
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" to fix it!
mysql.help_relation                                OK
mysql.help_topic
error    : Table upgrade required. Please do "REPAIR TABLE `help_topic`" to fix it!
mysql.host                                         OK
mysql.proc
error    : Table upgrade required. Please do "REPAIR TABLE `proc`" to fix it!
mysql.procs_priv                                   OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name
error    : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" to fix it!
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Repairing tables
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_topic                                   OK
mysql.proc                                         OK
mysql.time_zone_name                               OK
Running 'mysql_fix_privilege_tables'...
OK

$./bin/mysql -uroot  --socket=/tmp/mysql_ssmirnova.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.31-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> call account_count();
+---------------------+----------+
| Number of accounts: | COUNT(*) |
+---------------------+----------+
| Number of accounts: |        5 | 
+---------------------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1601 | Creation context of stored routine `test`.`account_count` is invalid | 
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> REPAIR TABLE `proc`;
+------------+--------+----------+----------+
| Table      | Op     | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.proc | repair | status   | OK       | 
+------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> use test
Database changed

mysql> call account_count();
+---------------------+----------+
| Number of accounts: | COUNT(*) |
+---------------------+----------+
| Number of accounts: |        5 | 
+---------------------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[18 Dec 2008 20:57] Alexander Rubin
Ok, after running "repair table mysql.proc" the warnings are gone.
Then the question is why it is not running repair table from mysql_upgrade
[19 Dec 2008 7:55] Sveta Smirnova
Thank you for the feedback.

Verified as described in my last comment.

mysqlcheck call really repairs mysql.proc table, but after changing fields it should be updated again which is not done.

Workaround: run REPAIR TABLE mysql.proc manually.
[26 Feb 2009 17:32] Paul Dubois
Another workaround for the mysql.proc table problem is described already in the manual. See http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html under "Server Changes":

Incompatible change: Several issues were identified for stored
programs (stored procedures and functions, 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.
[10 Aug 2009 20:21] Omer Barnir
(private note): on a second look this bug seems very similar to bug bug#30731.
[13 Nov 2009 9:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90319

3200 lars-erik.bjork@sun.com	2009-11-13
      This is a patch for bug#41569.
      "mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does
      not set values".
      
      mysql_upgrade (ver 5.1) adds 3 fields (character_set_client, 
      collation_connection and db_collation) to the mysql.proc table, but 
      does not set any values. When we run stored procedures, which were 
      created with mysql 5.0, a warning is logged into the error log.
      
      The solution to this is for mysql_upgrade to set default best guess values
      for these fields. A warning is also written during upgrade, to make the
      user aware that default values are set.
      
      This patch still lacks a test, so this is not a complete commit.
     @ client/mysql_upgrade.c
        Result lines which start with "WARNING" are passed through to the output. This way we have a way of triggering WARNING-messages during upgrade directly from the .sql-script.
     @ scripts/mysql_system_tables_fix.sql
        The new fields are populated, and warnings is written.
[16 Nov 2009 11:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90497

3200 lars-erik.bjork@sun.com	2009-11-16
      This is a patch for bug#41569.
      "mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does
      not set values".
            
      mysql_upgrade (ver 5.1) adds 3 fields (character_set_client, 
      collation_connection and db_collation) to the mysql.proc table, but 
      does not set any values. When we run stored procedures, which were 
      created with mysql 5.0, a warning is logged into the error log.
            
      The solution to this is for mysql_upgrade to set default best guess values
      for these fields. A warning is also written during upgrade, to make the
      user aware that default values are set.
     @ client/mysql_upgrade.c
        Result lines which start with "WARNING" are passed through to the output.
        This way we have a way of triggering WARNING-messages during upgrade
        directly from the .sql-script.
     @ mysql-test/r/mysql_upgrade.result
        Expected result of the test.
     @ mysql-test/t/mysql_upgrade.test
        Added a test-case for the bug.
     @ scripts/mysql_system_tables_fix.sql
        The new fields are populated, and warnings are written.
[2 Dec 2009 13:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92456

3238 lars-erik.bjork@sun.com	2009-12-02
      This is a patch for bug#41569.
      "mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does
      not set values".
                  
      mysql_upgrade (ver 5.1) adds 3 fields (character_set_client, 
      collation_connection and db_collation) to the mysql.proc table, but 
      does not set any values. When we run stored procedures, which were 
      created with mysql 5.0, a warning is logged into the error log.
                  
      The solution to this is for mysql_upgrade to set default best guess values
      for these fields. A warning is also written during upgrade, to make the
      user aware that default values are set.
     @ client/mysql_upgrade.c
        Result lines which start with "WARNING" are passed through to the output.
        This way we have a way of triggering WARNING-messages during upgrade
        directly from the .sql-script.
     @ mysql-test/r/mysql_upgrade.result
        Expected result of the test.
     @ mysql-test/t/mysql_upgrade.test
        Added a test case for the bug.
     @ scripts/mysql_system_tables_fix.sql
        The new fields are populated, and warnings are written.
[3 Dec 2009 10:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92636

3240 lars-erik.bjork@sun.com	2009-12-03
      This is a patch for bug#41569.
      "mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does
      not set values".
                  
      mysql_upgrade (ver 5.1) adds 3 fields (character_set_client, 
      collation_connection and db_collation) to the mysql.proc table, but 
      does not set any values. When we run stored procedures, which were 
      created with mysql 5.0, a warning is logged into the error log.
                  
      The solution to this is for mysql_upgrade to set default best guess
      values for these fields. A warning is also written during upgrade, to
      make the user aware that default values are set.
     @ client/mysql_upgrade.c
        Result lines which start with "WARNING" are passed through to the output. 
        This way we have a way of triggering WARNING-messages during upgrade 
        directly from the .sql-script.
     @ mysql-test/r/mysql_upgrade.result
        Expected result of the test.
     @ mysql-test/t/mysql_upgrade.test
        Added a test-case for the bug.
     @ scripts/mysql_system_tables_fix.sql
        The new fields are populated, and warnings are written.
[3 Dec 2009 16:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92711

3242 lars-erik.bjork@sun.com	2009-12-03
      This is a patch for bug#41569.
      "mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does
      not set values".
                  
      mysql_upgrade (ver 5.1) adds 3 fields (character_set_client, 
      collation_connection and db_collation) to the mysql.proc table, but 
      does not set any values. When we run stored procedures, which were 
      created with mysql 5.0, a warning is logged into the error log.
                  
      The solution to this is for mysql_upgrade to set default best guess
      values for these fields. A warning is also written during upgrade, to
      make the user aware that default values are set.
     @ client/mysql_upgrade.c
        Result lines which start with "WARNING" are passed through to the output. 
        This way we have a way of triggering WARNING-messages during upgrade 
        directly from the .sql-script.
     @ mysql-test/r/mysql_upgrade.result
        Expected result of the test.
     @ mysql-test/t/mysql_upgrade.test
        Added a test-case for the bug.
     @ scripts/mysql_system_tables_fix.sql
        The new fields are populated, and warnings are written.
[4 Dec 2009 16:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92908

3245 Davi Arnaut	2009-12-04
      Bug#41569: mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table but does not set values
      
      Post-merge fix: Redirect stderr to a file as to avoid buffering
      problems due to redirecting stderr to stdout.
     @ mysql-test/r/mysql_upgrade.result
        Update test case result.
     @ mysql-test/t/mysql_upgrade.test
        Redirect stderr to a file, cat and remove.
[7 Dec 2009 16:48] Lars-Erik Bjørk
Pushed into 5.1.42 and 6.0.14-alpha
[19 Dec 2009 8:27] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:31] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211164058-ycpe0f20d1c4h1gl) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[5 Jan 2010 11:58] MC Brown
A note has been added to the 5.51, 5.1.42 and 6.0.14 changelog: 

The mysql_upgrade command would create three additional fields to the mysql.proc table (character_set_client, collation_connection, and db_collation), but did not populate the fields with correct values. This would lead to error messages reported during stored procedure execution.
[15 Jan 2010 8:59] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:davi.arnaut@sun.com-20091204160020-z1t29xz3x6clazif) (merge vers: 5.1.42) (pib:16)
[15 Jan 2010 13:55] MC Brown
Noted in the 5.1.43 changelog
[12 Mar 2010 14:17] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:33] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:45] MC Brown
No changelog entry required (already noted in earlier changelog)
[12 Mar 2010 14:49] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 15:43] MC Brown
No changelog entry needed.