Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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