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)]> 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)]> use piwik
Database changed
MariaDB [piwik]> 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]> 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
.
Best Answer
This may work -- I found it interesting in any event: How to remove Temp Tables the proper way