Adding AUTO_INCREMENT column to a table results in data inconsistency

[Alex Yurchenko]

This is because different nodes use different auto_increment_offsets.

[Seppo Jaakola (seppo-jaakola)]

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)]

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

[Ovais Tariq (ovais-tariq)]

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)]

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

[Laurent Minost (lolomin)]

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

[Alex Yurchenko (ayurchen)]

Suggestion from lp:1183081 - return error when the operation is unsafe.

[Vladislav Klyachin (klyachin)]

Fix pushed in wsrep-5.5-23 in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.5-23/revision/3922
Fix pushed in wsrep-5.5 in revision: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3887
Fix pushed in wsrep-5.6 in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/3948

[Jay Janssen (jay-janssen)]

Can someone comment on what the fix actually is here?

[Alex Yurchenko (ayurchen)]

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)]

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

[Teemu Ollakka (teemu-ollakka)]

Fixes in:
5.5-23: http://bazaar.launchpad.net/~codership/codership-mysql/5.5-23/revision/3937
5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3919
5.6: http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4009