wsrep_slave_FK_checks not working

[Marcelo Altmann]

wsrep_slave_FK_checks is not working properly. Even when we set it to OFF slave applier threads continue to validate foreign keys.

How to reproduce:

node1:
CREATE DATABASE IF NOT EXISTS fk;
use fk;
CREATE TABLE fk.parent ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
CREATE TABLE fk.child ( `id` int(11) NOT NULL, `pId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_parent` (`pId`), CONSTRAINT `fk_parent` FOREIGN KEY (`pId`) REFERENCES `parent` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB;
INSERT INTO fk.parent VALUES (1),(2),(3);
INSERT INTO fk.child VALUES (1,1),(2,2),(3,3);

node2:
add wsrep_slave_FK_checks = 0; to my.cnf
restart mysql

node1:
  set sql_log_bin=0; DELETE FROM fk.child WHERE id = 1; set sql_log_bin=1;DELETE FROM fk.parent WHERE id = 1;

From what I can see, nodes are honoring the session variable from master. On GRA_ file we can see SET @@session.foreign_key_checks=1:

[root@marcelo_altmann_pxc_node_2 mysql]# mysqlbinlog -vvv GRA_1_12.log.c
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170519 16:50:05 server id 2 end_log_pos 123 Start: binlog v 4, server v 5.7.17-13-57-log created 170519 16:50:05
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
fVofWQ8CAAAAdwAAAHsAAAABAAQANS43LjE3LTEzLTU3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AGFXQe4=
'/*!*/;
# at 123
#170519 16:50:05 server id 2 end_log_pos 150 Previous-GTIDs
# [empty]
# at 150
#170519 16:44:13 server id 1 end_log_pos 71 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1495226653/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 221
#170519 16:44:13 server id 1 end_log_pos 114 Table_map: `fk`.`parent` mapped to number 109
# at 264
#170519 16:44:13 server id 1 end_log_pos 150 Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
HVkfWRMBAAAAKwAAAHIAAAAAAG0AAAAAAAMAAmZrAAZwYXJlbnQAAQMAAA==
HVkfWSABAAAAJAAAAJYAAAAAAG0AAAAAAAEAAgAB//4BAAAA
'/*!*/;
### DELETE FROM `fk`.`parent`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Workaround:
Add to my.cnf
init_connect='SET foreign_key_checks=0;'

Run on MySQL:
SET GLOBAL init_connect='SET foreign_key_checks=0;'

[Marcelo Altmann (marcelo.altmann)]

Tested on 5.6.35-81.0-56 and 5.7.17-13-57 . Both versions are affected

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1981