Description:
Data dictionary does not have any command/parameter/procedure for maintenance except for upgrade=FORCE
This has consequences:
It's notoriously difficult to migrate from 5.7 to 8.0 with some table comments (https://bugs.mysql.com/bug.php?id=110177)
DD "corruptions" cannot be resolved manually by playing with frm/sdi files like on 5.7
Playing with copied table and ALTERs does not work to properly fix the DD
Dump/restore can work with some cases, but we would need a better solution for huge dataset
How to repeat:
mysql> create database test;
mysql> create table test.test_table(t int);
mysql> SET session debug='+d,skip_dd_table_access_check';
mysql> delete from mysql.tables where name='test_table';
Query OK, 1 row affected (0,05 sec)
mysql> drop database test;
// mysql restart, without issues
// dangling file-per-table tablespace that can't be used anymore, and can't be deleted
mysql> select * from information_schema.innodb_tablespaces where name like '%test_table%';
+-------+-------------------------------------------------------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+-------+-------------------------------------------------------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 2 | test/test_table | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
+-------+-------------------------------------------------------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
mysql> select * from information_schema.innodb_tables where name like '%test_table%';
Empty set (0,02 sec)
// Now: impossible to fix without manually editing the DD again on multiple tables
// this procedure repeats a real production issue (not the rootcause, but this generates the exact same result of the a missing row in mysql.tables)
Suggested fix:
Commands:
- sort of "CHECK DATA DICTIONARY CONSISTENCY" command to assess if DD is healthy
- "DISCARD (TABLESPACE, TABLE, TABLESPACE_FILE, ...) FROM DATA DICTIONARY for id=x" to safely remove any orphan object, along with its dependencies, and maybe dump their sdi files beforehand.
(I would not see a usecase for an IMPORT command, recreating objects work)
Startup options:
- "--dump-data-dictionary": would create the .sdi file for every object, enabling us to edit it (at our own risk obviously, but it could help edit DD more easily especially if it's not able to start. Would also enable to take physical backups of SDIs before any unsafe action). See ibd2sdi tool
- "--import-data-dictionary": would re-generate the data dictionary from sdi files, but would fail if any anything would be missing, unreadable or inconsistent.
