Purging Data When the Table Is Big and Has Children Tables

Hello friends, at Percona Managed Services, we usually do different types of things every day, including routine tasks, monitoring, and, very frequently, answering questions that are not always easy to answer.

A few days ago, a client asked us the following question: “ Hey Percona, I have a question and a problem simultaneously: I want to delete rows from a table from a specific date back because the data is not necessary. I tried to run the DELETE command, which gave me this error: Cannot delete or update a parent row: a foreign key constraint fails . Could you please help me?”

At first glance, the error message was obviously clear: the table from which rows were to be deleted had a child table, which prevented the execution of the DELETE directly.

“Don’t worry, we’ll take a look at the issue, and accordingly, we’ll proceed to suggest an execution plan,” we replied. And this is how this blog originates so that if you find yourself in the same situation, proceed similarly or at least consider some of the practices used to meet the objective.

First of all, let’s review the structure of the table, its size, and its constraints.

MySQL table

Let’s check the table size (the numbers of the table sizes are real, they don’t match the structure of the table, as I have removed the relevant columns to keep the whole thing anonymous).

+--------------+------------------+------------+---------+----------+---------+----------+
| table_schema | table_name       | table_rows | DATA_MB | INDEX_MB | FREE_MB | TOTAL_MB |
+--------------+------------------+------------+---------+----------+---------+----------+
| cust         | orders           |  101703010 |   50990 |    25760 |      70 |    76820 |
| cust         | exceptions       |   15251900 |    2110 |      640 |      50 |     2800 |
| cust         | personalizations |  119541910 |    9310 |     7090 |      60 |    16460 |
+--------------+------------------+------------+---------+----------+---------+----------+

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

| cust | orders | 101703010 | 50990 | 25760 | 70 | 76820 |

| cust | exceptions | 15251900 | 2110 | 640 | 50 | 2800 |

| cust | personalizations | 119541910 | 9310 | 7090 | 60 | 16460 |

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

The constraints:

mysql> SELECT *
FROM   information_schema.REFERENTIAL_CONSTRAINTS
WHERE  REFERENCED_TABLE_NAME IN ( 'orders' )G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: cust
          CONSTRAINT_NAME: FK_Exceptions_Orders_OrderId
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: cust
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: exceptions
    REFERENCED_TABLE_NAME: orders
*************************** 2. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: cust
          CONSTRAINT_NAME: OrderId
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: cust
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: personalizations
    REFERENCED_TABLE_NAME: orders
2 rows in set (0.03 sec)

mysql > SELECT *

FROM information_schema.REFERENTIAL_CONSTRAINTS

WHERE REFERENCED_TABLE_NAME IN ( 'orders' )G

*************************** 1. row ***************************

CONSTRAINT_CATALOG: def

CONSTRAINT_SCHEMA: cust

CONSTRAINT_NAME: FK_Exceptions_Orders_OrderId

UNIQUE_CONSTRAINT_CATALOG: def

UNIQUE_CONSTRAINT_SCHEMA: cust

UNIQUE_CONSTRAINT_NAME: PRIMARY

MATCH_OPTION: NONE

UPDATE_RULE: NO ACTION

DELETE_RULE: NO ACTION

TABLE_NAME: exceptions

REFERENCED_TABLE_NAME: orders

*************************** 2. row ***************************

CONSTRAINT_CATALOG: def

CONSTRAINT_SCHEMA: cust

CONSTRAINT_NAME: OrderId

UNIQUE_CONSTRAINT_CATALOG: def

UNIQUE_CONSTRAINT_SCHEMA: cust

UNIQUE_CONSTRAINT_NAME: PRIMARY

MATCH_OPTION: NONE

UPDATE_RULE: NO ACTION

DELETE_RULE: NO ACTION

TABLE_NAME: personalizations

REFERENCED_TABLE_NAME: orders

2 rows in set (0.03 sec)

Finally, we show you below the topology presented by the client in question:

1. mysqlserver01=127.0.0.2 [RW] [ver: 8.0.31-23]
    |___ 2. mysqlserver02=127.0.0.3 [SR] [ver: 8.0.31-23] (Slave_delay: 0)

