MySQL Bugs: #35789: wrong datatypes for collation and charset columns in EVENTS, ROUTINES, TRIGGERS

Bug #35789	wrong datatypes for collation and charset columns in EVENTS, ROUTINES, TRIGGERS
Submitted:	3 Apr 2008 9:48	Modified:	8 Dec 2008 23:50
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
Collation and character set identifiers are (should be) 64 characters long (max). The columns COLLATION_NAME and CHARACTER_SET_NAME in information_schema.COLLATIONS and information_schema.CHARACTER_SETS respectively reflect this.

However, character set and collation columsn in VIEWS, EVENTS and ROUTINES (which at least conceptually, reference these columns) have a datatype that will only allow 32 characters.

The data types for the referencing columns should be 64 too.

How to repeat:
mysql> select table_name, column_name, column_type from information_schema.columns where table_schema = 'information_schema' and column_name like '%COLLA%';
+---------------------------------------+------------------------+-------------+
| table_name                            | column_name            | column_type |
+---------------------------------------+------------------------+-------------+
| CHARACTER_SETS                        | DEFAULT_COLLATE_NAME   | varchar(64) |
| COLLATIONS                            | COLLATION_NAME         | varchar(64) |
| COLLATION_CHARACTER_SET_APPLICABILITY | COLLATION_NAME         | varchar(64) |
| COLUMNS                               | COLLATION_NAME         | varchar(64) |
| EVENTS                                | COLLATION_CONNECTION   | varchar(32) |
| EVENTS                                | DATABASE_COLLATION     | varchar(32) |
| ROUTINES                              | COLLATION_CONNECTION   | varchar(32) |
| ROUTINES                              | DATABASE_COLLATION     | varchar(32) |
| SCHEMATA                              | DEFAULT_COLLATION_NAME | varchar(64) |
| STATISTICS                            | COLLATION              | varchar(1)  |
| TABLES                                | TABLE_COLLATION        | varchar(64) |
| TRIGGERS                              | COLLATION_CONNECTION   | varchar(32) |
| TRIGGERS                              | DATABASE_COLLATION     | varchar(32) |
| VIEWS                                 | COLLATION_CONNECTION   | varchar(32) |
+---------------------------------------+------------------------+-------------+
14 rows in set (0.03 sec)

mysql> use test;
Database changed
mysql> select table_name, column_name, column_type from information_schema.columns where table_schema = 'information_schema' and column_name like '%CHAR%';
+---------------------------------------+----------------------------+---------------------+
| table_name                            | column_name                | column_type         |
+---------------------------------------+----------------------------+---------------------+
| CHARACTER_SETS                        | CHARACTER_SET_NAME         | varchar(64)         |
| COLLATIONS                            | CHARACTER_SET_NAME         | varchar(64)         |
| COLLATION_CHARACTER_SET_APPLICABILITY | CHARACTER_SET_NAME         | varchar(64)         |
| COLUMNS                               | CHARACTER_MAXIMUM_LENGTH   | bigint(21) unsigned |
| COLUMNS                               | CHARACTER_OCTET_LENGTH     | bigint(21) unsigned |
| COLUMNS                               | CHARACTER_SET_NAME         | varchar(64)         |
| EVENTS                                | CHARACTER_SET_CLIENT       | varchar(32)         |
| ROUTINES                              | CHARACTER_SET_CLIENT       | varchar(32)         |
| SCHEMATA                              | DEFAULT_CHARACTER_SET_NAME | varchar(64)         |
| TRIGGERS                              | CHARACTER_SET_CLIENT       | varchar(32)         |
| VIEWS                                 | CHARACTER_SET_CLIENT       | varchar(32)         |
+---------------------------------------+----------------------------+---------------------+
11 rows in set (0.02 sec)

Suggested fix:
ensure all I_S columns for collation names and character set names are exactly the same as the types of the columns they reference.

Verified as described.

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/45108

ChangeSet@1.2585, 2008-04-09 12:38:00+05:00, gluh@mysql.com +4 -0
  Bug#35789 wrong datatypes for collation and charset columns in EVENTS, ROUTINES, TRIGGERS
  changed 'charset', 'collation' field length from 64 to MY_CS_NAME_SIZE(32)
  in tables:
  SCHEMATA, TABLES, COLUMNS, CHARACTER_SETS,
  COLLATIONS, COLLATION_CHARACTER_SET_APPLICABILITY

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/58615

2707 Sergey Glukhov	2008-11-13
      Bug#35789 wrong datatypes for collation and charset columns in EVENTS, ROUTINES, TRIGGERS
      changed 'charset', 'collation' field length from 64 to MY_CS_NAME_SIZE(32)
      in tables:
      SCHEMATA, TABLES, COLUMNS, CHARACTER_SETS,
      COLLATIONS, COLLATION_CHARACTER_SET_APPLICABILITY

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/58616

2924 Sergey Glukhov	2008-11-13 [merge]
      Bug#35789 wrong datatypes for collation and charset columns in EVENTS, ROUTINES, TRIGGERS
      changed 'charset', 'collation' field length from 64 to MY_CS_NAME_SIZE(32)
      in tables:
      SCHEMATA, TABLES, COLUMNS, CHARACTER_SETS,
      COLLATIONS, COLLATION_CHARACTER_SET_APPLICABILITY
      addon: 'show_check' test is enabled

Pushed into 5.1.31  (revid:sergey.glukhov@sun.com-20081113095020-0ofmav3ph1is16mr) (version source revid:azundris@mysql.com-20081114081134-pswy3m5go7r64m1p) (pib:5)

Pushed into 6.0.9-alpha  (revid:sergey.glukhov@sun.com-20081113111102-fyk21wakxv3hdsrt) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

The columns that store character set and collation names in several
INFORMATION_SCHEMA tables were lengthened because they were not long
enough to store some possible values: SCHEMATA, TABLES, COLUMNS,
CHARACTER SETS, COLLATIONS, and
COLLATION_CHARACTER_SET_APPLICABILITY.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)