Replace cause different auto_increment count between the source node and others
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership |
New
|
Low
|
Unassigned | |||
5.5 |
New
|
Low
|
Unassigned | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Won't Fix
|
Medium
|
Unassigned | |||
5.6 |
Fix Committed
|
Medium
|
Unassigned |
Bug Description
Server version: 5.5.34-55-log Percona XtraDB Cluster (GPL), wsrep_25.9.r3928
Description:
I build a two nodes xtradb cluster for test and they are running with the same configuration.
Table define:
mysql> show create table t2\G
*******
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`idd` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
Value of auto_increment is 37, execute sql:
mysql> update t2 set id=40 where id=1;
As known,the value of auto increment counter is still 37. execute sql:
mysql> replace t2 values(40,2);
Now comes the problem :On the source node ,auto increment counter's values is 41,but it still is 37 on the other nodes.
If now a insert statement execute on another node with an unspecified value for auto increment column . 38 will be allocated for new row 's auto increment column . When the counter increase up to 40,It get a error:
mysql> insert into t2(idd) values(0);
ERROR 1062 (23000): Duplicate entry '40' for key 'PRIMARY'
So,It could cause problems with different auto increment counter on the nodes in the cluster;
Some variables :
wsrep_auto_
innodb_
binlog_format=ROW
thanks
information type: | Public → Public Security |
information type: | Public Security → Private Security |
information type: | Private Security → Public |
description: | updated |
@liuxiaocheng,
I can reproduce this. From what I observed, the first insert gives a duplicate key error but subsequent insert succeeds. Also, since the insert is not replicated, the cluster consistency remains.
Have you noticed this with any other command or just with
replace?