[MDEV-31683] Replication aborts upon operations with broken foreign keys Created: 2023-07-13  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Replication, Storage Engine - InnoDB
Affects Version/s: 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Set to Minor because tampering with foreign keys via FOREIGN_KEY_CHECKS=OFF rarely ends well anyway. The scenario below is particularly unfortunate for replication, because on the master the first ALTER, which fails, apparently somehow disables the broken foreign key, so the second ALTER succeeds without complaints; but since the first ALTER fails, it is not written to the binary log, so the slave only sees the second ALTER, and thus the second ALTER fails on the slave. I don't even know whether it is InnoDB, or replication, or server here to blame.

--source include/have_partition.inc
 
--source include/have_innodb.inc
 
--source include/master-slave.inc
 
 
 
SET FOREIGN_KEY_CHECKS = OFF;
 
CREATE TABLE t1 (b TEXT, c INT, UNIQUE(b), FOREIGN KEY (c) REFERENCES t2 (a)) ENGINE=InnoDB;
 
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
 
SET FOREIGN_KEY_CHECKS = ON;
 
--error ER_ERROR_ON_RENAME
 
ALTER TABLE t1 FORCE;
 
ALTER TABLE t2 PARTITION BY KEY(a);
 
 
 
--sync_slave_with_master
 
 
 
# Cleanup
 
--connection master
 
DROP TABLE t1, t2;
 
--source include/rpl_end.inc

10.6 8171f9da

 
2023-07-14  0:35:45 8 [ERROR] Slave SQL: Error 'Cannot delete or update a parent row: a foreign key constraint fails' on query. Default database: 'test'. Query: 'ALTER TABLE t2 PARTITION BY KEY(a)', Gtid 0-1-3, Internal MariaDB error code: 1217
 
2023-07-14  0:35:45 8 [Warning] Slave: Cannot delete or update a parent row: a foreign key constraint fails Error_code: 1217
 
2023-07-14  0:35:45 8 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 693

Reproducible on 10.6+.
The test case behaves differently on 10.4-10.5, the second CREATE TABLE there just fails. Maybe it can be modified to fail the same way on 10.4-10.5, I didn't try.
Generated at Thu Feb 08 10:25:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.