1

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

0

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

| improve this answer | |
  • If it is a simple M-M, designate one Primary to handle the sequences table; make a separate connection to grab a num. Be ready to procedurally VOID ids that (rarely) get skipped. – Rick James Sep 18 at 17:54
1

All(?) INSERTs, including IODKU and REPLACE can burn ids, leading to gaps. This happens with the statement is rolled back. And for INSERT IGNORE when some UNIQUE triggers "ignore".

Even without multi-master, you can get AUTO_INCREMENT values out of order. This is because the auto_inc is grabbed when needed, but not replicated until COMMITted. Replicas will see the ids out of order.

Bottom line: AUTO_INCREMENT guarantees uniqueness, nothing else.

(Also, what Gordan says.)

| improve this answer | |
  • Thanks and you are right, gaps can also be happened when we delete a row. But as I've mentioned before we use IDs as our invoice numbers and gaps can get us in trouble with TAX laws in our country. So how can I solve this problem?? I can use DRBD solution to replicate database folder and make two servers use one database but like Gordan said, it will affect performance. – sajad kamali Sep 18 at 9:05
  • The obvious answer is that you cannot use auto-increment as your invoice numbers. You will need to implement a separate source of uniqueness with full serialisation and prevention of deletions and updating of the ID once inserted using triggers. – Gordan Bobic Sep 18 at 10:16
  • @sajadkamali - I updated your Question to point out the absolute requirement for no gaps, etc. Then I added another Answer. (The current answer says that you can't use auto_increment; the new Answer attempts to invent a suitable workaround that should work in Galera; I'm not sure about a simple multi-master configuration. – Rick James Sep 18 at 17:52
0

If you need decent performance - no, you can't have sequential IDs. You will have the same side effect with Galera, and different gaps with MySQL Cluster.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.