Adding AUTO_INCREMENT column to a table results in data inconsistency
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
| 5.5 |
High
|
Teemu Ollakka | |||
| 5.6 |
High
|
Teemu Ollakka | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
| 5.5 |
Fix Released
|
Undecided
|
Unassigned | ||
| 5.6 |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
This is because different nodes use different auto_increment_
Related branches
Changed in codership-mysql: | |
assignee: | nobody → Seppo Jaakola (seppo-jaakola) |
Seppo Jaakola (seppo-jaakola) wrote : | #1 |
Ovais Tariq (ovais-tariq) wrote : | #2 |
This does not seem to be repeatable on Percona Server 5.5.28 when using default replication not the galera one.
Server version: 5.5.28-rel29.1-log Percona Server with XtraDB (GPL), Release rel29.1, Revision 334
-- master server:
master [localhost] {msandbox} (test) > show global variables like 'auto_increment%';
+------
| Variable_name | Value |
+------
| auto_increment_
| auto_increment_
+------
2 rows in set (0.00 sec)
master [localhost] {msandbox} (test) > select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
-- slave server:
slave1 [localhost] {msandbox} (test) > show global variables like 'auto_increment%';
+------
| Variable_name | Value |
+------
| auto_increment_
| auto_increment_
+------
2 rows in set (0.00 sec)
-- test case run:
master [localhost] {msandbox} (test) > show tables;
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > create table t (i int);
Query OK, 0 rows affected (0.14 sec)
master [localhost] {msandbox} (test) > insert into t values (1),(2)
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > ALTER TABLE `t` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 5 rows affected (1.18 sec)
Records: 5 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 5 | 3 |
| 7 | 4 |
| 9 | 5 |
+----+------+
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 5 | 3 |
| 7 | 4 |
| 9 | 5 |
+----+------+
5 rows in set (0.00 sec)
And you can see that the AUTO_INC column has identical values.
The binary log contents on the master server are as follows:
*!40019 SET @@session.
/*!50003 SET @OLD_COMPLETION
DELIMITER /*!*/;
# at 4
#130124 10:20:58 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-rel29.1-log created 130124 10:20:58
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
+vwAUQ8BAAAAZwA
AAAAAAAAAAAAAAA
'/*!*/;
# at 107
#130124 10:21:08 server id 1 end_log_pos 215 Query thread_id=2 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=
SET @@session.
SET @@session.
SET @@session.
SET @@session.
/*!\C utf8 *//*!*/;
SET @@session.
Ovais Tariq (ovais-tariq) wrote : | #3 |
it looks like Galera replication will need to bundle the relevant session variables within the write-set when the write-set involves a query that has to be executed separately on each node, for example a DDL. In this case if the auto_increment* variables had been honoured then data inconsistency would not have been produced.
Ovais Tariq (ovais-tariq) wrote : | #4 |
The workaround seems to be to disable wsrep_auto_
node1> set global wsrep_auto_
Query OK, 0 rows affected (0.00 sec)
node2> set global wsrep_auto_
Query OK, 0 rows affected (0.00 sec)
node3> set global wsrep_auto_
Query OK, 0 rows affected (0.00 sec)
node1> create table t (i int);
Query OK, 0 rows affected (0.69 sec)
node1> insert into t values (1),(2)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
node1> ALTER TABLE `t` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 5 rows affected (1.44 sec)
Records: 5 Duplicates: 0 Warnings: 0
node1> select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 4 | 2 |
| 7 | 3 |
| 10 | 4 |
| 13 | 5 |
+----+------+
5 rows in set (0.00 sec)
node2> select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 4 | 2 |
| 7 | 3 |
| 10 | 4 |
| 13 | 5 |
+----+------+
5 rows in set (0.00 sec)
node3> select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 4 | 2 |
| 7 | 3 |
| 10 | 4 |
| 13 | 5 |
+----+------+
tags: | added: auto-inc |
Laurent Minost (lolomin) wrote : | #5 |
Hi,
I strongly think that this bug should be fixed and take into consideration with a higher priority because its occurrence is increasing and it is now opened/known for a long time, a simple search on Codership Google group leads to some cases reported recently : https:/
Moreover, the impact can be critical on a running cluster (losing 2 nodes on a 3 nodes cluster can have some consequences on traffic and response time if it is on a production cluster due to the fact that the cluster will run on one node only for a moment, from the time to restart others nodes then resyncing with SST ...)
IMO, this should normally not arrived in this case or in any case in a cluster environment as it is against the logic of having a cluster if finally all nodes but one go down !?
Regards,
Laurent
Changed in codership-mysql: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
Alex Yurchenko (ayurchen) wrote : | #6 |
Suggestion from lp:1183081 - return error when the operation is unsafe.
Changed in codership-mysql: | |
assignee: | Seppo Jaakola (seppo-jaakola) → Vladislav Klyachin (klyachin) |
Changed in codership-mysql: | |
status: | Confirmed → Fix Committed |
Vladislav Klyachin (klyachin) wrote : | #7 |
Fix pushed in wsrep-5.5-23 in revision: http://
Fix pushed in wsrep-5.5 in revision: http://
Fix pushed in wsrep-5.6 in revision: http://
Jay Janssen (jay-janssen) wrote : | #8 |
Can someone comment on what the fix actually is here?
Alex Yurchenko (ayurchen) wrote : | #9 |
You can easily see it from the patch: just explicitly setting auto_increment_
Teemu Ollakka (teemu-ollakka) wrote : | #10 |
Test described in message #1 still causes inconsistency. Reopening for further investigation.
Teemu Ollakka (teemu-ollakka) wrote : | #11 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/
Here's how to reproduce:
mysql> create table t (i int);
Query OK, 0 rows affected (0.37 sec)
mysql> insert into t values (1),(2) ,(3),(4) ,(5);
Query OK, 5 rows affected (0.00 sec)
mysql> ALTER TABLE `t` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY
KEY FIRST;Query OK, 5 rows affected (0.58 sec)
mysql> select * from t;
+----+------+
| id | i |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 5 | 3 |
| 7 | 4 |
| 9 | 5 |
+----+------+
5 rows in set (0.00 sec)
Node 2:
=======
mysql> select * from t;
+----+------+
| id | i |
+----+------+
| 2 | 1 |
| 4 | 2 |
| 6 | 3 |
| 8 | 4 |
| 10 | 5 |
+----+------+
5 rows in set (0.00 sec)