Bug #93033 Missing info on partitioned tables in I_S.INNODB_COLUMNS after upgrade to 8.0
Submitted: 31 Oct 16:45 Modified: 1 Nov 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 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 5:10] Umesh Shastry
Hello Alexey,

Thank you for the report and test case.

regards,
Umesh