4

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.

  • I think you borked your catalog. Good luck. You may be able to dump reload to work around the problem. – Evan Carroll Sep 21 '17 at 16:00
  • When you say dump reload, do you mean dump the db to file, drop, then reimport? – Gregory Pakosz Sep 21 '17 at 19:42
0

This may work -- I found it interesting in any event: How to remove Temp Tables the proper way

  • Unfortunately it doesn't work. I tried it before posting :( – Gregory Pakosz Aug 16 '17 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.