umoser,

In the described scenario, InnoDB does not "ignore" tables, it's just a totally wrong way of doing things which has never been supported or recommended in any documentation.

Every InnoDB table has an frm file, ibd file (assuming innodb_log_file_per_table=1 which is default in 10.2), and certain information in the system table space (ibdata), and maybe even in iblogs. By removing a part of it (ibdata and iblogs) but keeping frm and ibd files, you simply leave the database in an inconsistent corrupt state, the server and the engine don't have enough information to do anything with these tables, it's quite natural that they cannot be created, removed, or updated.

Hi Elena,
the interesting thing is that it works for all other InnoDB tables except for those three. If the server has been shutdown normally the Undo logs in ibdata and the Redo logs in the ib-logfiles are no longer needed since all dirty pages have been flushed. So removing the ib-logfiles has always been the normal way to restart the server with a changed ib_logfile configuration. And to free undo space in the ibdata file(s) that was obsolete also worked by removing the ibdata file(s) and restarting the server afterwards. This has also been documented in different blogs and books on MySQL / MariaDB. And as I said, the data directory get correctly recreated for all InnoDB tables but those three. By the way after restart the server shows the tables if you issue a "show tables from mysql;" only that they are not accessible. So thebehoviour must be different for user created InnoDB tables and the three tables in mysql.
So therefore I would like to ask you to have a look into this different behaviour.
Kind regards
Ulrich

umoser, I really wish it worked like you describe.
I have continuously worked on the InnoDB internals ever since I joined Innobase Oy in 2003, and the fragility of the InnoDB system tablespace has always bothered me.
About a year ago, I left Oracle’s MySQL team and joined MariaDB. I would claim that I know the internals of the InnoDB data dictionary, transaction system, and crash recovery fairly well. While we are a long way from implementing MDEV-11633, I believe that at MariaDB it is possible to fix this eventually.

The system tablespace contains the following information:

• Transaction system state
• InnoDB data dictionary (information about all persistent InnoDB tables)
• Doublewrite buffer (only needed in crash recovery)
• Change buffer (buffered changes to secondary index leaf pages, potentially for any persistent InnoDB table)
• Undo log records (unless they are exclusively stored in separate undo* files)
• User tables (if any tables were created without innodb_file_per_table=1)

With the exception of the doublewrite buffer, all of this is covered by write-ahead logging (writing to ib_logfile* first, then to the persistent data files).

The InnoDB redo log files (ib_logfile*) are safe to remove only in the case when the redo log is logically empty, that is, when there are no logged changes since the latest redo log checkpoint. A normal InnoDB shutdown (innodb_fast_shutdown<2) will perform a redo log checkpoint at the very end.

The InnoDB system tablespace or the InnoDB undo files are never safe to remove. I wonder where you got that advice from.

• While a slow shutdown (innodb_fast_shutdown=0) should logically empty all undo logs (provided that no transactions exist in XA PREPARE state), there sometimes are problems with this; see MDEV-11802 and MDEV-13603.
• InnoDB mostly ignores .frm files and relies on its internal data dictionary instead. If you delete the system files, InnoDB will use its dubious ‘open or create’ semantics and create an empty tablespace. It will ignore any .ibd files, because those will not be found in the now-empty InnoDB data dictionary. The .frm files only matter to the SQL parser; ha_innobase::open() will report that the tables do not exist. The only way to bind orphan (but clean) .ibd files to the new empty instance would be ALTER TABLE…IMPORT TABLESPACE.

I believe that we need major changes to make InnoDB be more maintainable, or behave more logically:

• Remove the InnoDB data dictionary tables.
  Store FOREIGN KEY metadata outside of InnoDB, and make .ibd files self-containing
  (identify where each secondary index starts, and also store persistent statistics).
• Move the transaction status data to a crash-safe table that would store all related data, such as GTID and XID.
• Restructure the undo logs; I have been playing with the thought of having tablename.ibu files. It would also allow faster export and import.

Basically, I would eliminate the system tablespace, or only support it for the purpose of reading old tables when upgrading.

Closing again, because there was no feedback from the original reporter, and the following discussion in comments appears to be unrelated (and not a bug).

Thank you Marko for the explanation.