MySQL Bugs: #98131: Files left after DROP TABLE since InnoDB skips post

Bug #98131	Files left after DROP TABLE since InnoDB skips post_ddl for bootstrapping THDs
Submitted:	6 Jan 2020 8:51	Modified:	30 Jan 2020 22:03
Reporter:	Sivert Sørumgård	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Executing DROP TABLE of an InnoDB table having an implicit tablespace during server initialization, restart, upgrade or init-file execution will leave the .ibd file behind even though the DROP TABLE will be committed.

In a similar situation, DROP SCHEMA of a schema containing InnoDB tables with implicit tablespaces will fail since the directory is not empty after all tables have been removed.

How to repeat:
Create an SQL script file like:

CREATE SCHEMA s;
CREATE TABLE s.t(i int);
DROP SCHEMA s;
EOF

Restart the mysqld server with the --init-file option to execute the script above during restart. The error log will contain an error message stating that the schema could not be dropped since the directory is not empty. In the directory, the t.ibd file will be present.

The script can be modified to just drop the table. Then, there is no error message in the error log, but you will still see that the .ibd file is present after the server has been restarted.

Suggested fix:
This seems to be caused by the implementation of LogDDL::skip(), which will skip the post_ddl() action for bootstrapping THDs.

Posted by developer:
 
Fixed as of the upcoming 8.0.20 release, and here's the proposed changelog entry from the documentation team:

DDL logging was not performed for SQL operations executed by the boostrap
thread using the init_file startup variable, causing files to be left
behind that should have been removed during a post-DDL stage.

Bug#97472 TRUNCATE TABLE in init-file leaves an orphan .ibd

was marked as a duplicate.