Bug #106372 mysqlsh upgrade pre checker shows empty table name for Orphaned tables
Submitted: 3 Feb 2022 14:33 Modified: 8 Aug 2022 9:35
Reporter: Pranay Motupalli Email Updates:
Status: Closed Impact on me:
None 
Category:Shell Upgrade Checker Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql-shell

[3 Feb 2022 14:33] Pranay Motupalli
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 "
[3 Feb 2022 14:33] Pranay Motupalli
Test details

Attachment: testing_bug_report.txt (text/plain), 7.02 KiB.

[3 Feb 2022 14:34] Pranay Motupalli
Updated the version column in the bug report
[4 Feb 2022 4:35] MySQL Verification Team
Thank you for report
All best
[8 Aug 2022 9:35] Edward Gilmore
updated Shell 8.0.30 changelog with the following:
The upgrade checker utility returned an error for orphaned
tables. As of this release, the utility parses the table names
in the innodb_sys_tables table for orphaned tables.