Lets start by considering a scenario where records are being inserted in a single auto-increment table via different nodes of a multi-master cluster. One issue that might arise is ‘collision’ of generated auto-increment values on different nodes, which is precisely the subject of this article.
As the cluster is multi-master, it allows writes on all master nodes. As a result of which a table might get same auto-incremented values on different nodes on INSERTs. This issue is discovered only after the writeset is replicated and that’s a problem!
Galera cluster suffers with the similar problem.
Lets try to emulate this on a 2-node Galera cluster :
1) On node #1: MariaDB [test]> CREATE TABLE t1(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT)ENGINE=InnoDB; Query OK, 0 rows affected (0.07 sec) MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> INSERT INTO t1(c2) VALUES (1); …[Read more]