MySQL Bugs: #97472: TRUNCATE TABLE in init-file leaves an orphan .ibd

Bug #97472	TRUNCATE TABLE in init-file leaves an orphan .ibd
Submitted:	4 Nov 2019 15:34	Modified:	1 Mar 2022 10:52
Reporter:	Andrew Garner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When providing an init-file that initializes and truncates a table, it was observed that orphan .ibd tables were created as a result.

For each restart of mysql with this init-file a new #sql-ib*.ibd file is seemingly created.

How to repeat:
Given this init-file:

# cat init-file.sql
CREATE SCHEMA IF NOT EXISTS foo;
CREATE TABLE IF NOT EXISTS foo.t1(id int primary key);
TRUNCATE TABLE foo.t1;

When I run a MySQL 8.0.18 instance that executes this via the --init-file option

# docker run --name init-file-truncate --rm -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -v $PWD/init-file.sql:/etc/mysql/init-file.sql -d mysql:8.0.18 --init-file=/etc/mysql/init-file.sql --log-error-verbosity=3

Then I can see there are additional *.ibd files in the ${datadir}/foo/ directory:

# ls -l /var/lib/mysql/foo/
total 320
-rw-r----- 1 mysql mysql 114688 Nov  4 15:11 #sql-ib1059-3848024680.ibd
-rw-r----- 1 mysql mysql 114688 Nov  4 15:11 #sql-ib1060-1127983405.ibd
-rw-r----- 1 mysql mysql 114688 Nov  4 15:12 #sql-ib1061-3839697642.ibd
-rw-r----- 1 mysql mysql 114688 Nov  4 15:12 t1.ibd

I cannot reproduce this outside of docker.

Thanks for your report

This seems like this was bug  #30721214, perhaps.

> InnoDB: DDL logging was not performed for SQL operations executed by the bootstrap thread using the init_file startup variable, causing files to be left behind that should have been removed during a post-DDL stage. (Bug #30721214)

Not repeatable on 8.0.20.   Thanks?

Duplicate of

Bug#98131 Files left after DROP TABLE since InnoDB skips post_ddl for bootstrapping THDs

which was fixed in 8.0.20.