1 2	1. mysqlserver01 = 127.0.0.2 [ RW ] [ ver : 8.0.31 - 23 ] \| __ _ 2. mysqlserver02 = 127.0.0.3 [ SR ] [ ver : 8.0.31 - 23 ] ( Slave_delay : 0 )

This means the following:

A primary server, with Percona Server for MySQL installed (in Read/Write mode) version 8.0.31-23.
One replica with the same software version (in Read-Only and Super-Read-Only mode).

What can we say or respond to this situation?

First, the table has two child tables, not just one.
Second, the size of the tables in play is important.
The person who tried to run this in production did not do the following:
- Run some previous backups of the rows to delete.
- Check if the table had referential integrity, child tables.
- Check the size of the table.
- Check the database’s topology, whether one or more servers were replicating, if it was a Percona XtraDB Cluster , etc.

What does all this imply?

If he were able to execute the DELETE (let’s assume he googled something and found a way to do it by executing SET FOREIGN_KEY_CHECKS=0 ), not having made a previous backup, no backup, bad idea.
Having executed such a DELETE and considering the massive number of rows to be deleted, the replication in the replica/s would be affected.
Having executed the DELETE of only the parent table using SET FOREIGN_KEY_CHECKS=0 , leaves the child tables with orphaned rows, with which we are violating the referential integrity of the model.
Depending on how much the application uses the table, such a high-volume transaction would have generated some lock, which in one way or another, would have caused downtime in the application.

So what should we do in these cases?

When you need to do this type of operation in which a large number of rows are involved, with constraints, it is always advisable to carry out certain checks, make a backup of the information before it is deleted, delete the data from the child tables using the criteria of the parent table (with pt-archiver ).

Very well. We are going to do the necessary tests for this to be carried out successfully, for which we will use the following test environment:

Below is the test topology (as you can see, we’re using the same version of Percona Server for MySQL ).

1. PS8_Primary=192.168.0.70 [RW] [ver: 8.0.31-23]
    |___ 2. PS8_Replica=192.168.0.71 [SR] [ver: 8.0.31-23] (Slave_delay: 0)

1 2	1. PS8_Primary = 192.168.0.70 [ RW ] [ ver : 8.0.31 - 23 ] \| __ _ 2. PS8_Replica = 192.168.0.71 [ SR ] [ ver : 8.0.31 - 23 ] ( Slave_delay : 0 )

Once created and loaded the tables (you can use this link to do so), we have something like this:

+--------------+------------------+------------+---------+----------+---------+----------+
| TABLE_SCHEMA | TABLE_NAME       | TABLE_ROWS | DATA_MB | INDEX_MB | FREE_MB | TOTAL_MB |
+--------------+------------------+------------+---------+----------+---------+----------+
| test         | orders           |      10000 |       2 |        0 |       4 |        6 |
| test         | exceptions       |      20000 |       2 |        0 |       4 |        6 |
| test         | personalizations |      20000 |       3 |        0 |       3 |        6 |
+--------------+------------------+------------+---------+----------+---------+----------+

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

| test | orders | 10000 | 2 | 0 | 4 | 6 |

| test | exceptions | 20000 | 2 | 0 | 4 | 6 |

| test | personalizations | 20000 | 3 | 0 | 3 | 6 |

+ -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- + -- -- -- -- - + -- -- -- -- -- +

Now, for the test, let’s remove a significant number of rows from the table. For this, we will execute this query in test.orders and use the CreatedDate field as a referenc e:

PS8_Primary (none)>  select count(1) from test.orders where CreatedDate < '2022-07-30';
+----------+
| count(1) |
+----------+
|     3749 |
+----------+
1 row in set (1.04 sec)

PS8_Primary ( none ) > select count (1) from test.orders where CreatedDate < '2022-07-30' ;

+----------+

| count (1) |

+----------+

| 3749 |

+----------+

1 row in set (1.04 sec)

Let’s try doing the same thing the client did to check if he was right:

PS8_Primary (none)> delete from test.orders where CreatedDate < '2022-07-30';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`exceptions`, CONSTRAINT `FK_Exceptions_Orders_OrderId` FOREIGN KEY (`OrderId`) REFERENCES `orders` (`OrderId`))

