MySQL slave_open_temp_tables always shows 1 I have a MySQL database set up for testing xtrabackup. The database is MySQL 5.5.52.

It is configured as a slave, however, there is very little in the way of replication (1 query every 10 seconds).

During the last test of xtrabackup (last night) it failed to complete due to:

180606 01:05:18 Slave open temp tables: 1
180606 01:05:18 Slave_open_temp_tables did not become zero after 300 seconds


Looking at the the database now, it is still showing Slave_open_temp_tables = 1.

I have tried restarting replication, and flushing the tables and logs. But it insists on staying at 1.

In the end I was forced to restart the database entirely, and that fixed the problem.

Is there a better way of 'flushing' this status without needing to resort to a full restart.
I found the way to do this.

STOP SLAVE;
SHOW SLAVE STATUS;


write down relay_master_log_file and exec_master_log_pos

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE = 'relay_master_log_file', MASTER_LOG_POS = exec_master_log_pos;
START SLAVE;


And now it reports Slave_open_temp_tables AS 0.