MySQL Bugs: #100318: present in INFORMATION_SCHEMA's INNODB_SYS

Bug #100318	present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES
Submitted:	24 Jul 2020 13:48	Modified:	3 Aug 2020 13:33
Reporter:	Shubhangi Dalvi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S3 (Non-critical)
Version:	Mysql 5.7.23	OS:	CentOS (6)
Assigned to:		CPU Architecture:	Other (64 bit)
Tags:	upgrade checker showing missing tables which is not exists

Description:
We are planning to upgrade mysql from 5.7.23 to 8.0.20. We have executed mysqlshell upgrader checker which are showing below 

CONSUMER_COURT.case_sub_sub_category_master - present in INFORMATION_SCHEMA's
    INNODB_SYS_TABLES table but missing from TABLES table

Now this schema is actually not present in mysql, even no related file exists in /var/lib/mysql data folder.  

We have fired select on INFORMATION_SCHEMA's INNODB_SYS_TABLES and it is showing all tables of this schema.  

Please suggest method to remove this table/schema information from metadata INFORMATION_SCHEMA's INNODB_SYS_TABLES so can process further for upgrade. 

How to repeat:
NA

Hi Mr. Dalvi,

Thank you for your bug report.

However, there are some problems with you bug report.

First of all, 5.7.23 is an old release. Please, try first to upgrade from 5.7.23 to 5.7.31. After that, follow the procedure for upgrading. Use the latest MySQL 8.0 and latest MySQL Shell Checker.

After that, if you still have these problems, run again Shell Upgrade Checker. If you have problems, we will need you to send us the output from the Checker, together with a dump of the couple of the smallest InnoDB tables in a schema that makes you a problem.

Thanks in advance.

Thanks for update.

We had used latest mysql shell check 8.0.20  only.
As per your suggestion we will try first upgrade from 5.7.23 to 5.7.31 and update.

Hi Mr. Dalvi,

Please, also follow the entire upgrade procedure as described in our Reference Manual.

This happens if a database or table was removed from the filesystem without using DROP TABLE.  This test uses MySQL 5.7.31:

mysql> create database willdropwithrm;
Query OK, 1 row affected (0.00 sec)

mysql> use willdropwithrm;
Database changed

mysql> create table t1(c1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

$ rm -rf ~/mysql_data57/t1

-- restart MySQL: note there are warnings at startup
2020-08-02T06:10:39.221693Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-08-02T06:10:39.221729Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-08-02T06:10:39.221735Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2020-08-02T06:10:39.221740Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './willdropwithrm/t1.ibd' OS error: 71
2020-08-02T06:10:39.221744Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-08-02T06:10:39.221747Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.

-- note the table is present in INNODB_SYS_TABLES
mysql> select * from information_schema.innodb_sys_tables where name like '%orphaned';
+----------+-------------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                          | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-------------------------------+------+--------+-------+-------------+------------+---------------+------------+
|       40 | willdropwithrm/t1    |   33 |      4 |    23 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-------------------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)

-- but the table is missing from information_schema.tables
mysql> select * from information_schema.tables where table_name = 't1';
Empty set (0.00 sec)

Please see the manual here:
https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

As per that page:
create database test;
use test;
create table t1(c1 int) engine=innodb;
create database willbeorphaned;
shutdown;
copy test/t1.frm into the willbeorphaned/ directory  
restart MySQL
use willbeorphaned;
drop t1;

note, the schema for the table need to match the original schema.  You can create a single column table and use the .frm for it.  You will have to follow this procedure for every orphaned table.

that rm command should be rm -rf .../willbeorphaned

sorry about that

Hi Justin,

Thank you very much for your help, which is truly appreciated.

We have re-read the link to our manual page, that you quoted and we find that it is descriptive enough.

If you find anything missing, please let us know.

Thanks again.