Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.2
-
None
Description
Note: the bug report uses 'comment' column as a known example, but possibly there are others.
On all currently supported versions, table mysql.proc is initially created with column comment of type text:
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','IGNORE_BAD_TABLE_OPTIONS','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` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, |
`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' |
However, when mysql_upgrade is run, it converts the column into char(64)
ALTER TABLE proc MODIFY db |
char(64) collate utf8_bin DEFAULT '' NOT NULL, |
MODIFY definer |
char(", @definer_name_length, ") collate utf8_bin DEFAULT '' NOT NULL, |
MODIFY comment |
char(64) collate utf8_bin DEFAULT '' NOT NULL |
")
|
and then back to text
ALTER TABLE proc MODIFY comment |
text collate utf8_bin NOT NULL |
Naturally, if the column contained a comment longer than 64 symbols, it gets truncated.
It also affects Debian installation, because mysql_upgrade runs there in background, and if other packages which need to use mysql.proc (e.g. Spider) are being installed during this time, they can't create necessary procedures and fail to get configured properly.