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:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[4 Nov 2019 15:34] Andrew Garner
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 TABLE IF NOT EXISTS foo.t1(id int primary key);

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
[28 Nov 2019 18:06] MySQL Verification Team
I cannot reproduce this outside of docker.

Thanks for your report
[29 Apr 2020 20:35] Andrew Garner
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?
[1 Mar 2022 10:52] Erlend Dahl
Duplicate of

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

which was fixed in 8.0.20.