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*/;
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: FK_checks = 0; to my.cnf
add wsrep_slave_
restart mysql
node1: bin=1;DELETE FROM fk.parent WHERE id = 1;
set sql_log_bin=0; DELETE FROM fk.child WHERE id = 1; set sql_log_
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 PSEUDO_ SLAVE_MODE= 1*/; _TYPE=@ @COMPLETION_ TYPE,COMPLETION _TYPE=0* /; AAHsAAAABAAQANS 43LjE3LTEzLTU3L WxvZwAAAAAAAAAA AAAAAAAAAAAAAAA A AAAAAAAAAEzgNAA gAEgAEBAQEEgAAX wAEGggAAAAICAgC AAAACgoKKioAEjQ A 1495226653/ *!*/; pseudo_ thread_ id=7/*! */; foreign_ key_checks= 1, @@session. sql_auto_ is_null= 0, @@session. unique_ checks= 1, @@session. autocommit= 1/*!*/; sql_mode= 1436549152/ *!*/; auto_increment_ increment= 2, @@session. auto_increment_ offset= 2/*!*/; character_ set_client= 8,@@session. collation_ connection= 8,@@session. collation_ server= 8/*!*/; lc_time_ names=0/ *!*/; collation_ database= DEFAULT/ *!*/;
/*!50530 SET @@SESSION.
/*!50003 SET @OLD_COMPLETION
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 '
fVofWQ8CAAAAdwA
AAAAAAAAAAAAAAA
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=
SET @@session.
SET @@session.
SET @@session.
SET @@session.
/*!\C latin1 *//*!*/;
SET @@session.
SET @@session.
SET @@session.
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 ' AAHIAAAAAAG0AAA AAAAMAAmZrAAZwY XJlbnQAAQMAAA= = AAJYAAAAAAG0AAA AAAAEAAgAB/ /4BAAAA GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; TYPE=@OLD_ COMPLETION_ TYPE*/; PSEUDO_ SLAVE_MODE= 0*/;
HVkfWRMBAAAAKwA
HVkfWSABAAAAJAA
'/*!*/;
### DELETE FROM `fk`.`parent`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_
/*!50530 SET @@SESSION.