Description:
The table mysql.proc ships in latin1. It should be created in CHARACTER SET utf8 COLLATION utf8_bin instead.
Mixing the collations in the mysql schema breaks the MySQL Administrator user management.
How to repeat:
mysql> SHOW CREATE TABLE mysql.proc\G
*************************** 1. row ***************************
Table: proc
Create Table: CREATE TABLE `proc` (
`db` varchar(64) character set latin1 collate latin1_bin NOT NULL default '',
`name` varchar(64) NOT NULL default '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL default 'FUNCTION',
`specific_name` varchar(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` varchar(64) NOT NULL default '',
`body` blob NOT NULL,
`definer` varchar(77) character set latin1 collate latin1_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','O
NLY_FULL_GROUP_BY','NO_UNSI
GNED_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') NOT NULL
default '',
`comment` varchar(64) character set latin1 collate latin1_bin NOT NULL default
'',
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stored Procedures'
1 row in set (0.00 sec)
Suggested fix:
Change the script that creates the mysql.proc table to include the correct CHARACTER SET utf8 COLLATION utf8_bin
Description: The table mysql.proc ships in latin1. It should be created in CHARACTER SET utf8 COLLATION utf8_bin instead. Mixing the collations in the mysql schema breaks the MySQL Administrator user management. How to repeat: mysql> SHOW CREATE TABLE mysql.proc\G *************************** 1. row *************************** Table: proc Create Table: CREATE TABLE `proc` ( `db` varchar(64) character set latin1 collate latin1_bin NOT NULL default '', `name` varchar(64) NOT NULL default '', `type` enum('FUNCTION','PROCEDURE') NOT NULL default 'FUNCTION', `specific_name` varchar(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` varchar(64) NOT NULL default '', `body` blob NOT NULL, `definer` varchar(77) character set latin1 collate latin1_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','O NLY_FULL_GROUP_BY','NO_UNSI GNED_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') NOT NULL default '', `comment` varchar(64) character set latin1 collate latin1_bin NOT NULL default '', PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stored Procedures' 1 row in set (0.00 sec) Suggested fix: Change the script that creates the mysql.proc table to include the correct CHARACTER SET utf8 COLLATION utf8_bin