[#MDEV-15185] PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS

Comment by Elena Stepanova [ 2018-02-02 ]

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
MariaDB [test]> use db1;
Database changed
MariaDB [db1]> create table t1 (i int) partition by hash(i) partitions 2;
Query OK, 0 rows affected (0.52 sec)

Remove the table files from the disk
MariaDB [db1]> select @@datadir;
+----------------------+
\| @@datadir \|
+----------------------+
\| /data/bld/10.0/data/ \|
+----------------------+
1 row in set (0.01 sec)

MariaDB [db1]> system rm /data/bld/10.0/data/db1/t1*

MariaDB [db1]> show tables;
Empty set (0.01 sec)

MariaDB [db1]> select * from  INFORMATION_SCHEMA.INNODB_SYS_TABLES;

+----------+--------------------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME                     | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+--------------------------+------+--------+-------+-------------+------------+---------------+

|       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 |

+----------+--------------------------+------+--------+-------+-------------+------------+---------------+

9 rows in set (0.00 sec)

Create a fake table with the same partitions in a different schema (won't be able to do in the current schema)
MariaDB [db1]> use db2;
Database changed

MariaDB [db2]> create table t1 (i int) partition by hash(i) partitions 2;
Query OK, 0 rows affected (0.68 sec)

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/
MariaDB [db2]> drop table t1;
Query OK, 0 rows affected (0.25 sec)

Problematic files are still there
MariaDB [db2]> use db1;
MariaDB [db1]> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES;
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
\| TABLE_ID \| NAME \| FLAG \| N_COLS \| SPACE \| FILE_FORMAT \| ROW_FORMAT \| ZIP_PAGE_SIZE \|
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
\| 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 \|
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
9 rows in set (0.00 sec)

Now drop the bad table
MariaDB [db1]> drop table t1;
Query OK, 0 rows affected (0.25 sec)

MariaDB [db1]> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES;
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
\| TABLE_ID \| NAME \| FLAG \| N_COLS \| SPACE \| FILE_FORMAT \| ROW_FORMAT \| ZIP_PAGE_SIZE \|
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
\| 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 \|
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+
7 rows in set (0.01 sec)

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?

Comment by Marko Mäkelä [ 2018-02-03 ]

elenst, that sounds about right.
For what it is worth, I am reluctant to merge the contribution
~~MDEV-11412~~ AliSQL: Support force drop table
because I think that the correct approach would be to remove the InnoDB system tablespace (MDEV-11633), to make InnoDB data files self-contained, and to make storage engines rely on a crash-safe data dictionary that is maintained at the SQL layer.

Comment by Steven McDowall [ 2018-02-20 ]

@elena – As the customer in question that technique has been tried and all variations. Alas, the description is far from complete and it's far more complex. To be brief, the original database system was Windows and then moved to Linux. This in itself caused interesting issues with file names and case sensitivity.

There is no way we can find that, now that we are on Linux, a way to create the partition name (to drop) that has the lower case #p# that is found in the INFO_SYS tables .. Anything we do creates a new #P# thing . We worked literally 5 hours with a MariaDB tech to try various things to remove these and nothing worked, hence the ticket.

back in the day when the MDEV bug was around that partition names ignore the lower-case- file setting thing (don't remember which MDEV but it's there) .. which also lead to this issue ..

It still seems quite "absurd" that since these ghost partitions are not even needed (they were created over 2 years ago and deleted)..that there is not a simple way to just remove them from the offending "INFO SCHEMA" .. The only issue they are causing are 480+ warning lines upon MariaDB startup complaining they aren't there .. Pretty annoying ..

Are you sure there aren't any little dev tools to hack the INFO SCHEMA information to remove these things?

Comment by Elena Stepanova [ 2018-02-20 ]

"INFO SCHEMA" does not hold any information of its own, it only provides read access to metadata in the database. The problem is not in the information schema, it's in the discrepancy between the contents of the system tablespace and the rest of the data; and to get rid of it, you need to hack not the seemingly unimportant information schema, but the highly sensitive InnoDB system tablespace. I'm not aware of any reasonably safe ways to do so; I suppose if there were any, marko would have suggested them in his previous comment. We can ask him again. Probably the above-mentioned ~~MDEV-11412~~ patch would have done that, but apparently it hasn't been merged into MariaDB, and even if it will ever be, it won't be 10.0.

Comment by Sergei Golubchik [ 2018-03-29 ]

MDEV-11633 will provide a way to "hack the INFO SCHEMA", or, more correctly, to get rid of these stale entries in the InnoDB data dictionary.

~~MDEV-11412~~ will get rid of the data dictionary, so this situation will not be possible in the first place.

So, I'm closing this issue. I'll be continued in one of the issues above.

[MDEV-15185] PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS_TABLES Created: 2018-02-02 Updated: 2020-08-25 Resolved: 2018-03-29
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.0.33
Fix Version/s:	N/A

[MDEV-15185] PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS_TABLES Created: 2018-02-02 Updated: 2020-08-25 Resolved: 2018-03-29