| 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 Aug 2019 16:22 |
| Reporter: | Alexey Kopytov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Nov 2018 5:10]
MySQL Verification Team
Hello Alexey, Thank you for the report and test case. regards, Umesh
[7 May 2019 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 2019 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.
[1 Aug 2019 16:22]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.18 release, and here's the changelog entry: The INFORMATION_SCHEMA.INNODB_COLUMNS table did not display partitioned table columns after upgrading from MySQL 5.7 to MySQL 8.0. For partitioned tables created on the MySQL 8.0 release, INFORMATION_SCHEMA.INNODB_COLUMNS only displayed columns for the first table partition.
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.