Re: q re transferring mysql db from redhat to debian
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
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?
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,
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.
Obsidian Consulting Group
phone: +613 9355 7844