Adding AUTO_INCREMENT column to a table results in data inconsistency

Bug #587170 reported by Alex Yurchenko
40
This bug affects 8 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
Fix Released
High
Teemu Ollakka
5.6
Fix Released
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_offsets.

Tags: auto-inc

Related branches

Changed in codership-mysql:
assignee: nobody → Seppo Jaakola (seppo-jaakola)
Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

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)

Revision history for this message
Ovais Tariq (ovais-tariq) wrote :
Download full text (5.2 KiB)

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_increment | 2 |
| auto_increment_offset | 1 |
+--------------------------+-------+
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_increment | 2 |
| auto_increment_offset | 2 |
+--------------------------+-------+
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),(3),(4),(5);
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.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
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 '
+vwAUQ8BAAAAZwAAAGsAAAABAAQANS41LjI4LXJlbDI5LjEtbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# 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=1359019268/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session...

Read more...

Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

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.

Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

The workaround seems to be to disable wsrep_auto_increment_control across the cluster.

node1> set global wsrep_auto_increment_control=0;
Query OK, 0 rows affected (0.00 sec)

node2> set global wsrep_auto_increment_control=0;
Query OK, 0 rows affected (0.00 sec)

node3> set global wsrep_auto_increment_control=0;
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),(3),(4),(5);
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
Revision history for this message
Laurent Minost (lolomin) wrote :

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://groups.google.com/forum/?fromgroups=#!searchin/codership-team/$2Bbug$2F587170$20

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
Revision history for this message
Alex Yurchenko (ayurchen) wrote :

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
Revision history for this message
Vladislav Klyachin (klyachin) wrote :
Revision history for this message
Jay Janssen (jay-janssen) wrote :

Can someone comment on what the fix actually is here?

Revision history for this message
Alex Yurchenko (ayurchen) wrote :

You can easily see it from the patch: just explicitly setting auto_increment_increrement and auto_increment_offset to 1 for the master thread before processing TOI action. Basically TOI actions don't need this autoincrement control at all due to strictly serial processing.

Revision history for this message
Teemu Ollakka (teemu-ollakka) wrote :

Test described in message #1 still causes inconsistency. Reopening for further investigation.

Revision history for this message
Teemu Ollakka (teemu-ollakka) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Duplicates of this bug

Other bug subscribers

Remote bug watches

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