Adding AUTO_INCREMENT column to a table results in data inconsistency

Bug #587170 reported by Alex Yurchenko on 2010-05-29
40
This bug affects 8 people
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_offsets.

Related branches

Changed in codership-mysql:
assignee: nobody → Seppo Jaakola (seppo-jaakola)
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)

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...

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.

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
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
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
Jay Janssen (jay-janssen) wrote :

Can someone comment on what the fix actually is here?

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.

Teemu Ollakka (teemu-ollakka) wrote :

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

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  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers