6 ms·
Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. I
by davidw 10y ago
Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course.
The second generation of machines, where I did have a say in things, used UUID's and Postgres.
- mercer 10y agoWait, what? MySQL reuses them? Can you elaborate on that?
- davidw 10y agoHere's what happens. Perhaps it has been fixed now: * New record is created, with new ID. * Record is deleted. * Database is shut down (cleanly). * Database is started again. * New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.
- billmalarky 10y agoHow old was the version of mysql? Even years ago I remember mysql stored the auto increment value separately.
- yardie 10y agoYou can also do the same in certain versions SQL Server. Change the ID field from auto-increment enabled to disabled. Add and delete a few fields. Then re-enable auto increment. Now, normally the server should start new IDs at the last position. Normally being the operative word.
- pietroalbini 10y agoThat'a bug reported in 2003 and not yet fixed. https://bugs.mysql.com/bug.php?id=199 https://bugs.mysql.com/bug.php?id=199
- muxxa 10y agoAm I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is that the database is much more comprehensible, e.g. foreign keys are legible without having to join back to the primary table. The relational model for data is pretty cool and breaks down when e.g. UNION doesn't work if 2 rows differ only in an arbitrary integer primary key.
- davidw 10y agoThe problem is that a lot of people end up screwing that up and picking something that doesn't work well as a primary key. Also, just using an integer is often a great way to get started without thinking about things so much. That works for a lot of web apps.
- ryanbrunner 10y agoThe problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.
- VLM 10y agoUnique and never changes can be two things concatenated, not one thing. If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application. Given the above, some important things concatenated and hashed works. Can always add an application ID or importer ID or process ID or a timestamp of high enough resolution. Obviously needs are different if you're trying to create a bank user database vs deduplicating sampled engineering data. So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.
- combatentropy 10y agoPostgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence. In Postgres a sequence is a database object that always returns the number 1 more than the number it last gave someone (This is customizable too, https://www.postgresql.org/docs/9.5/static/sql-createsequence.html https://www.postgresql.org/docs/9.5/static/sql-createsequenc...). With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers. UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.
- davidw 10y agoThese were not 'remote clients' accessing PG directly, but independent computers that were not even on or connected to the network some of the time. > UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. Exactly.
- dragonwriter 10y ago> Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence. Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.