Correct way to clear orphaned temporary tables preventing backup

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

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.