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]
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] 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]
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