Unable to get rid of orphan temporary tables After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a.

From there I started googling and landed on the innodb troubleshooting docs

Indeed, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; gives the following result:

MariaDB [(none)]&gt; SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
+----------+--------+----------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME           | FLAG | N_COLS | SPACE |
+----------+--------+----------------+------+--------+-------+
|      323 | piwik  | #sql-1526_3a   |    1 |     11 |   320 |
|      265 | piwik  | #sql-5305_92b0 |    1 |     24 |   262 |
|      321 | piwik  | #sql-7920_48   |    1 |     24 |   318 |
+----------+--------+----------------+------+--------+-------+


I tried to follow MySQL troubleshooting instructions and did:

MariaDB [(none)]&gt; use piwik
Database changed
MariaDB [piwik]&gt; drop table `#mysql50##sql-1526_3a`;
ERROR 1051 (42S02): Unknown table '#mysql50##sql-1526_3a'


From there, I read more blog posts and stack exchange answers and noticed that there are no #sql-1526_3a.idb and #sql-1526_3a.frm files in my /var/lib/mysql/piwik/ folder.

I then tried the following:

$ cd /var/lib/mysql/piwik
$ cp piwik_user.ibd #sql-1526_3a.ibd
$ cp piwik_user.frm #sql-1526_3a.frm


and restarted MariaDB.

Then I tried droping the table again:

MariaDB [piwik]&gt; drop table `#mysql50##sql-1526_3a`;
Database changed
Query OK, 0 rows affected (0.00sec)


Horray?!! Well, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; still lists the #sql-1526_3a table. On disk, the .frm file got deleted but the .ibd file remains.

So? Does someone know how I can remove these orphan tables for real?



EDIT

It seems to me these trailing temporary tables are "referenced" from /var/lib/mysql/ibdata1 before I shrunk it and enabled innodb_file_per_table = 1.
This may work -- I found it interesting in any event:  How to remove Temp Tables the proper way