Emergency

Correct way to clear orphaned temporary tables preventing backup

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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/g...bles-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/...-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/...eshooting.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


  • #2
    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.
    Last edited by lorraine.pocklington; 11-06-2017, 01:11 PM. Reason: To add the last question.

    Comment

    Working...
    X