MariaDB – Master to Master Replication Auto Increment Not Sequential

linuxmariadbMySQL

I have two master to master MariaDB server replication with the below settings:

server A with 192.168.1.100:
------------------------------
bind-address            = 0.0.0.0
server-id       = 1
log-bin         = "mysql-bin"
binlog-do-db        = wordpress
replicate-do-db     = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 1

and:

Server B with 192.168.1.200:
---------------------------------
bind-address            = 0.0.0.0
server-id               = 2
log-bin                 = "mysql-bin"
binlog-do-db            = wordpress
replicate-do-db         = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 2

The replication works great but I have problem with auto increments. As you see they are not sequential. For example:

Server A increments: 1, 3, 5, 7, 9 and …

Server B increments: 2, 4, 6, 8 and …

The problem occurs when I insert 2 records in server A and 1 record in server B, the total primary key of the table will look like this: ID : 1, 3, 4

As you see number two is missing because I have inserted two records in server A. But I need auto increment to generate sequential numbers since we use them as our invoice numbers. Gaps can get us in trouble with TAX laws in our country.

I know this is the internal mechanism for MariaDB and MySQL databases in order to avoid duplicate keys. but I just wanted to know if there is an alternative HA solution for sequential index replication.

Thank you

Best Answer

AUTO_INCREMENT cannot be trusted to avoid gaps in the numbers. It appears that you must avoid them.

Plan A: Switch to MariaDB and use a SEQUENCE table.

Plan B: Simulate such.

Have a table with 2 columns in one row. (A second sequence could use a second row, etc.) Something like:

CREATE TABLE sequences (
    key CHAR(1) NOT NULL DEFAULT 'a',  -- the name of the sequence
    invoice_num INT UNSIGNED NOT NULL,
    PRIMARY KEY(key)
) ENGINE=InnoDB;

key is needed to uniquely identify the row and lock it (below).

To get the next invoice number:

INSERT INTO sequences (key, invoice_num)
        ('a', 1)            -- inserts once; updates the rest of the time
    ON DUPLICATE KEY
    UPDATE invoice_num = LAST_INSERT_ID(VALUES(invoice_num) + 1)
check for errors: that IODKU should retry in Galera, but there still could be an error
SELECT LAST_INSERT_ID();   -- to get the new value (local to connection)

Please test this thoroughly.

That should work even with requests for invoice numbers hitting all Galera nodes "simultaneously". autocommit is sufficient. That commit is implicit in the IODKU. It will retry if the commit fails (due to a "simultaneous" hit from another node).

Related Question