0

I'm developing an application which uses MySQL database. The application has grown to a point where it runs on more PCs on same network and has one central database running on server. All data are shared between the server and the PCs. I made synchronization that works from server to PCs, but I'm having some trouble synchronizing new data back because of unique primary keys.

For now I use composite primary key solution, where every distributed PC and DB has certain ID and the ID is composited in the primary key with other ID being incremented. I would like to be able to generate unique ID from the application before inserting to the DB and to be able easily synchronize new data to the server. I read a lot about UUIDs and its problem with clustering in databases. My question is what is the best and correct way to solve distributed database synchronization and collisions with primary keys?

One way I thought of what if I had one int primary auto-increment key just for clustering only and second unique key UUID binary(16) column for identification and foreign key relationships? Would be the appending performance better?

Thank you.

S3S
  • 24,483
  • 5
  • 24
  • 44
TomCrow
  • 102
  • 10
  • Why not have the database assign the id upon insert with an `AUTO_INCREMENT` column. This is a pretty common use case with mysql as a back end. Darn near every web app built on a LAMP stack has multiple concurrent users with primary keys being assigned by the database on insert. You insert in your main table, then you [retrieve the newly generated ID using `SELECT LAST_INSERT_ID()`](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html) and continue inserting into your downstream tables. – JNevill Jan 04 '19 at 14:41
  • Certainly a UUID isn't out of the question either though. One of the last thick client applications connected to a centralized database type applications I worked on I used a GUID (C#) produced by the application as the key. Teradata was the database on the back end though, so I'm not certain what the performance impact would be switching to a mysql backend. – JNevill Jan 04 '19 at 14:43
  • i think this [Advantages and disadvantages of GUID / UUID database keys](https://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys) pretty much covers it... Storing a GUID/UUID in binary is indeed the most optimal form.. With [innodb as table engine](https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html) you have to consider avoiding the GUID/UUID as PRIMARY KEY because secondary Indexes gets a copy from the PRIMARY KEY also within them which makes the indexes pretty disk consuming. – Raymond Nijland Jan 04 '19 at 15:10

0 Answers0