Testing: ####### Linux: ----- mysql> show global variables like '%version%'; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | innodb_version | 5.7.34 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 5.7.34-debug-log | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+-----------------------+ 8 rows in set (0.00 sec) 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),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]'; +-------------+--------------+---------------------------------------------------------------------------------------+ | schema_name | table_name | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table | +-------------+--------------+---------------------------------------------------------------------------------------+ | test | #sql2-65bb-2 | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table | | test | testing_user | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table | +-------------+--------------+---------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) Windows: ------- mysql> show global variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.37 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 5.7.37-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+------------------------------+ mysql> 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),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]'; +-------------+--------------+---------------------------------------------------------------------------------------+ | schema_name | table_name | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table | +-------------+--------------+---------------------------------------------------------------------------------------+ | test | testing_user | present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table | +-------------+--------------+---------------------------------------------------------------------------------------+ 1 row in set (0.01 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 | +----------+------------------------+------+--------+-------+-------------+------------+---------------+------------+ | 41 | test/testing_user#p#p0 | 33 | 8 | 24 | Barracuda | Dynamic | 0 | Single | | 42 | test/testing_user#p#p1 | 33 | 8 | 25 | Barracuda | Dynamic | 0 | Single | | 43 | test/testing_user#p#p2 | 33 | 8 | 26 | Barracuda | Dynamic | 0 | Single | | 44 | test/testing_user#p#p3 | 33 | 8 | 27 | Barracuda | Dynamic | 0 | Single | | 45 | test/testing_user#p#p4 | 33 | 8 | 28 | Barracuda | Dynamic | 0 | Single | | 46 | test/testing_user#p#p5 | 33 | 8 | 29 | Barracuda | Dynamic | 0 | Single | | 47 | test/testing_user#p#p6 | 33 | 8 | 30 | Barracuda | Dynamic | 0 | Single | | 48 | test/testing_user#p#p7 | 33 | 8 | 31 | Barracuda | Dynamic | 0 | Single | | 49 | test/testing_user#p#p8 | 33 | 8 | 32 | Barracuda | Dynamic | 0 | Single | | 50 | test/testing_user#p#p9 | 33 | 8 | 33 | Barracuda | Dynamic | 0 | Single | +----------+------------------------+------+--------+-------+-------------+------------+---------------+------------+ 10 rows in set (0.00 sec)