MySQL clean orphan tables innoDB

Question

I have a MySQL server with a lot of orphan tables. According to the manual I need to:

In the database directory, rename the #sql-*.frm file to match the base name of the orphan intermediate table

the files that I have (some of them) are like:

#sql-15655_a541c.frm
#sql-15655_a541e.frm
#sql-15655_a543a.frm
#sql-15655_a543c.frm
#sql-15655_a543d.frm
#sql-15655_a543e.frm
#sql-15655_a5440.frm
#sql-15655_a5442.frm
#sql-15655_a5443.frm
......

And the ibd files are (well...some of them!):

#sql-ib2015-2421921804.ibd
#sql-ib2016-2421921806.ibd
#sql-ib2017-2421921808.ibd
#sql-ib2020-2421921814.ibd
#sql-ib2021-2421921816.ibd

So, my intention is to do the following, from the terminal:

mv \#sql-15655_a541c.frm \#sql-ib2015-2421921804.frm

I just picked up the first frm and moved it to the first ibd file by keeping the ibd name with the frm extension. So, I will do the same for the second, third etc files. Then I will drop the tables with the #mysql50# prefix.

Does the order matter? What if I rename the first 'frm' file with the filename of the fifth ibd file? Will that lead to a broken/corrupted DB? How do you know how to associate the frm with the ibd files? Do I need to stop the mysql server?

Thank you for time!

Answer 1

https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html says:

The .frm file must have the same table schema as the orphan intermediate table (it must have the same columns and indexes) and must be placed in the database directory of the orphan intermediate table.

If the .frm file must have the same columns and indexes, then yes, you must match the right .frm file with the right .ibd file. I don't know how these temporary filenames are generated. There doesn't seem to be any clear correlation, so it's practically impossible to know which one goes with the other.

You can dump the original table structure from a .frm file using the mysqlfrm tool in MySQL Utilities. There's a nice blog showing how to do this here: https://www.percona.com/blog/2014/01/02/recover-table-structure-frm-files-mysql-utilities/

You should download the MySQL Utilities soon, because Oracle seems to have deprecated these tools. Some of the features of these tools is reimplemented as part of MySQL Shell, but I expect any features for .frm files will not be supported, because MySQL 8.0 doesn't use .frm files anymore.

I just tried using mysqlfrm on my Macbook, but I get this error:

ERROR: Cannot find location of mysql_system_tables.sql.

I guess that's because MySQL is installed under /usr/local on my Macbook. Maybe it will be more successful on a Linux server, but I don't have a Linux installation handy that has MySQL Utilities installed.

And that's only half of the story. You would still need to know the table structure of each .ibd file to match it to the right .frm file. You might be able to piece the information together from these INFORMATION_SCHEMA tables:

• INNODB_SYS_TABLES
• INNODB_SYS_COLUMNS
• INNODB_SYS_INDEXES

For example, here's a simple table in my test schema:

CREATE TABLE `A` (
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

If I query the I_S tables:

mysql> select * from innodb_sys_tables where name = 'test/A';
+----------+--------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME   | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+--------+------+--------+-------+-------------+------------+---------------+
|     2126 | test/a |    1 |      6 |  2106 | Antelope    | Compact    |             0 |
+----------+--------+------+--------+-------+-------------+------------+---------------+

mysql> select * from innodb_sys_columns where table_id = 2126;
+----------+------+-----+-------+--------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+------+-----+-------+--------+-----+
|     2126 | c1   |   0 |     6 |   1283 |   4 |
|     2126 | c2   |   1 |     6 |   1027 |   4 |
|     2126 | c3   |   2 |     6 |   1027 |   4 |
+----------+------+-----+-------+--------+-----+

mysql> select * from innodb_sys_indexes where table_id = 2126;
+----------+---------+----------+------+----------+---------+-------+
| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+---------+----------+------+----------+---------+-------+
|     3875 | PRIMARY |     2126 |    3 |        1 |       3 |  2106 |
|     3876 | c2      |     2126 |    0 |        1 |       4 |  2106 |
+----------+---------+----------+------+----------+---------+-------+