MySQL Bugs: #71355: Strange data type is used for name-related columns in some I_S.INNODB

Bug #71355	Strange data type is used for name-related columns in some I_S.INNODB_* tables
Submitted:	12 Jan 2014 17:22	Modified:	13 Jan 2014 5:04
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, innodb_sys_foreign, innodb_sys_foreign_cols

Description:
Manual page about INFORMATION_SCHEMA.INNODB_SYS_FOREIGN does NOT list data types and does NOT explain content for the FOR_NAME and REF_NAME columns, but these are essentially table names qualified with database name:

mysql> select * from information_schema.innodb_sys_foreign;
+------------------+-----------+-----------+--------+------+
| ID               | FOR_NAME  | REF_NAME  | N_COLS | TYPE |
+------------------+-----------+-----------+--------+------+
| test/t02_ibfk_1  | test/t02  | test/t01  |      1 |    0 |
| test/t03_ibfk_1  | test/t03  | test/t02  |      1 |    0 |
| test/t04_ibfk_1  | test/t04  | test/t03  |      1 |    0 |
| test/t05_ibfk_1  | test/t05  | test/t04  |      1 |    0 |
| test/t06_ibfk_1  | test/t06  | test/t05  |      1 |    0 |
...

For some reason "/" is used in between database name and table name (not "." as it is required by MySQL's SQL dialect), but what's even more strange is the data type used:

mysql> desc information_schema.innodb_sys_foreign;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID       | varchar(193)     | NO   |     |         |       |
| FOR_NAME | varchar(193)     | NO   |     |         |       |
| REF_NAME | varchar(193)     | NO   |     |         |       |
| N_COLS   | int(11) unsigned | NO   |     | 0       |       |
| TYPE     | int(11) unsigned | NO   |     | 0       |       |
+----------+------------------+------+-----+---------+-------+
5 rows in set (0.25 sec)

I wonder where that 193 comes from, if both database name and table name identifies are usually limited to 64 characters, in other INFORMATION_SCHEMA tables serving similar purpose for example:

mysql> desc information_schema.key_column_usage;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |     |         |       |
| TABLE_CATALOG                 | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME                   | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.07 sec)

mysql> desc information_schema.referential_constraints;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.04 sec)

Same 193 one can see in:

mysql> desc information_schema.innodb_sys_foreign_cols;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ID           | varchar(193)     | NO   |     |         |       |
| FOR_COL_NAME | varchar(193)     | NO   |     |         |       |
| REF_COL_NAME | varchar(193)     | NO   |     |         |       |
| POS          | int(11) unsigned | NO   |     | 0       |       |
+--------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

where 193 is even more weird, as the content of FOR_COL_NAME and REF_COL_NAME is actually a column name (identified) without any qualification:

mysql> select * from information_schema.innodb_sys_foreign_cols;
+------------------+--------------+--------------+-----+
| ID               | FOR_COL_NAME | REF_COL_NAME | POS |
+------------------+--------------+--------------+-----+
| test/t02_ibfk_1  | t_id         | id           |   0 |
| test/t03_ibfk_1  | t_id         | id           |   0 |
| test/t04_ibfk_1  | t_id         | id           |   0 |
| test/t05_ibfk_1  | t_id         | id           |   0 |
...

How to repeat:
Create some InnoDB tables with foreign keys. Then execute:

desc information_schema.innodb_sys_foreign;
select * from information_schema.innodb_sys_foreign;

desc information_schema.innodb_sys_foreign_cols;
select * from information_schema.innodb_sys_foreign_cols;

and try to understand why this long data types are used for some columns.

Suggested fix:
Make up your mind and use consistent data types for the same data in different INFORMATION_SCHEMA tables and/or document in details why some tables allow longer values than one can assume based on MySQL rules for identifiers.

Hello Valeriy,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh

// With 5.6.15(GA)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.15-debug-log |
+------------------+
1 row in set (0.04 sec)
mysql> 
mysql> use test
Database changed
mysql> CREATE TABLE parent (
    ->     id INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> CREATE TABLE child (
    ->     id INT, 
    ->     parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     FOREIGN KEY (parent_id) 
    ->         REFERENCES parent(id)
    ->         ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> desc information_schema.innodb_sys_foreign;                                                                                                                               +----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID       | varchar(193)     | NO   |     |         |       |
| FOR_NAME | varchar(193)     | NO   |     |         |       |
| REF_NAME | varchar(193)     | NO   |     |         |       |
| N_COLS   | int(11) unsigned | NO   |     | 0       |       |
| TYPE     | int(11) unsigned | NO   |     | 0       |       |
+----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_foreign;
+-------------------+------------+-------------+--------+------+
| ID                | FOR_NAME   | REF_NAME    | N_COLS | TYPE |
+-------------------+------------+-------------+--------+------+
| test/child_ibfk_1 | test/child | test/parent |      1 |    1 |
+-------------------+------------+-------------+--------+------+
1 row in set (0.06 sec)

mysql> desc information_schema.innodb_sys_foreign_cols;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ID           | varchar(193)     | NO   |     |         |       |
| FOR_COL_NAME | varchar(193)     | NO   |     |         |       |
| REF_COL_NAME | varchar(193)     | NO   |     |         |       |
| POS          | int(11) unsigned | NO   |     | 0       |       |
+--------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_foreign_cols;
+-------------------+--------------+--------------+-----+
| ID                | FOR_COL_NAME | REF_COL_NAME | POS |
+-------------------+--------------+--------------+-----+
| test/child_ibfk_1 | parent_id    | id           |   0 |
+-------------------+--------------+--------------+-----+
1 row in set (0.00 sec)

mysql>

Let me guess: 

The table is UTF8.  64 utf8 characters = up to 64*3 = 192 bytes.

It looks like somebody mistook that varchar lengths in table definitions are not specificied using byte-width but character-width.  And further added "1" for some weird reason!

This table is stored in memory and it is waste of memory as far as I can understand.