1 2	PS8_Primary ( none ) > delete from test.orders where CreatedDate < '2022-07-30' ; ERROR 1451 (23000): Cannot delete or update a parent row : a foreign key constraint fails (`test`.`exceptions`, CONSTRAINT `FK_Exceptions_Orders_OrderId` FOREIGN KEY (`OrderId`) REFERENCES `orders` (`OrderId`))

The procedure

Backup: the most crucial step in this process. In this way, we make sure that if for some reason, the data is needed again, it can be reinserted in the same table or another table in another schema.

Let’s create the /home/percona/Backup directory and check if there is enough disk space for such a backup (based on the table size, it should be at least the same or less). For this, we will use the replica so as no impact the operation of the primary instance.

[percona@PS8_Replica ~]$ pwd
/home/percona
[percona@PS8_Replica ~]$ mkdir /home/percona/Backup
[percona@PS8_Replica ~]$ cd /home/percona/Backup/
[percona@PS8_Replica Backup]$ pwd
/home/percona/Backup
[percona@PS8_Replica Backup]$

[ percona @ PS8_Replica ~ ] $ pwd

/ home / percona

[ percona @ PS8_Replica ~ ] $ mkdir / home / percona / Backup

[ percona @ PS8_Replica ~ ] $ cd / home / percona / Backup /

[ percona @ PS8_Replica Backup ] $ pwd

/ home / percona / Backup

[ percona @ PS8_Replica Backup ] $

For the backup, we will use mydumper , which allows us to use the following:

WHERE clauses filter the rows.
Use several threads in parallel to read the data (the default is 4).
Compress the backup file.

Next, these are the commands to execute for the backups. And we verify that each step finished OK:

[percona@PS8_Replica ~]$ mydumper --host=localhost --compress --verbose 3 --less-locking --database test --tables-list test.orders --where "CreatedDate < '2022-07-30'" --outputdir /home/percona/Backup --logfile /home/percona/Backup/test_orders.log
[percona@PS8_Replica ~]$ echo $?
0

[ percona @ PS8_Replica ~ ] $ mydumper -- host = localhost -- compress -- verbose 3 -- less - locking -- database test -- tables - list test .orders -- where "CreatedDate < '2022-07-30'" -- outputdir / home / percona / Backup -- logfile / home / percona / Backup / test_orders .log

[ percona @ PS8_Replica ~ ] $ echo $ ?

[percona@PS8_Replica ~]$ mydumper --host=localhost --compress --verbose 3 --less-locking --database test --tables-list test.exceptions --where "OrderId in (select OrderId from test.orders WHERE CreatedDate < '2022-07-30')" --outputdir /home/percona/Backup/ --logfile /home/percona/Backup/test_exceptions.log
[percona@PS8_Replica ~]$ echo $?
0

[ percona @ PS8_Replica ~ ] $ mydumper -- host = localhost -- compress -- verbose 3 -- less - locking -- database test -- tables - list test .exceptions -- where "OrderId in (select OrderId from test.orders WHERE CreatedDate < '2022-07-30')" -- outputdir / home / percona / Backup / -- logfile / home / percona / Backup / test_exceptions .log

[ percona @ PS8_Replica ~ ] $ echo $ ?

[percona@PS8_Replica ~]$ mydumper --host=localhost --compress --verbose 3 --less-locking --database test --tables-list test.personalizations --where "OrderId in (select OrderId from test.orders WHERE CreatedDate < '2022-07-30')" --outputdir /home/percona/Backup/ --logfile /home/percona/Backup/test_personalizations.log
[percona@PS8_Replica ~]$ echo $?
0

[ percona @ PS8_Replica ~ ] $ mydumper -- host = localhost -- compress -- verbose 3 -- less - locking -- database test -- tables - list test .personalizations -- where "OrderId in (select OrderId from test.orders WHERE CreatedDate < '2022-07-30')" -- outputdir / home / percona / Backup / -- logfile / home / percona / Backup / test_personalizations .log

[ percona @ PS8_Replica ~ ] $ echo $ ?

Let’s verify that the backup files exist and have data:

