Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-1981

LP #1692113: wsrep_slave_FK_checks not working

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Low
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Marcelo Altmann last update 31-05-2017 20:03:28

      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 /!/;

      1. 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
      2. Warning: this binlog is either in use or was not closed properly.
        BINLOG '
        fVofWQ8CAAAAdwAAAHsAAAABAAQANS43LjE3LTEzLTU3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
        AGFXQe4=
        '/!/;
      3. at 123
        #170519 16:50:05 server id 2 end_log_pos 150 Previous-GTIDs
      4. [empty]
      5. 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/!/;
        /Unable to render embedded object: File (\C latin1 *//*) not found./;
        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
        /!/;
      6. at 221
        #170519 16:44:13 server id 1 end_log_pos 114 Table_map: `fk`.`parent` mapped to number 109
      7. 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
      '/!/;

          1. DELETE FROM `fk`.`parent`
          2. WHERE
          3. @1=1 /* INT meta=0 nullable=0 is_null=0 */
            ROLLBACK /* added by mysqlbinlog / /!*/;
            SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/;
            DELIMITER ;
      1. 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;'

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: