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

DDL to add FK on one node fails but completes on other nodes causing inconsistency

    XMLWordPrintable

    Details

    • Needs Review:
      Yes
    • Needs QA:
      Yes

      Description

      Launch a 3 node PXC 5.7 cluster.

      Server version: 5.7.28-31-57-log Percona XtraDB Cluster (GPL), Release rel31, Revision ef2fa88, WSREP version 31.41, wsrep_31.41

       

      +------+---------+----------------------+
      | NAME | STATE | IPV4 |
      +------+---------+----------------------+
      | pxc1 | RUNNING | 10.84.148.120 (eth0) |
      +------+---------+----------------------+
      | pxc2 | RUNNING | 10.84.148.214 (eth0) |
      +------+---------+----------------------+
      | pxc3 | RUNNING | 10.84.148.240 (eth0) |
      +------+---------+----------------------+

       

      Use the attached test case:

       

      #!/bin/bash
      MYSQLROOTCMD='mysql -uroot -psecret'
      MYSQLTESTCMD='mysql -umytest -pmytest'
      NODE1=10.84.148.120
      NODE2=10.84.148.214
      $MYSQLROOTCMD -h$NODE1 -e "drop user 'mytest'@'10.%'";
      $MYSQLROOTCMD -h$NODE1 -e "drop database mytest1";
      $MYSQLROOTCMD -h$NODE1 -e "select @@hostname, user()";
      $MYSQLROOTCMD -h$NODE1 -e "create database mytest1";
      $MYSQLROOTCMD -h$NODE1 -e "create user 'mytest'@'10.%' identified by 'mytest'";
      $MYSQLROOTCMD -h$NODE1 -e "grant all privileges on mytest1.* to 'mytest'@'10.%'";
      $MYSQLROOTCMD -h$NODE1 -e "create table mytest1.t1 ( id int primary key auto_increment, s int, m int, v varchar(100), key s_idx(s), key m_idx(m) ) engine=innodb";
      $MYSQLTESTCMD -h$NODE1 -v -e "select @@hostname,user(); set foreign_key_checks=0; alter table mytest1.t1 add foreign key (m) references mytest2.t1 (id);";
      $MYSQLROOTCMD -h$NODE1 -e "select @@hostname; show create table mytest1.t1\G"
      $MYSQLROOTCMD -h$NODE2 -e "select @@hostname; show create table mytest1.t1\G"
      

       

      Last two commands will show table DDL inconsistency:

      + mysql -uroot -psecret -h10.84.148.120 -e 'select @@hostname; show create table mytest1.t1\G'
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +------------+
      | @@hostname |
      +------------+
      | pxc1 |
      +------------+
      *************************** 1. row ***************************
       Table: t1
      Create Table: CREATE TABLE `t1` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `s` int(11) DEFAULT NULL,
       `m` int(11) DEFAULT NULL,
       `v` varchar(100) DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `s_idx` (`s`),
       KEY `m_idx` (`m`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      + mysql -uroot -psecret -h10.84.148.214 -e 'select @@hostname; show create table mytest1.t1\G'
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +------------+
      | @@hostname |
      +------------+
      | pxc2 |
      +------------+
      *************************** 1. row ***************************
       Table: t1
      Create Table: CREATE TABLE `t1` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `s` int(11) DEFAULT NULL,
       `m` int(11) DEFAULT NULL,
       `v` varchar(100) DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `s_idx` (`s`),
       KEY `m_idx` (`m`),
       CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`m`) REFERENCES `mytest2`.`t1` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin

       

      SHOW BINLOG EVENTS in pxc2 shows the ALTER statement completed:

      | pxc2-bin.000003 | 14649 | Query | 1 | 14791 | alter table mytest1.t1 add foreign key (m) references mytest2.t1 (id)

       

      But it failed on pxc1 where it was executed:

       

      --------------
      alter table mytest1.t1 add foreign key (m) references mytest2.t1 (id)
      --------------
      ERROR 1142 (42000) at line 1: REFERENCES command denied to user 'mytest'@'pxc3.lxd' for table 'mytest2.t1'
      

       

       

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              jericho.rivera Jericho Rivera
              Votes:
              2 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 2 days, 5 hours, 10 minutes
                  2d 5h 10m

                    Smart Checklist