[percona@PS8_Replica Backup]$ ls -l /home/percona/Backup/*
-rw-rw-r--. 1 percona percona   226 Feb 23 19:29 /home/percona/Backup/metadata
-rw-rw-r--. 1 percona percona   151 Feb 23 19:29 /home/percona/Backup/test-schema-create.sql.gz
-rw-rw-r--. 1 percona percona     4 Feb 23 19:28 /home/percona/Backup/test.exceptions-metadata
-rw-rw-r--. 1 percona percona   327 Feb 23 19:28 /home/percona/Backup/test.exceptions-schema.sql.gz
-rw-rw-r--. 1 percona percona 59531 Feb 23 19:28 /home/percona/Backup/test.exceptions.00000.sql.gz
-rw-rw-r--. 1 percona percona     4 Feb 23 19:28 /home/percona/Backup/test.orders-metadata
-rw-rw-r--. 1 percona percona   306 Feb 23 19:28 /home/percona/Backup/test.orders-schema.sql.gz
-rw-rw-r--. 1 percona percona 42736 Feb 23 19:28 /home/percona/Backup/test.orders.00000.sql.gz
-rw-rw-r--. 1 percona percona     4 Feb 23 19:29 /home/percona/Backup/test.personalizations-metadata
-rw-rw-r--. 1 percona percona   304 Feb 23 19:29 /home/percona/Backup/test.personalizations-schema.sql.gz
-rw-rw-r--. 1 percona percona 57352 Feb 23 19:29 /home/percona/Backup/test.personalizations.00000.sql.gz
-rw-rw-r--. 1 percona percona  2794 Feb 23 19:28 /home/percona/Backup/test_exceptions.log
-rw-rw-r--. 1 percona percona  2735 Feb 23 19:28 /home/percona/Backup/test_orders.log
-rw-rw-r--. 1 percona percona  2806 Feb 23 19:29 /home/percona/Backup/test_personalizations.log

[ percona @ PS8_Replica Backup ] $ ls - l / home / percona / Backup / *

- rw - rw - r -- . 1 percona percona 226 Feb 23 19 : 29 / home / percona / Backup / metadata

- rw - rw - r -- . 1 percona percona 151 Feb 23 19 : 29 / home / percona / Backup / test - schema - create .sql .gz

- rw - rw - r -- . 1 percona percona 4 Feb 23 19 : 28 / home / percona / Backup / test .exceptions - metadata

- rw - rw - r -- . 1 percona percona 327 Feb 23 19 : 28 / home / percona / Backup / test .exceptions - schema .sql .gz

- rw - rw - r -- . 1 percona percona 59531 Feb 23 19 : 28 / home / percona / Backup / test .exceptions . 00000.sql.gz

- rw - rw - r -- . 1 percona percona 4 Feb 23 19 : 28 / home / percona / Backup / test .orders - metadata

- rw - rw - r -- . 1 percona percona 306 Feb 23 19 : 28 / home / percona / Backup / test .orders - schema .sql .gz

- rw - rw - r -- . 1 percona percona 42736 Feb 23 19 : 28 / home / percona / Backup / test .orders . 00000.sql.gz

- rw - rw - r -- . 1 percona percona 4 Feb 23 19 : 29 / home / percona / Backup / test .personalizations - metadata

- rw - rw - r -- . 1 percona percona 304 Feb 23 19 : 29 / home / percona / Backup / test .personalizations - schema .sql .gz

- rw - rw - r -- . 1 percona percona 57352 Feb 23 19 : 29 / home / percona / Backup / test .personalizations . 00000.sql.gz

- rw - rw - r -- . 1 percona percona 2794 Feb 23 19 : 28 / home / percona / Backup / test_exceptions .log

- rw - rw - r -- . 1 percona percona 2735 Feb 23 19 : 28 / home / percona / Backup / test_orders .log

- rw - rw - r -- . 1 percona percona 2806 Feb 23 19 : 29 / home / percona / Backup / test_personalizations .log

MySQL 5.7
End of Life

Compare Percona to Leading Database Solutions

Software
Downloads

Product
Documentation

Resource Hub

Financial Services

Driving Database Success

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

Purging Data When the Table Is Big and Has Children Tables

Conclusion

Bonus track

MySQL 5.7 End of Life

Compare Percona to Leading Database Solutions

Software Downloads

Product Documentation

Resource Hub

Financial Services

Driving Database Success

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

Purging Data When the Table Is Big and Has Children Tables

Conclusion

Bonus track

MySQL 5.7
End of Life

Software
Downloads

Product
Documentation