Bug #93033 Missing info on partitioned tables in I_S.INNODB_COLUMNS after upgrade to 8.0
Submitted: 31 Oct 2018 16:45 Modified: 1 Nov 2018 5:10
Reporter: Alexey Kopytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[31 Oct 2018 16:45] Alexey Kopytov
Description:
1. Execute the following statements on MySQL 5.7 (tested with 5.7.21):

mysql> CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;
Query OK, 0 rows affected (0.05 sec)

2. Observe that columns for both regular and partitioned tables are present in INFORMATION_SCHEMA.INNODB_SYS_COLUMNS:

mysql> SELECT t.NAME, c.NAME
    ->   FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
    ->   JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
    ->   WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/t1';
+---------+------+
| NAME    | NAME |
+---------+------+
| test/t1 | a    |
| test/t1 | b    |
+---------+------+
2 rows in set (0.03 sec)

mysql> SELECT t.NAME, c.NAME
    ->   FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
    ->   JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
    ->   WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'
    ->   ORDER BY t.NAME, c.NAME;
+--------------+------+
| NAME         | NAME |
+--------------+------+
| test/tp#P#p0 | a    |
| test/tp#P#p0 | b    |
| test/tp#P#p1 | a    |
| test/tp#P#p1 | b    |
| test/tp#P#p2 | a    |
| test/tp#P#p2 | b    |
+--------------+------+
6 rows in set (0.01 sec)

3. Do an in-place upgrade to MySQL 8.0 (tested with 8.0.13);

4. Observe that I_S.INNODB_COLUMNS (the equivalent of INNODB_SYS_COLUMNS in 8.0) contains rows for the regular table, but not for the partitioned table:

mysql> SELECT t.NAME, c.NAME
    ->   FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
    ->   JOIN INFORMATION_SCHEMA.INNODB_TABLES t
    ->   WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/t1';
+---------+------+
| NAME    | NAME |
+---------+------+
| test/t1 | a    |
| test/t1 | b    |
+---------+------+
2 rows in set (0.10 sec)

mysql> SELECT t.NAME, c.NAME
    ->   FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
    ->   JOIN INFORMATION_SCHEMA.INNODB_TABLES t
    ->   WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'
    ->   ORDER BY t.NAME, c.NAME;
Empty set (0.10 sec)

5. Observe that recreating the partitioned table in 8.0 restores column information in I_S.INNODB_COLUMNS. Albeit only for the first partition, which might be a separate issue:

mysql> DROP TABLE tp;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;
Query OK, 0 rows affected (0.13 sec)

mysql> SELECT t.NAME, c.NAME
    ->   FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
    ->   JOIN INFORMATION_SCHEMA.INNODB_TABLES t
    ->   WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'
    ->   ORDER BY t.NAME, c.NAME;
+--------------+------+
| NAME         | NAME |
+--------------+------+
| test/tp#P#p0 | a    |
| test/tp#P#p0 | b    |
+--------------+------+
2 rows in set (0.11 sec)

How to repeat:
See above.
[1 Nov 2018 5:10] Umesh Shastry
Hello Alexey,

Thank you for the report and test case.

regards,
Umesh
[7 May 9:58] Gopal Shankar
Posted by developer:
 
It seems InnoDB is not updating INFORMATION_SCHEMA.INNODB_COLUMNS.TABLE_ID
except for first partition.

A) On 5.7 we see:
`````````````````
1.
CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;

2.
SELECT c.TABLE_ID, t.TABLE_ID, t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c ON c.TABLE_ID = t.TABLE_ID
WHERE  t.NAME LIKE 'test/tp%'
    ORDER BY t.NAME;
TABLE_ID	TABLE_ID	NAME	NAME
37	37	test/tp#P#p0	a
37	37	test/tp#P#p0	b
38	38	test/tp#P#p1	a
38	38	test/tp#P#p1	b
39	39	test/tp#P#p2	a
39	39	test/tp#P#p2	b

3.
SELECT t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'
  ORDER BY t.NAME, c.NAME;
NAME	NAME
test/tp#P#p0	a
test/tp#P#p0	b
test/tp#P#p1	a
test/tp#P#p1	b
test/tp#P#p2	a
test/tp#P#p2	b

B) On 8.0 we see:
`````````````````
1.
CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;

