Bug #97472 TRUNCATE TABLE in init-file leaves an orphan .ibd
Submitted: 4 Nov 15:34 Modified: 28 Nov 18:06
Reporter: Andrew Garner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Package Repos and Docker Images Severity:S3 (Non-critical)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[4 Nov 15:34] Andrew Garner
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
[28 Nov 18:06] Bogdan Kecman
I cannot reproduce this outside of docker.

Thanks for your report