Waiting for table backup lock drop table if exists file_search

1

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
2

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

1 Like
4

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
5

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

2 Likes
6

Thank you for the clarification, this helps a lot.

1 Like
7

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

1 Like
8

Replication is not affected by the backup process

2 Likes
9

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
10

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

1 Like
11

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
12

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
13

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
14

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
15

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
16

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
17

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