Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.33
-
None
Description
Folks,
The files from a partitioned table were removed from disk but the rows for each of the files are on the INFORMATION_SCHEMA.INNODB_SYS_TABLES. The customer wants to know how to fix that and he has attached the files to the ticket that helped us to verify what is going on.
Please, let us know if you need any other information, cheers!
Attachments
Issue Links
- relates to
-
MDEV-11412 Ensure that table is truly dropped when using DROP TABLE
-
- Closed
-
-
MDEV-11633 Make the InnoDB system tablespace optional
-
- Open
-
I can only think of re-creating the fake files and then dropping the table. Like so (file operations of course should be done in a normal way, here they are done from MySQL client for the sake of simplicity):
Initial table
Remove the table files from the disk
| @@datadir |
| /data/bld/10.0/data/ |
MariaDB [db1]> system rm /data/bld/10.0/data/db1/t1*
MariaDB [db1]> show tables;
| 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 |
| 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 |
| 18 | db1/t1#P#p0 | 1 | 4 | 4 | Antelope | Compact | 0 |
| 19 | db1/t1#P#p1 | 1 | 4 | 5 | Antelope | Compact | 0 |
| 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 |
| 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 |
| 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 |
Create a fake table with the same partitions in a different schema (won't be able to do in the current schema)
Copy table files and then drop the fake table
MariaDB [db2]> system cp /data/bld/10.0/data/db2/t1* /data/bld/10.0/data/db1/
Problematic files are still there
| 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 |
| 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 |
| 18 | db1/t1#P#p0 | 1 | 4 | 4 | Antelope | Compact | 0 |
| 19 | db1/t1#P#p1 | 1 | 4 | 5 | Antelope | Compact | 0 |
| 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 |
| 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 |
| 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 |
Now drop the bad table
| 14 | SYS_DATAFILES | 0 | 5 | 0 | Antelope | Redundant | 0 |
| 11 | SYS_FOREIGN | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 | Antelope | Redundant | 0 |
| 13 | SYS_TABLESPACES | 0 | 6 | 0 | Antelope | Redundant | 0 |
| 17 | mysql/gtid_slave_pos | 1 | 7 | 3 | Antelope | Compact | 0 |
| 16 | mysql/innodb_index_stats | 1 | 11 | 2 | Antelope | Compact | 0 |
| 15 | mysql/innodb_table_stats | 1 | 9 | 1 | Antelope | Compact | 0 |
Of course, it's a hack and comes without any warranty. The right answer is "restore from the backup and never do it again".
marko, do you have a better idea?