Not the answer you need?
Register and ask your own question!

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

jsancheztellojsancheztello EntrantInactive User Role Beginner
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)

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.