#At file:///home/lb200670/mysql/41569-mysql-5.1-bugteam/ based on revid:bar@stripped
3240 lars-erik.bjork@stripped 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.
modified:
client/mysql_upgrade.c
mysql-test/r/mysql_upgrade.result
mysql-test/t/mysql_upgrade.test
scripts/mysql_system_tables_fix.sql
=== modified file 'client/mysql_upgrade.c'
--- a/client/mysql_upgrade.c 2009-09-28 06:24:19 +0000
+++ b/client/mysql_upgrade.c 2009-12-03 10:53:10 +0000
@@ -778,6 +778,10 @@ static int run_sql_fix_privilege_tables(
found_real_errors++;
print_line(line);
}
+ else if (strncmp(line, "WARNING", 7) == 0)
+ {
+ print_line(line);
+ }
} while ((line= get_line(line)) && *line);
}
=== modified file 'mysql-test/r/mysql_upgrade.result'
--- a/mysql-test/r/mysql_upgrade.result 2009-01-26 14:20:33 +0000
+++ b/mysql-test/r/mysql_upgrade.result 2009-12-03 10:53:10 +0000
@@ -127,3 +127,45 @@ mysql.time_zone_transition
mysql.time_zone_transition_type OK
mysql.user OK
set GLOBAL sql_mode=default;
+#
+# Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table
+# but does not set values.
+#
+CREATE PROCEDURE testproc() BEGIN END;
+UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc';
+UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc';
+UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc';
+WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (latin1). Please verify if necessary.
+WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (latin1_swedish_ci). Please verify if necessary.
+WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
+mtr.global_suppressions OK
+mtr.test_suppressions OK
+mysql.columns_priv OK
+mysql.db OK
+mysql.event OK
+mysql.func OK
+mysql.general_log
+Error : You can't use locks with log tables.
+status : OK
+mysql.help_category OK
+mysql.help_keyword OK
+mysql.help_relation OK
+mysql.help_topic OK
+mysql.host OK
+mysql.ndb_binlog_index OK
+mysql.plugin OK
+mysql.proc OK
+mysql.procs_priv OK
+mysql.servers OK
+mysql.slow_log
+Error : You can't use locks with log tables.
+status : OK
+mysql.tables_priv OK
+mysql.time_zone OK
+mysql.time_zone_leap_second OK
+mysql.time_zone_name OK
+mysql.time_zone_transition OK
+mysql.time_zone_transition_type OK
+mysql.user OK
+CALL testproc();
+DROP PROCEDURE testproc;
=== modified file 'mysql-test/t/mysql_upgrade.test'
--- a/mysql-test/t/mysql_upgrade.test 2009-07-28 19:59:38 +0000
+++ b/mysql-test/t/mysql_upgrade.test 2009-12-03 10:53:10 +0000
@@ -89,3 +89,20 @@ DROP USER mysqltest1@'%';
set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE';
--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1
eval set GLOBAL sql_mode=default;
+
+
+--echo #
+--echo # Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table
+--echo # but does not set values.
+--echo #
+
+# Create a stored procedure and set the fields in question to null.
+# When running mysql_upgrade, a warning should be written.
+
+CREATE PROCEDURE testproc() BEGIN END;
+UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc';
+UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc';
+UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc';
+--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1
+CALL testproc();
+DROP PROCEDURE testproc;
=== modified file 'scripts/mysql_system_tables_fix.sql'
--- a/scripts/mysql_system_tables_fix.sql 2009-10-27 10:09:36 +0000
+++ b/scripts/mysql_system_tables_fix.sql 2009-12-03 10:53:10 +0000
@@ -415,18 +415,48 @@ ALTER TABLE proc ADD character_set_clien
ALTER TABLE proc MODIFY character_set_client
char(32) collate utf8_bin DEFAULT NULL;
+SELECT CASE WHEN COUNT(*) > 0 THEN
+CONCAT ("WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (", @@character_set_client, "). Please verify if necessary.")
+ELSE NULL
+END
+AS value FROM proc WHERE character_set_client IS NULL;
+
+UPDATE proc SET character_set_client = @@character_set_client
+ WHERE character_set_client IS NULL;
+
ALTER TABLE proc ADD collation_connection
char(32) collate utf8_bin DEFAULT NULL
AFTER character_set_client;
ALTER TABLE proc MODIFY collation_connection
char(32) collate utf8_bin DEFAULT NULL;
+SELECT CASE WHEN COUNT(*) > 0 THEN
+CONCAT ("WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (", @@collation_connection, "). Please verify if necessary.")
+ELSE NULL
+END
+AS value FROM proc WHERE collation_connection IS NULL;
+
+UPDATE proc SET collation_connection = @@collation_connection
+ WHERE collation_connection IS NULL;
+
ALTER TABLE proc ADD db_collation
char(32) collate utf8_bin DEFAULT NULL
AFTER collation_connection;
ALTER TABLE proc MODIFY db_collation
char(32) collate utf8_bin DEFAULT NULL;
+SELECT CASE WHEN COUNT(*) > 0 THEN
+CONCAT ("WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.")
+ELSE NULL
+END
+AS value FROM proc WHERE db_collation IS NULL;
+
+UPDATE proc AS p SET db_collation =
+ ( SELECT DEFAULT_COLLATION_NAME
+ FROM INFORMATION_SCHEMA.SCHEMATA
+ WHERE SCHEMA_NAME = p.db)
+ WHERE db_collation IS NULL;
+
ALTER TABLE proc ADD body_utf8 longblob DEFAULT NULL
AFTER db_collation;
ALTER TABLE proc MODIFY body_utf8 longblob DEFAULT NULL;
Attachment: [text/bzr-bundle] bzr/lars-erik.bjork@sun.com-20091203105310-3obybcp6a8wsq9p9.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (lars-erik.bjork:3240) Bug#41569 | lars-erik.bjork | 3 Dec |
