Cannot drop orphaned #sql-ib file

MySQL & MariaDB Percona Server for MySQL 8.0
1

We are operating percona-xtradb-cluster version 8.0.36, on three nodes, on Ubuntu 22.04 LTS.

due to a small problem with the cluster, as a result of disrupted ALTER operation we are facing problem with a file that was created in /var/log/mysql/System-Main called #sql-ib541-4089763484.ibd with a size of 22GB, which we wanted to delete (to regain space).

The file exists on disk BUT:

  • It is not listed in information schema tables (INFORMATION_SCHEMA.INNODB_TABLES has no records regarding this file)
  • It is listed in INFORMATION_SCHEMA.INNODB_TABLESPACES as ‘System-Main/#sql-ib541-4089763484’, SPACE=16417, FLAG=16417
  • It is listed in INFORMATION_SCHEMA.FILES table as Filename: ./System@002dMain/#sql-ib541-4089763484.ibd, File_type: TABLESPACE, Tablespace_name: System-Main/#sql-ib541-4089763484

We are not able to:
Drop table
Alter table
Drop tablespace
discard or import tablespace.

All operation state: “Tablespace #sql-ib541-4089763484 doesn’t exist”
We tried adding #mysql50# and the full name of the database to the name - the same result.

Creating table with the same name, importing tablespace or renaming other table to this name throws exception:
Error Code: 1062. Duplicate entry ‘System-Main/#sql-ib541-4089763484’ for key ‘tablespaces.name’

Mysqlcheck does not detect any problems.

Can anybody advice how get rid of this table keeping information schema consistent? Can we safely delete this file?
If no - can we anyhow reduce its size? We thought about creating empty table having the same fields structure and change the file on the disk to smaller one but we are curious if it wont generate any problems as INFORMATION_SCHEMA.INNODB_TABLES keeps the current size of the file.

Backup and restore are unfortunately out of the question due to the size of the database and a possible interruption in the operation of one of our 24/7 systems…

2

Hello @Cezary

as a result of disrupted ALTER operation we are facing problem

How did you execute ALTER? It is important as it’s a PXC cluster. Check the table definition on all 3 PXC nodes and make sure it’s the same.

Are you seeing this issue only on a single PXC node or on all nodes? If you are facing this issue on a single node and other nodes are in sync, you can perform an SST restore from other healthy nodes; this way, you don’t need to take down time.

Did you upgrade the PXC version recently?
Check what is the timestamp for the #sql-ib541-4089763484.ibd file on the filesystem.

The Idea behind checking the above things is, this issue should NOT happen with 8.0 version as it uses Atomic-DDL

If it’s impacting your business or application, the better option is to restore it from backup to quickly recover it.

3

Checking further I got this in MySQL documentation,

Intermediate table files:

Some online DDL operations that rebuild the table create a temporary intermediate table file in the same directory as the original table. An intermediate table file may require space equal to the size of the original table. Intermediate table file names begin with #sql-ib prefix and only appear briefly during the online DDL operation.

You can, Try restating the problmatic pxc node to see if cleanup the temporary intermediate table file.

4

Unfortunately it was regular SQL ALTER command on a big table.

The file exists on all nodes. SQL schema on all nodes is consistent. Mysqlcheck does not see any problems.

Nodes have been upgraded from 5.7 to 8 last month. It was done after this ALTER and when file already existed.
On 5.7 we were facing the same situation with the same file.

Table timestamp differs on nodes as we made SST on 1 or 2 of them.

File dates on nodes

Node1: -rw-r----- 1 mysql mysql 23391633408 Mar 14 17:08 ‘#sql-ib541-4089763484.ibd’
Node2: -rw-r----- 1 mysql mysql 23391633408 Mar 14 16:50 ‘#sql-ib541-4089763484.ibd’
Node3: -rw-r----- 1 mysql mysql 23391633408 Mar 15 16:07 ‘#sql-ib541-4089763484.ibd’

It was on 5.7 but we would like to get rid of this file now.

#1 This table is backed-up by percona – exists in all performed backups, so probably it will recover as there is information regarding this table in tablespace

#2 recovery would be painful for us, as backup process takes about 2h, so recovery would stop production for similar – if not longer – time. This is why we use Percona cluster not single server with replication.

This is why we are looking for solution allowing us to free space used by this table without stopping the production, but all methods described are not working.

We believe there must be any tool or method to fix such a obvious problem.

5

Any thoughts how to solve the issue?

6

Are you able to extract the SDI details? https://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html

7

Yes,

`# ibd2sdi \#sql-ib541-4089763484.ibd
["ibd2sdi"
,
{
        "type": 2,
        "id": 213,
        "object":
                {
    "mysqld_version_id": 80035,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "System-Main/#sql-ib541-4089763484",
        "comment": "",
        "options": "encryption=N;",
        "se_private_data": "flags=16417;id=946;server_version=80035;space_version=1;state=normal;",
        "engine": "InnoDB",
        "engine_attribute": "",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./System@002dMain/#sql-ib541-4089763484.ibd",
                "se_private_data": ""
            }
        ]
    }
}
}
]