Not the answer you need?
Register and ask your own question!

Correct way to clear orphaned temporary tables preventing backup

mikesmikes EntrantCurrent User Role Beginner
Hi, This morning our normal backup failed, due to a timeout on obtaining a table lock, as the DB believed there were still three open temp tables.

These were present on both of our 2 slaves, though only 2 tables showed up with the following command ( 3 .frm files, only 2 .ibd)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
+
+
+
+
+
+
+
+
+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+
+
+
+
+
+
+
+
+
| 9218755 | mysqltmp/#sql7e1e_5a71601_1a9f5 | 1 | 5 | 9218724 | Antelope | Compact | 0 |
| 9218968 | mysqltmp/#sql7e1e_5a71601_1b1c5 | 1 | 5 | 9218937 | Antelope | Compact | 0 |
+
+
+
+
+
+
+
+
+
2 rows in set (0.00 sec)

I attempted the following just prior to the above statement.

mysql> DROP TEMPORARY TABLE IF EXISTS `#mysql50##sql7e1e_5a71601_1b1c5`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TEMPORARY TABLE IF EXISTS `#mysql50##sql7e1e_5a71601_1a9f5`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

based on advice seen here

https://mariadb.com/resources/blog/get-rid-orphaned-innodb-temporary-tables-right-way

but all this seems to have done, after a slave stop, and mysql restart, is generate the following errors (x2)

2017-11-01 12:40:08 7f9fa620d820 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2017-11-01 12:40:08 44245 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysqltmp/#sql7e1e_5a71601_1a9f5'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-11-01 12:40:08 44245 [ERROR] InnoDB: Tablespace open failed for '"mysqltmp"."#sql7e1e_5a71601_1a9f5"', ignored.

2017-11-01 12:40:08 7f9fa620d820 InnoDB: Error: table `mysqltmp`.`#sql7e1e_5a71601_1a9f5` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

I'm assuming that as the current show status is showing 0 Slave_open_temp_tables, that the next backup will be Ok.

What would be the suggested course of action to clear this properly ?

Thanks,

Mike

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi there, where you have this

    DROP TEMPORARY TABLE IF EXISTS

    The MariaDB blog suggests

    DROP TABLE

    Does that make a difference? You can use SHOW WARNINGS to clarify the reason for the warning.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.