-
Type:
Bug
-
Status: Done
-
Priority:
High
-
Resolution: Fixed
-
Affects Version/s: 8.0.18-internal, 5.6.x, 5.7.x
-
Fix Version/s: 8.0.18-26.4.3.2-internal, 5.6.47-28.40, 5.7.29-31.43
-
Component/s: None
-
Labels:None
-
Needs Review:Yes
-
Needs QA:Yes
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'