Description:
MySQL Shell schemaInconsistencyCheck shows empty table name in the output for an Orphaned table due to the following commit.
Regression due to commit: https://github.com/mysql/mysql-shell/commit/9bb0a1b1b6fb49a0de7a33d63a24a258b1c5322c which is made to fix issues with windows and partitioned tables.
How to repeat:
## Consider there are following:
1. A partitioned table without an entry in MySQL Data Dictionary but there are entries in InnoDB Data Dictionary.
2. An Orphan Temporary table present in InnoDB Data Dictionary resulted due to a crash during execution of DDL.
3. Any MySQL 5.7 version (preferable latest)
## Current state of the Engine
mysql> select * from information_schema.tables where table_name like '%#%';
Empty set (0.75 sec)
mysql> select * from information_schema.tables where table_name like '%testing%';
Empty set (0.75 sec)
mysql> select * from information_schema.innodb_sys_tables where name like '%#%';
+----------+------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+------------------------+------+--------+-------+-------------+------------+---------------+------------+
| 13238 | test/#sql2-65bb-2 | 33 | 5 | 13237 | Barracuda | Dynamic | 0 | Single | <<<<<<<<<---------- Orphaned table
| 13240 | test/testing_user#P#p0 | 33 | 8 | 13239 | Barracuda | Dynamic | 0 | Single |
| 13241 | test/testing_user#P#p1 | 33 | 8 | 13240 | Barracuda | Dynamic | 0 | Single |
| 13242 | test/testing_user#P#p2 | 33 | 8 | 13241 | Barracuda | Dynamic | 0 | Single |
| 13243 | test/testing_user#P#p3 | 33 | 8 | 13242 | Barracuda | Dynamic | 0 | Single |
| 13244 | test/testing_user#P#p4 | 33 | 8 | 13243 | Barracuda | Dynamic | 0 | Single |
| 13245 | test/testing_user#P#p5 | 33 | 8 | 13244 | Barracuda | Dynamic | 0 | Single |
| 13246 | test/testing_user#P#p6 | 33 | 8 | 13245 | Barracuda | Dynamic | 0 | Single |
| 13247 | test/testing_user#P#p7 | 33 | 8 | 13246 | Barracuda | Dynamic | 0 | Single |
| 13248 | test/testing_user#P#p8 | 33 | 8 | 13247 | Barracuda | Dynamic | 0 | Single |
| 13249 | test/testing_user#P#p9 | 33 | 8 | 13248 | Barracuda | Dynamic | 0 | Single |
+----------+------------------------+------+--------+-------+-------------+------------+---------------+------------+
11 rows in set (0.00 sec)
root@ip-172-31-50-102:~/mysql-shell-8.0.28-linux-glibc2.12-x86-64bit/bin# ./mysqlsh --version
./mysqlsh Ver 8.0.28 for Linux on x86_64 - for MySQL 8.0.28 (MySQL Community Server (GPL))
Output:
######
{
"id": "schemaInconsistencyCheck",
"title": "Schema inconsistencies resulting from file removal or corruption",
"status": "OK",
"description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
"detectedProblems": [
{
"level": "Error",
"dbObject": "test", ----------------------->>>>>>>>>>>>> **ONLY DATABASE NAME IS SHOWN AND EMPTY TABLE NAME**
"description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
},
{
"level": "Error",
"dbObject": "test.testing_user",
"description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
}
]
}
Picked up query from the below code and ran it manually to cross check the same:
https://github.com/mysql/mysql-shell/blob/29bafc5692bd536a12c4e41c54cb587375fe52cf/modules...
mysql> select A.schema_name, A.table_name, 'present in INFORMATION_SCHEMA''s INNODB_SYS_TABLES table but missing from TABLES table'
-> from
-> (select distinct
-> replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(NAME, '/',1), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as schema_name,
-> replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(substring_index(NAME, '/',-1),'#',1), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as table_name
-> from
-> information_schema.innodb_sys_tables where NAME like '%/%') A
-> left join information_schema.tables I on A.table_name = I.table_name
-> and
-> A.schema_name = I.table_schema
-> where A.table_name not like 'FTS_0%'
-> and (I.table_name IS NULL or I.table_schema IS NULL)
-> and A.table_name
-> not REGEXP '@[0-9]'
-> and A.schema_name not REGEXP '@[0-9]';
+-------------+--------------+---------------------------------------------------------------------------------------+
| schema_name | table_name | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table |
+-------------+--------------+---------------------------------------------------------------------------------------+
| test | | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table |. <<<<<----- Empty table name.
| test | testing_user | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table |
+-------------+--------------+---------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)
Suggested fix:
Note: The below query is tested with partitions on Windows as well. Test details are attached
Modified query:
###############
select A.schema_name, A.table_name, 'present in INFORMATION_SCHEMA''s INNODB_SYS_TABLES table but missing from TABLES table'
from
(select distinct
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(NAME, '/',1), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as schema_name,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(substring_index(NAME, '/',-1),if(locate(BINARY "#P#", substring_index(NAME, "/",-1)), "#P#", "#p#"),1), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as table_name
from
information_schema.innodb_sys_tables where NAME like '%/%') A
left join information_schema.tables I on A.table_name = I.table_name
and
A.schema_name = I.table_schema
where A.table_name not like 'FTS_0%'
and (I.table_name IS NULL or I.table_schema IS NULL)
and A.table_name
not REGEXP '@[0-9]'
and A.schema_name not REGEXP '@[0-9]';
Change in code:
--------------
[xxxxxx@xxxxxx mysql-shell]$ git diff master
diff --git a/modules/util/upgrade_check.cc b/modules/util/upgrade_check.cc
index d6faa454a..02749cd62 100644
--- a/modules/util/upgrade_check.cc
+++ b/modules/util/upgrade_check.cc
@@ -1272,7 +1272,7 @@ Sql_upgrade_check::get_schema_inconsistency_check() {
"distinct "
replace_in_SQL("substring_index(NAME, '/',1)")
" as schema_name, "
- replace_in_SQL("substring_index(substring_index(NAME, '/',-1),'#',1)")
+ replace_in_SQL("substring_index(substring_index(NAME, '/',-1),if(locate(BINARY "#P#", substring_index(NAME, "/",-1)), "#P#", "#p#"),1)")
" as table_name from "
"information_schema.innodb_sys_tables where NAME like '%/%') A left "
"join information_schema.tables I on A.table_name = I.table_name and "