In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 and the auto_increment_offset accordingly. When I go over the 30 masters, this system is going to crash, so I would like the following:

The Auto-increment value is created by using the following calculation:

auto_increment_offset + N × auto_increment_increment

Is it possible to make the auto-increment-increment dynamic like this:

(SELECT COUNT(id) FROM Clients) + N x (SELECT COUNT(id) FROM Clients)

When I add a client to the table, the other clients will automatically be upping the increment.

to be on the safe side I probably should add a buffer:

(SELECT COUNT(id) FROM Clients) + N x ((SELECT COUNT(id) FROM Clients) + 5)

Can anyone tell me if this could be done, or if there is another way to accomplish this?

1 Answer 1


No, the auto increment functionality of MySQL (or MariaDB) does not provide some kind of "dynamic" auto increment values like you request. The formula you arranged is all MySQL can do.

In your case you might be better of in just abandoning the auto increment column and use a custom column instead. You can fill this for example with UUIDs. This does not guarantee uniqueness of generated IDs, but the possibility to generate the exact same IDs is very low.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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