# Database backup freezes with deadlock

**URL:** <https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000>\
**Category:** Percona XtraDB Cluster 8.x\
**Tags:** mysql, percona, kubernetes\
**Created:** [October 18, 2023, 9:59am UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000 "2023-10-18T09:59:56Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tobias\_Grether](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tobias_grether/32/13318_2.png) [@Tobias\_Grether](https://forums.percona.com/u/Tobias_Grether)\
**Post date:** [October 18, 2023, 9:59am UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/1 "2023-10-18T09:59:56Z")

</div>

Hello,

we recently wanted to switch from a single MariaDB server to a replicated XtraDB instance. For this purpose I set up an XtraDB cluster in our kubernetes cluster with 7 nodes distributed across four geographical zones (US, EU, India, AP).

I then wanted to import one of our backups from our old database into the new cluster.  
The .sql file for that table is 2.1GB big and consists of roughly 4.5 Million rows.  
I tried importing it using

```bash
mysql -h mysql-prod -u root -p my_database < backup_file.sql

```

This will prompt me for a password, begin running and insert rows. However, after some time, I get the following error:

```auto
ERROR 1213 (40001) at line 93582: Deadlock found when trying to get lock; try restarting transaction

```

The given line is a regular part of an INSERT INTO statement that inserts the data into the database.

I’m not sure if this is an issue with xtradb or an issue with my configuration of it.  
Maybe someone knows whats wrong?

---

<div class="post-metadata">

**Author:** ![Sergey\_Pronin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/sergey_pronin/32/14887_2.png) [@Sergey\_Pronin](https://forums.percona.com/u/Sergey_Pronin)\
**Post date:** [October 18, 2023, 12:05pm UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/2 "2023-10-18T12:05:43Z")

</div>

Hello @Tobias_Grether ,

when you say you run on Kubernetes - do you use Percona Operator?

As for deadlock issue:  
Try looking at: [https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar\_innodb\_print\_all\_deadlocks](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks) - then you will be able to find deadlocks in the error log.

Another good tool that can help you is [pt-deadlock-logger](https://docs.percona.com/percona-toolkit/pt-deadlock-logger.html).

---

<div class="post-metadata">

**Author:** ![Tobias\_Grether](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tobias_grether/32/13318_2.png) [@Tobias\_Grether](https://forums.percona.com/u/Tobias_Grether)\
**Post date:** [October 18, 2023, 12:51pm UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/3 "2023-10-18T12:51:15Z")

</div>

Hello @Sergey_Pronin,

yeah i’m using the Percona Operator.  
I already enabled `innodb_print_all_deadlocks` using

```sql
SET GLOBAL innodb_print_all_deadlocks=1;

```

However none of the nodes actually seem to print relevant data.  
I tried running pt-deadlock-logger, I’m not sure about its usage though - do I need to point it to the specific node in the cluster that receives the request or can I point it to any node in the cluster?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [October 18, 2023, 10:40pm UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/4 "2023-10-18T22:40:54Z")

</div>

> [@Tobias\_Grether](#):
>
> However none of the nodes actually seem to print relevant data.

You checked MySQL error log on each node? That’s were the info would be written.

---

<div class="post-metadata">

**Author:** ![Tobias\_Grether](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tobias_grether/32/13318_2.png) [@Tobias\_Grether](https://forums.percona.com/u/Tobias_Grether)\
**Post date:** [October 19, 2023, 12:35pm UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/5 "2023-10-19T12:35:56Z")

</div>

Yeah I checked for each one of them, however it doesn’t seem to state anything related to deadlocks

---

<div class="post-metadata">

**Author:** ![jw12314](https://avatars.discourse-cdn.com/v4/letter/j/dfb087/32.png) [@jw12314](https://forums.percona.com/u/jw12314)\
**Post date:** [October 20, 2023, 4:14am UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/6 "2023-10-20T04:14:35Z")

</div>

I might be wrong and this might be totally offtopic, but tightly coupled database cluster with more than 1 hop is a no no.  
Take a look at this excellent post:

> **[How Not to do MySQL High Availability: Geographic Node Distribution with...](https://www.percona.com/blog/how-not-to-do-mysql-high-availability-geographic-node-distribution-with-galera-based-replication-misuse/)**
>
> Let's talk about MySQL high availability (HA) and synchronous replication once more. Don't misconfigured the connections between data centers!

And here more info how to setup this: [MySQL High Availability On-Premises: A Geographically Distributed Scenario](https://www.percona.com/blog/mysql-high-availability-on-premises-a-geographically-distributed-scenario/)

---

<div class="post-metadata">

**Author:** ![Tobias\_Grether](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tobias_grether/32/13318_2.png) [@Tobias\_Grether](https://forums.percona.com/u/Tobias_Grether)\
**Post date:** [October 20, 2023, 11:43am UTC](https://forums.percona.com/t/database-backup-freezes-with-deadlock/26000/7 "2023-10-20T11:43:28Z")

</div>

Hmm yeah thats actually a good link, thank you. I knew that this might be an issue but still wanted to try it either way.

Thanks guys and have a good one