Waiting for table backup lock drop table if exists file_search

Does running a backup cause the database to lock. While running show processlist i am getting Waiting for table backup lock drop table if exists file_search. I am currently running a backup and inserting data using scripts. The jobs are failing with deadlock errors.

1 Like

Hello, during a backup you should not run any DDL statements, as the backup requires a metadata lock at certain point. Check Backup Locks for more info

1 Like

The link does not say innodb? Only MyISAM, CSV, MEMORY, ARCHIVE, TokuDB, and MyRocks tables. All of my tables are innodb, so I shouldn’t get the lock?

1 Like

Perhaps the redaction is not clear but the lock to prevent DDL is taken for all tables, no matter which storage engine.

2 Likes

Thank you for the clarification, this helps a lot.

1 Like

Does replication still work during backups or are those in a wait state as well?

1 Like

Replication is not affected by the backup process

2 Likes

Does backup lock all nodes on the cluster from dml statements? I am running a backup and the node has issued the waiting for table backup lock the other node i tried to do some inserts and updates but I am running into table locks.

1 Like

Are you running Percona XtraDB Cluster or traditional replication? which version? did you check that no DDL commands are being run?

1 Like

running xtradb cluster 8.0.26. there might be a DDL running on the none backup node. Did something change between cluster 5.7 and 8.0.x. I have done the same back and the same activity, but these issues never happened on version 5.7. The only difference is i a using xbstream and on 5.7 i was using tar

1 Like

OK, since you are running a cluster remember DDL entails a cluster-wide lock (in TOI mode) so that will conflict with the backup lock. You should avoid running DDL on ANY node while the backup is running.

1 Like

thank you you have been really helpful. did this change between 5.7 and 8.0.x? With the older version we never encountered this issue. We used dml statements with temporary tables in the older version. is there a flag to ignore temp tables?

1 Like

Nothing changed in this regard to the best of my knowledge. You should see the same behavior in 5.7… is it possible your DDL statements were not run at the same time as the backup before?

1 Like

No it is not possible. the DDL statement is in a stored procedure that is used heavily (probably every 3rd hit to my application). It would be impossible for the stored procedure to not run during the backups.

1 Like

I see. The only thing that comes to mind is that perhaps your dataset is bigger now so backups take longer and you notice the impact. If the backup used to take a short time before maybe it was too fast to notice?

1 Like

No the dataset is more or less the same size about 1.3T. Since switching to xbstream the backups seem to go a lot quicker.

1 Like