Craig Sanders wrote:
On Tue, Jul 27, 2004 at 09:00:58AM -0400, Fraser Campbell wrote:On July 27, 2004 03:58 am, Henrik Heil wrote:The record_ids will stay the same with mysqldump. What makes you think they will not?I have seen problems with this. The existing auto-incremented fields were just fine but new ones were a little bit off. In a normal mysqldb if you have a single record with id 1 and delete it then add another record the new record will get id 2 (not filling in the missing 1). I've seen a case that after a mysqldump and restore the new records did not honour have that behaviour, "missing" ids were reused. I'm sure that I did something wrong with the dump but in that case it was not important so I didn't research it further.that's bizarre....and could easily lead to a hopelessly corrupted database when other tables refer to that id field. how are you supposed to restore a mysql db from backup then?
Two answers:1) Why are you relying on the auto_increment field to increment from highest point each time? So long as it gives you a unique value (and it should always do that), it shouldn't matter if it's re-using an old value (if it does, you shouldn't have deleted the old value...). Certinaly, if you're referring to those IDs elsewhere, and you've deleted the record it was referring to, good database design would be to not leave the references lying around, imnsho.
2) You can set the point to increment from, in a fairly hackish way, by doing a "alter table tbl_name auto_increment = x" where x is the highest number in use. Requires scripting around your backup/restore process, unfortunately.
With regard 1, the actual definition of auto_increment doesn't preclude re-use of numbers as far as I know, so if you're relying on it not to, you've got broken code anyway. That means the mysqldump is doing the correct thing, according to spec for auto_increment - there's no requirement in there to retain the highest number. The name of auto_increment is misleading, obviously ;)
With regard Craig's comment, if your database leaves hanging references to non-existant data around, you've got a broken database, whether you've realised it yet or not.
General note: We make a policy of using auto_increment _only_ to create sequence tables, which we manage ourselves. This is in line with postgres and oracle's use of sequence tables, and makes porting easier. We don't bother with ensuring that the next ID is higher than all previous ones - as long as they're unique, that's sufficient, any references to a defunct entry are removed when the entry is removed.
KJL -- Kevin Littlejohn Obsidian Consulting Group phone: +613 9355 7844 skype: callto://silarsis