[MDEV-28382] Replication fails when dropping multiple tables with foreign key constraints multiple times Created: 2022-04-21  Updated: 2022-04-25

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.6.7
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Volker Klasen Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Dropping multiple tables that have foreign key constraints due to which one or some of the tables cannot be dropped and repeating dropping the same tables again will lead to a replication failure. On both primary and replica all tables will be dropped, but the replication stops with an error.

How to reproduce:

mysql (foobar)> create table a (id int primary key);
 
Query OK, 0 rows affected (0.013 sec)
 
 
 
mysql (foobar)> create table b (id int primary key, ref int, constraint `tablea` foreign key (ref) references a (id));
 
Query OK, 0 rows affected (0.011 sec)
 
 
 
mysql (foobar)> insert into a values (2);
 
Query OK, 1 row affected (0.003 sec)
 
 
 
mysql (foobar)> insert into b values (1, 2);
 
Query OK, 1 row affected (0.003 sec)
 
 
 
mysql (foobar)> drop table a, b;
 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 
mysql (foobar)> show tables;
 
+------------------+
 
| Tables_in_foobar |
 
+------------------+
 
| a                |
 
+------------------+
 
1 row in set (0.002 sec)
 
 
 
mysql (foobar)> drop table a, b;
 
ERROR 1051 (42S02): Unknown table 'foobar.b'
 
mysql (foobar)> show tables;
 
Empty set (0.000 sec)

The second drop table command causes the replication to stop.

Cheers
Volker

 Comments   
Comment by Alice Sherepa [ 2022-04-25 ]

I could not repeat it, could you please add the output of SHOW SLAVE STATUS; and also SHOW BINLOG EVENTS or attach the binlog file for these events.
What I am getting on 10.6.7:
use `test`; DROP TABLE `b` /* generated by server */
use `test`; DROP TABLE `a`,`b` /* generated by server */

Comment by Volker Klasen [ 2022-04-25 ]

Binlog Events:

| mysql-bin.006880 | 4076 | Gtid              |         2 |        4118 | GTID 0-2-370668                                                                                                     |
 
| mysql-bin.006880 | 4118 | Query             |         2 |        4209 | create database foobar                                                                                              |
 
| mysql-bin.006880 | 4209 | Gtid              |         2 |        4251 | GTID 0-2-370669                                                                                                     |
 
| mysql-bin.006880 | 4251 | Query             |         2 |        4364 | use "foobar"; create table a (id int primary key)                                                                   |
 
| mysql-bin.006880 | 4364 | Gtid              |         2 |        4406 | GTID 0-2-370670                                                                                                     |
 
| mysql-bin.006880 | 4406 | Query             |         2 |        4585 | use "foobar"; create table b (id int primary key, ref int, constraint `tablea` foreign key (ref) references a (id)) |
 
| mysql-bin.006880 | 4585 | Gtid              |         2 |        4627 | BEGIN GTID 0-2-370671                                                                                               |
 
| mysql-bin.006880 | 4627 | Table_map         |         2 |        4673 | table_id: 5686 (foobar.a)                                                                                           |
 
| mysql-bin.006880 | 4673 | Write_rows_v1     |         2 |        4711 | table_id: 5686 flags: STMT_END_F                                                                                    |
 
| mysql-bin.006880 | 4711 | Xid               |         2 |        4742 | COMMIT /* xid=3722155 */                                                                                            |
 
| mysql-bin.006880 | 4742 | Gtid              |         2 |        4784 | BEGIN GTID 0-2-370672                                                                                               |
 
| mysql-bin.006880 | 4784 | Table_map         |         2 |        4831 | table_id: 5687 (foobar.b)                                                                                           |
 
| mysql-bin.006880 | 4831 | Write_rows_v1     |         2 |        4873 | table_id: 5687 flags: STMT_END_F                                                                                    |
 
| mysql-bin.006880 | 4873 | Xid               |         2 |        4904 | COMMIT /* xid=3722174 */                                                                                            |
 
| mysql-bin.006880 | 4904 | Gtid              |         2 |        4946 | GTID 0-2-370673                                                                                                     |
 
| mysql-bin.006880 | 4946 | Query             |         2 |        5064 | use "foobar"; DROP TABLE "b" /* generated by server */                                                              |
 
| mysql-bin.006880 | 5064 | Gtid              |         2 |        5106 | GTID 0-2-370674                                                                                                     |
 
| mysql-bin.006880 | 5106 | Query             |         2 |        5228 | use "foobar"; DROP TABLE "a","b" /* generated by server */                                                          |
 
| mysql-bin.006880 | 5228 | Gtid              |         2 |        5270 | GTID 0-2-370675                                                                                                     |
 
| mysql-bin.006880 | 5270 | Query             |         2 |        5368 | drop database foobar                                                                                                |

show replica status:

mysql (mysql)> show replica status\G
 
*************************** 1. row ***************************
 
                Slave_IO_State: Waiting for master to send event
 
                   Master_Host: replicahost.domain
 
                   Master_User: repl
 
                   Master_Port: 3306
 
                 Connect_Retry: 60
 
               Master_Log_File: mysql-bin.006880
 
           Read_Master_Log_Pos: 5368
 
                Relay_Log_File: relay-bin.000002
 
                 Relay_Log_Pos: 1855
 
         Relay_Master_Log_File: mysql-bin.006880
 
              Slave_IO_Running: Yes
 
             Slave_SQL_Running: No
 
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1051
 
                    Last_Error: Error 'Unknown table 'foobar.b'' on query. Default database: 'foobar'. Query: 'DROP TABLE "a","b" /* generated by server */'
 
                  Skip_Counter: 0
 
           Exec_Master_Log_Pos: 5064
 
               Relay_Log_Space: 2462
 
               Until_Condition: None
 
                Until_Log_File: 
                 Until_Log_Pos: 0
 
            Master_SSL_Allowed: Yes
 
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: NULL
 
 Master_SSL_Verify_Server_Cert: Yes
 
                 Last_IO_Errno: 0
 
                 Last_IO_Error: 
                Last_SQL_Errno: 1051
 
                Last_SQL_Error: Error 'Unknown table 'foobar.b'' on query. Default database: 'foobar'. Query: 'DROP TABLE "a","b" /* generated by server */'
 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2
 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: Current_Pos
 
                   Gtid_IO_Pos: 0-2-370675
 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
 
                     SQL_Delay: 0
 
           SQL_Remaining_Delay: NULL
 
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 43
 
Slave_Non_Transactional_Groups: 0
 
    Slave_Transactional_Groups: 9681
 
1 row in set (0.001 sec)

Maybe an important detail: we're using row-based replication

Generated at Thu Feb 08 10:00:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.