UK restriction not working when inserting inside a transaction from different nodes

[Jesús Sánchez]

Hello,

We have found that when inserting inside a trx from different nodes, UK checks are not working and we are able to insert duplicated values.

Here the table definition:

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `c3` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

FROM NODE 1:
mysql> start transaction; insert into t1(c2,c3) values(1,'A');
Query OK, 0 rows affected (0.00 sec)

FROM NODE 2:
mysql> start transaction; insert into t1(c2,c3) values(1,'B');
Query OK, 0 rows affected (0.00 sec)

FROM NODE 1:
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

FROM NODE 2:
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 1 | A |
| 2 | 1 | B |
+----+------+------+
2 rows in set (0.01 sec)

VERSION:

mysql> show global variables like '%version%';
+-------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------------------+
| innodb_version | 5.5.37-35.0-35.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.37-35.0-55 |
| version_comment | Percona XtraDB Cluster (GPL), Release 25.10, wsrep_25.10.r3985 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+----------------------------------------------------------------+

mysql> show global status like '%provider_version%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| wsrep_provider_version | 2.10(r175) |
+------------------------+------------+
1 row in set (0,00 sec)

[Nilnandan Joshi (nilnandan-joshi)]

Verified with PXC 5.5.37

Node 1:

mysql> CREATE TABLE `tb1` (
    -> `c1` int(11) NOT NULL AUTO_INCREMENT,
    -> `c2` int(11) DEFAULT NULL,
    -> `c3` varchar(1) DEFAULT NULL,
    -> PRIMARY KEY (`c1`),
    -> UNIQUE KEY `c2` (`c2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> start transaction; insert into tb1(c2,c3) values(1,'A');
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Node 2:

mysql> start transaction; insert into tb1(c2,c3) values(1,'B');
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Node 1:

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

Node 2:

mysql> commit;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from tb1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 1 | A |
| 2 | 1 | B |
+----+------+------+
2 rows in set (0.00 sec)

mysql>

[Nilnandan Joshi (nilnandan-joshi)]

Its not happening with PXC 5.6.19, while committing transaction on node2, it gives deadlock error.

mysql> start transaction; insert into t1(c2,c3) values(1,'B');
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> commit;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>
mysql> select * from t1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 5 | 1 | A |
+----+------+------+
1 row in set (0.00 sec)

mysql>

[Seppo Jaakola (seppo-jaakola)]

This is not happening with Galera Cluster 5.5.38 either:

MySQL [test]> start transaction; insert into tb1(c2,c3) values(1,'B');

MySQL [test]> commit;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
MySQL [test]> select * from tb1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 1 | A |
+----+------+------+

And GC 5.6.20 is also trouble free with this respect

[Jesús Sánchez (jesus-stdm)]

Sorry but i can't find 5.5.38 version in your repo/website.

The latest version i see is 5.5.37-25.10-756.wheezy. and I can reproduce the bug on that.

NODE 1:

mysql> show global variables like '%version%';
+-------------------------+-----------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------------------------------------------------------------+
| innodb_version | 5.5.37-35.0-35.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.37-35.0-25.10-log |
| version_comment | Percona XtraDB Cluster binary (GPL) 5.5.37-25.10, Revision 756, wsrep_25.10.r3985 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-----------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE `t1` (
    -> `c1` int(11) NOT NULL AUTO_INCREMENT,
    -> `c2` int(11) DEFAULT NULL,
    -> `c3` varchar(1) DEFAULT NULL,
    -> PRIMARY KEY (`c1`),
    -> UNIQUE KEY `c2` (`c2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> start transaction; insert into t1(c2,c3) values(1,'A');
Query OK, 0 rows affected (0.00 sec)

NODE 2:

mysql> start transaction; insert into t1(c2,c3) values(1,'B');
Query OK, 0 rows affected (0.00 sec)

NODE 1:

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

NODE 2:

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 4 | 1 | B |
| 5 | 1 | A |
+----+------+------+
2 rows in set (0.00 sec)

Thanks in advance

[Shahriyar Rzayev (rzayev-sehriyar)]

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