# Correct way to clear orphaned temporary tables preventing backup **URL:** \ **Category:** Percona XtraBackup\ **Created:** [November 1, 2017, 8:24am UTC](https://forums.percona.com/t/correct-way-to-clear-orphaned-temporary-tables-preventing-backup/5969 "2017-11-01T08:24:30Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\ **Post date:** [November 1, 2017, 8:24am UTC](https://forums.percona.com/t/correct-way-to-clear-orphaned-temporary-tables-preventing-backup/5969/1 "2017-11-01T08:24:30Z")
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 [url][https://mariadb.com/resources/blog/get-rid-orphaned-innodb-temporary-tables-right-way[/url]](https://mariadb.com/resources/blog/get-rid-orphaned-innodb-temporary-tables-right-way%5B/url%5D) 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 [url][http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html[/url]](http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html%5B/url%5D) 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: [url][http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html[/url]](http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html%5B/url%5D) 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 ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [November 6, 2017, 12:40pm UTC](https://forums.percona.com/t/correct-way-to-clear-orphaned-temporary-tables-preventing-backup/5969/2 "2017-11-06T12:40:55Z")
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.