I'm working on an application that run two database servers in a replica set. As per best practice, the auto_increment on each server is set to 2 and they are offset, to prevent id collisions between the primary and backup server in the event of a failover. The way the application was written exposes the ID numbers of various entities to the end user, which typically results in non-sequential IDs displayed to the user. This is not ideal and our users are expecting.
I am tasked with finding a solution to stop using the automatically generated IDs in user displays. However I'm not much of a database guy and I am not sure what is the best way to generate a sequence of unique, sequential numbers for use in this purpose.
The one thing I have thought of is perhaps to create some kind of sequential number generating service that the application could consume and, upon creation of a new entity in the database, fetch a "customer friendly" identifier to use instead of the database ID. Perhaps set up as a service in Google Cloud (we use GC for a lot of other functionality) or something of that nature, but I wasn't sure if this was the smart way to go about it, or if there's a better solution I'm missing due to my lack of experience with db architecture.
1条答案
按热度按时间jhdbpxl91#
What you are describing is master-master replication in mysql. That means that any record created on one server will be automatically replicated on another.
It is customary to set increment on two servers in that way that a new ID on one server will never interfere with IDs on anothes server. In that way, first server will create IDs 1, 3, 5, 7, 9, 11,..., and the second will create IDs 2, 4, 6, 8, 10,... Both servers can create records simultaneously, and they will be replicated without the creating the error conditon when replicated ID will be already present on another server.
When this is happening users will see IDs incrementing by two instead of one. Do both server create IDs in a master-master configuration or does one acts as main and the other one is standby and takes over only when the main fails?
I would recommend adding another column (userID) to the table, which is not the primary key. Then make a stored procedure/trigger that looks for the max userID in the database and assigns max+1 for the new record. To search for the max exectively, make an index for that coulmn. Performance will drop a bit, but you will not make that index, the performance will drop significantly.
A possible scenario of adding to the table would be something like this:
Server 1 is main, and three records (1,3,5) are added:
The rows must have been replicated to Server2 automatically. Then Server2 takes over and becomes main, and three records (2,4,6) are added:
Records 2,4,6 have been replicated to Server1, and will not interfere with records 1,3,5, because IDs are not clashing. It does not matter, which server adds a new row from that point on. userID will always be incremented by 1 as long as the replication is working.
Although, you say you are not familiar with db architecture, you will have to learn how to write a trigger, or make sure the application adding data will look for the max userID and assign max+1 for the next record. If you have more tables, you will have to make a trigger for each that reveals the ID for the user.
The application that reveals the IDs to the user will have to be changed to use userID instead of ID. There is a danger that in case of some error userID will be duplicated or will have gaps larger than 1.
It is generally a good idea not to use database primary key IDs in app for any other purpose than for internal use. For users, use some other keys.