Bug #9776 mysql.proc table created in wrong charset
Submitted: 8 Apr 2005 21:57 Modified: 19 Jul 2005 7:24
Reporter: Michael G. Zinner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Windows (Windows XP & others)
Assigned to: Alexander Barkov CPU Architecture:Any

[8 Apr 2005 21:57] Michael G. Zinner
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
[8 Apr 2005 23:15] MySQL Verification Team
Should be also linked to:

http://bugs.mysql.com/bug.php?id=9496
[9 Apr 2005 3:22] Paul DuBois
See also http://bugs.mysql.com/bug.php?id=9620
[19 Jul 2005 7:24] Alexander Barkov
Fixed by Pem under terms of bug@11365