[MDEV-32447] Using check_constraint_checks=OFF can break replication Created: 2023-10-11  Updated: 2023-10-17  Resolved: 2023-10-11

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

I've tried this on 10.6 but other versions are likely to be affected.

Put this into mysql-test/suite/rpl/t/rpl_check.test :

source include/master-slave.inc;
 
 
 
connection master;
 
 
 
create table t10(
 
  pk int primary key, 
  a int, 
  constraint a_big check (a>10)
 
);
 
 
 
insert into t10 values (1, 20);
 
set check_constraint_checks=off;
 
update t10 set a=a-15 where pk=1;
 
update t10 set a=a+100 where pk=1;
 
set check_constraint_checks=on ;
 
select * from t10;
 
 
 
sync_slave_with_master;
 
 
 
#  Here we are on the slave
 
select * from t10;
 
connection master;
 
 
 
drop table t10;
 
sync_slave_with_master;
 
 
 
--source include/rpl_end.inc

Run the test and it will fail with:

CURRENT_TEST: rpl.rpl_check
 
analyze: sync_with_master
 
mysqltest: At line 20: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 1166, 300, '')' returned NULL indicating slave SQL thread failure
 
 
 
...
 
Last_Error      Error 'CONSTRAINT `a_big` failed for `test`.`t10`' on query. Default database: 'test'. Query: 'update t10 set a=a-15 where pk=1'



 Comments   
Comment by Sergei Golubchik [ 2023-10-11 ]

I don't think it's a bug. One can also add a UNIQUE index on the slave side and insert a conflicting value on the master — it'll also break replication. There are many ways to break replication, if one really wants to do it. One can simply do STOP SLAVE too.

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