Creating table clone when running cluster

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hi!

I’m trying to create a specific table “clone” in a cluster. I have imported a database with ~100 tables and because I’d like to import new data into one of the tables called mails, I have to make a backup of that table first in case that anything goes wrong. So I tried to use two different approaches, but the first one fails after some time after starting processing and the second one is not appropriate for cluster - PXC strict mode.

Here is the first approach …

CREATE TABLE mails_tmp LIKE mails;
INSERT INTO mails_tmp SELECT * FROM mails;

… which works well, but after approximately 10 minutes in processing, I always get this error: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

Second approach would be …

CREATE TABLE mails_test AS (SELECT * FROM mails);

… but due to the PXC strict mode, it is not possible - ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of CREATE TABLE AS SELECT with pxc_strict_mode = ENFORCING or MASTER.

I have not worked with cluster before but I’d like to move the application along with database to K8s since there are certain benefits which this environment would introduce. Currently I’m testing the migration locally and this cloning of table is part of the process, to test app with different set of data.

What am I doing wrong or is there any other way of doing this table clone?

Thank you for help!

1 Like
2

Hello @koei934,
Run the CREATE TABLE LIKE as you have done, then use pt-table-sync to copy the rows from original to new. This tool will copy the rows in chunks, which will help prevent the deadlock issue you are seeing. Or you can do this yourself by repeating INSERT and WHERE primaryKey BETWEEN X and Y queries.

1 Like