Elena Stepanova added a comment - 2018-02-02 21:29 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?

Marko Mäkelä added a comment - 2018-02-03 13:46 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.

Steven McDowall added a comment - 2018-02-20 21:00 @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?

Elena Stepanova added a comment - 2018-02-20 21:44 "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.

Sergei Golubchik added a comment - 2018-03-29 19:37 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.