News for hackers. Stuff that matters.

A comment on Mistakes Beginners Make When Working with Databases. Parent comment

Re:Mistakes Beginners Make When Working with Databases by mercer on Wednesday June 08, 2016 @01:03PM (#11863727)
Wait, what? MySQL reuses them? Can you elaborate on that?
[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by pietroalbini on Wednesday June 08, 2016 @01:16PM (#11863828)
That'a bug reported in 2003 and not yet fixed.

https://bugs.mysql.com/bug.php?id=199

[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by mercer on Wednesday June 08, 2016 @02:54PM (#11864626)
Oh wow, that's definitely good to know. I do quite a number of migrations and so far this hasn't bitten me, but it could.
[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by davidw on Wednesday June 08, 2016 @03:24PM (#11864872)
Migrating from Mysql to Postgres might be a good migration :-)
[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by mercer on Thursday June 09, 2016 @03:03AM (#11867988)
Sadly that's not always an option for me...
[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by davidw on Wednesday June 08, 2016 @01:06PM (#11863745)
Here'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.

[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by billmalarky on Wednesday June 08, 2016 @01:20PM (#11863860)
How old was the version of mysql? Even years ago I remember mysql stored the auto increment value separately.
[ Parent | on HN ]
Re:Mistakes Beginners Make When Working with Databases by yardie on Wednesday June 08, 2016 @01:13PM (#11863804)
You 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.
[ Parent | on HN ]