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: Valerii 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:
Tags: information_schema, innodb_sys_foreign, innodb_sys_foreign_cols

[12 Jan 2014 17:22] Valerii Kravchuk
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.
[13 Jan 2014 5:04] Umesh Shastry
Hello Valeriy,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh
[13 Jan 2014 5:06] Umesh Shastry
// 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>
[13 Jan 2014 12:31] Peter Laursen
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.