Replace cause different auto_increment count between the source node and others

Bug #1285070 reported by liuxiaocheng on 2014-02-26
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Low
Unassigned
5.5
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
*************************** 1. row ***************************
       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_increment_control=1
innodb_autoinc_lock_mode=2
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?

liuxiaocheng (devilkin0312) wrote :

@ Raghavendra D Prabhu (raghavendra-prabhu)

  1.It would cause a lot of duplicate key error if a batch of replace statements has executed on the source node and then insert rows on the other nodes. The lead a accident in my application.

   2.I just confirm replace command so far , suppose that commands which lead a different auto-increment counter between master and slave could have the problem.

thanks

Yiu-Chung Lee (lee-yiu-chung) wrote :

Ref: http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/18544-auto_increment-counter-not-replicated

I met the same problem (but using different statement). Below is the table schema:

CREATE TABLE `tbl_user_image` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`stub` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=383345 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And this is the sql statement used to update the table:

REPLACE INTO `tbl_user_image` (`stub`) VALUES ('a');

After some troubleshooting, I found out the problem is in mysql itself. I tested the above statement in a standard master-slave setup under mysql-server-5.5.35, and discovered the following:

1. No problem if binlog_format is set to statement (or mixed)
2. No problem if binlog_format is set to row and insert statement is used
3. No problem if binlog_format is set to row, replace statement is used and the table is empty (or no unique key conflict)
3. Problem if binlog_format is set to row and replace statement is used and there is a unique key conflict

Hui Liu (hickey) wrote :

It's a known issue of replace's effect for auto_increment, not introduced by galera.

Upstream bug link: http://bugs.mysql.com/bug.php?id=73563

Krunal Bauskar (krunal-bauskar) wrote :
Download full text (3.6 KiB)

This is no more an issue with latest-5.6. Please upgrade and re-try.

source-node:
mysql> 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
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> insert into t2 values (1, 100);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 1 | 100 |
+----+------+
1 row in set (0.00 sec)

mysql> update t2 set id=40 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 40 | 100 |
+----+------+
1 row in set (0.00 sec)

mysql> replace t2 values(40,2);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 40 | 2 |
+----+------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idd` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 40 | 2 |
| 41 | 0 |
+----+------+
2 rows in set (0.00 sec)

mysql>

---------

replicated-node

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 40 | 100 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | idd |
+----+------+
| 40 | 2 |
+----+------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idd` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENG...

Read more...

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.