Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.39-galera
-
Fix Version/s: 5.5.41-galera
-
Component/s: Galera
-
Labels:
-
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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/
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.
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.
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.
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 Choubey to give a proper reply.