Percona XtraDB Cluster - HA scalable solution for MySQL

Adding AUTO_INCREMENT column to a table results in data inconsistency

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
Status tracked in Trunk
5.6
Undecided
Unassigned
Trunk
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.

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