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