2.
SELECT c.TABLE_ID, t.TABLE_ID, t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_TABLES t
LEFT JOIN INFORMATION_SCHEMA.INNODB_COLUMNS c ON c.TABLE_ID = t.TABLE_ID
WHERE  t.NAME LIKE 'test/tp%'
  ORDER BY t.NAME;
TABLE_ID	TABLE_ID	NAME	NAME
1063	1063	test/tp#P#p0	a
1063	1063	test/tp#P#p0	b
NULL	1064	test/tp#P#p1	NULL
NULL	1065	test/tp#P#p2	NULL

3.
SELECT t.NAME, c.NAME                                                           
FROM INFORMATION_SCHEMA.INNODB_COLUMNS c                                  
JOIN INFORMATION_SCHEMA.INNODB_TABLES t                                   
WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'                      
ORDER BY t.NAME, c.NAME;
NAME	NAME
test/tp#P#p0	a
test/tp#P#p0	b

Observation:
````````````
We see that in 5.7, the command A.2 returns all valid table ID's for
column c.TABLE_ID.
whereas, in 8.0, the command B.2 returns table ID for just the first
partition p0. The I_S.INNODB_COLUMNS.TABLE_ID for partition p1 and p2
is NULL. So obviously, the command B.3 will not list row for p1 and p2.
And this seems to be root cause.

InnoDB should fix following function (I guess):
i_s.cc:6131 i_s_dict_fill_innodb_columns(thd, table_id, col_name, &column_rec,

NOTE: This bug is not related to upgrade as described when reporting the bug.
[7 May 10:00] Gopal Shankar
Posted by developer:
 
It seems InnoDB is not updating INFORMATION_SCHEMA.INNODB_COLUMNS.TABLE_ID
except for first partition.

A) On 5.7 we see:
`````````````````
1.
CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;

2.
SELECT c.TABLE_ID, t.TABLE_ID, t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c ON c.TABLE_ID = t.TABLE_ID
WHERE  t.NAME LIKE 'test/tp%'
    ORDER BY t.NAME;
TABLE_ID	TABLE_ID	NAME	NAME
37	37	test/tp#P#p0	a
37	37	test/tp#P#p0	b
38	38	test/tp#P#p1	a
38	38	test/tp#P#p1	b
39	39	test/tp#P#p2	a
39	39	test/tp#P#p2	b

3.
SELECT t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'
  ORDER BY t.NAME, c.NAME;
NAME	NAME
test/tp#P#p0	a
test/tp#P#p0	b
test/tp#P#p1	a
test/tp#P#p1	b
test/tp#P#p2	a
test/tp#P#p2	b

B) On 8.0 we see:
`````````````````
1.
CREATE TABLE tp (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;

2.
SELECT c.TABLE_ID, t.TABLE_ID, t.NAME, c.NAME
FROM INFORMATION_SCHEMA.INNODB_TABLES t
LEFT JOIN INFORMATION_SCHEMA.INNODB_COLUMNS c ON c.TABLE_ID = t.TABLE_ID
WHERE  t.NAME LIKE 'test/tp%'
  ORDER BY t.NAME;
TABLE_ID	TABLE_ID	NAME	NAME
1063	1063	test/tp#P#p0	a
1063	1063	test/tp#P#p0	b
NULL	1064	test/tp#P#p1	NULL
NULL	1065	test/tp#P#p2	NULL

3.
SELECT t.NAME, c.NAME                                                           
FROM INFORMATION_SCHEMA.INNODB_COLUMNS c                                  
JOIN INFORMATION_SCHEMA.INNODB_TABLES t                                   
WHERE c.TABLE_ID = t.TABLE_ID AND t.NAME LIKE 'test/tp%'                      
ORDER BY t.NAME, c.NAME;
NAME	NAME
test/tp#P#p0	a
test/tp#P#p0	b

Observation:
````````````
We see that in 5.7, the command A.2 returns all valid table ID's for
column c.TABLE_ID.
whereas, in 8.0, the command B.2 returns table ID for just the first
partition p0. The I_S.INNODB_COLUMNS.TABLE_ID for partition p1 and p2
is NULL. So obviously, the command B.3 will not list row for p1 and p2.
And this seems to be root cause.

InnoDB should fix following function (I guess):
i_s.cc:6131 i_s_dict_fill_innodb_columns(thd, table_id, col_name, &column_rec,

NOTE: This bug is not related to upgrade as described when reporting the bug.