[MDEV-6665] Wrong ID's generated with Galera using insert containing multiple rows. Created: 2014-08-30  Updated: 2014-11-19  Resolved: 2014-11-19

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 5.5.39-galera
Fix Version/s: 5.5.41-galera

Type: Bug Priority: Minor
Reporter: Eric Webster Assignee: Nirbhay Choubey (Inactive)
Resolution: Not a Bug Votes: 0
Labels: galera
Environment:

Debian 7, fully patched, MariaDB 5.5.39 Galera build (from your repo)


 Description   

If you do an insert with multiple values, auto increment ID generation is not replicated properly. It seems to go by the local galera auto_id offset values instead of those given by the master.

Master not running Galera but 5.5.39:

MariaDB [accounts]> create table test ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [accounts]> show create table test\G
 
*************************** 1. row ***************************
 
       Table: test
 
Create Table: CREATE TABLE `test` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `text` text,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MariaDB [accounts]>  insert into test (text) VALUES ("one"),("two");
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [accounts]> show table status like 'test'\G
 
*************************** 1. row ***************************
 
           Name: test
 
         Engine: InnoDB
 
        Version: 10
 
     Row_format: Compact
 
           Rows: 2
 
 Avg_row_length: 8192
 
    Data_length: 16384
 
Max_data_length: 0
 
   Index_length: 0
 
      Data_free: 0
 
 Auto_increment: 3 <------
 
    Create_time: 2014-08-30 00:55:42
 
    Update_time: NULL
 
     Check_time: NULL
 
      Collation: latin1_swedish_ci
 
       Checksum: NULL
 
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
 
 
 
MariaDB [accounts]> select * from test;
 
+----+------+
 
| id | text |
 
+----+------+
 
|  1 | one  |
 
|  2 | two  |
 
+----+------+
 
2 rows in set (0.00 sec)

Galera slave:

MariaDB [accounts]> show table status like 'test'\G
 
*************************** 1. row ***************************
 
           Name: test
 
         Engine: InnoDB
 
        Version: 10
 
     Row_format: Compact
 
           Rows: 2
 
 Avg_row_length: 8192
 
    Data_length: 16384
 
Max_data_length: 0
 
   Index_length: 0
 
      Data_free: 0
 
 Auto_increment: 9  <-----
 
    Create_time: 2014-08-30 00:55:11
 
    Update_time: NULL
 
     Check_time: NULL
 
      Collation: latin1_swedish_ci
 
       Checksum: NULL
 
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
 
 
 
MariaDB [accounts]> select * from test;
 
+----+------+
 
| id | text |
 
+----+------+
 
|  3 | one  |
 
|  6 | two  |
 
+----+------+
 
2 rows in set (0.01 sec)



 Comments   
Comment by Elena Stepanova [ 2014-08-30 ]

Back in days, Galera was meant to be run only with row binlog_format, in which case replication of auto-increment wouldn't matter. Maybe it's not so anymore, assigning to nirbhay_c to give a proper reply.

Comment by Eric Webster [ 2014-08-30 ]

I forgot to mention that this is using row based replication. A normal insert works correctly of course, it's only when you do multiple at once in the ("values"),("values") format.

Comment by Nirbhay Choubey (Inactive) [ 2014-11-19 ]

Its is an expected behavior added to avoid "collisions" in a cluster.
Refer this blog for an explanation : https://blog.mariadb.org/auto-increments-in-galera/

Comment by Eric Webster [ 2014-11-19 ]

That article discusses controlling the auto increment values when writes are being sent into the cluster directly. It at least doesn't suggest that they will be controlled when a slave is replicating data in directly. I'd really expect the binlog row data to win and it to accept the data as-is and not manipulate it at all since the source is from replication.

Comment by Nirbhay Choubey (Inactive) [ 2014-11-19 ]

In order to achieve that you can either (a) set master's binlog_format to ROW or (b) start
all galera nodes with wsrep_auto_increment_control=OFF.

Here is what I tried:

Topology:
master >> slave/node1 <<>> node2

Case 1:
master, slave/node1, node2 configuration:

binlog-format=row
 
log-bin
 
log-slave-updates

On master:

MariaDB [test]> create table test ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
MariaDB [test]> insert into test (text) VALUES ("one"),("two");

On node1/slave & node2:

MariaDB [test]> select * from test;
 
+----+------+
 
| id | text |
 
+----+------+
 
|  1 | one  |
 
|  2 | two  |
 
+----+------+

Case 2:

master configuration:

binlog-format=statement
 
log-bin
 
log-slave-updates

slave/node1 & node2 configuration:

binlog-format=row
 
log-bin
 
log-slave-updates

On master:

MariaDB [test]> create table test ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
MariaDB [test]> insert into test (text) VALUES ("one"),("two");

On node1 (slave) & node2:

MariaDB [test]> select * from test;
 
+----+------+
 
| id | text |
 
+----+------+
 
|  1 | one  |
 
|  3 | two  |
 
+----+------+

What you described is case#2 where master's binlog_format is statement. In this what happens is
when the statement is received by the slave galera node the auto increment values are generated
based on nodes auto_increment settings. But, since node's auto increment settings are different from
that of master, the generated values are different.

Comment by Eric Webster [ 2014-11-19 ]

That makes sense to me. I swear it was using RBR, as I noted above in an earlier comment, but the test system I was using is no longer around due to how long ago it was. I tried to duplicate it again, on a system using RBR and could not, so I can only guess that the previous master was using statement based replication. Without anyway to confirm it, it's the only possible explanation.

Generated at Thu Feb 08 07:13:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.