Philipp Steinkrueger <[EMAIL PROTECTED]> wrote:
>
> if you dump the database and reload it into the server,
> there are chances that the autoincrement values are renumberd.
> i cant find a reference for that at mysql.com at the moment,
> but i am pretty sure i've read something like this and the problem is de
facto discussed in severel forums. i could not reproduce this with my
mysql 4.1 server, but perhaps its an mysql 3.2 issue, dont know.
FWIW, I have been dealing with this type of problem with
MySQL 4.0, trying to copy a table from Microsoft SQL
into MySQL using MS's DTS utility. The target table has an
auto-increment key, and MySQL will assign the next number in
the old sequence even if you do a "delete from table;" and wipe
it clean. I tried to insert a row with the auto-incremented
field's value set to 1 into the blank table after the above delete,
and that worked as expected (setting the key to 1). However, any
insert into the table that doesn't specify the value of the key
will pick up where the old key from the deleted data left off.
Since a picture is worth a thousand words, here's a picture of
table 'table1':
mysql> create table table1 (
-> key1 int(10) not null auto_increment primary key,
-> field1 char(15) not null
-> );
[Throw some data into the table.]
key1 (auto-increment) field1
-------------------- ------
1 One
2 Two
3 Three
mysql> delete from table1;
[We now have a blank table.]
mysql> insert into table1 values ('1','NewOne');
mysql> insert into table1 (field1) values ('NewTwo');
mysql> insert into table1 (field1) values ('NewThree');
One would expect the results to be the same as the above
table, but in reality what you get is:
key1 (auto-increment) field1
-------------------- ------
1 NewOne
4 NewTwo
5 NewThree
I don't know if the database restore is doing the same thing,
but my guess is that it is. The workaround is to drop the
entire table and then recreate it from scratch, which seems
to reset the auto_increment index whereas simply deleting
everything from the table doesn't.
Just figured that I'd mention it, since I was just dealing with
this unexpected MySQL 4.0 behavior working on something completely
unrelated to Bacula.
Oh, I guess that I should do an intro. I'm a new Bacula user,
who has suffered with ARCserve 9.0x on Linux for too many
years now. I'm sure that many of you know the pain of
dealing with that package, particularly the bundled Ingres
database that's nearly impossible to shut down without
total database corruption. Whoever wrote "ARGHserve" in
the Bacula web site testimonials said it all! In any case,
I'm running ARCserve for weekly tapes and disk incrementals
on about two dozen servers, and am running Bacula side-by-side
(on another server) to make second backups of a half dozen of
these systems onto a pair of LaCie 1TB USB2 disks. Once I
get Bacula tweaked and have confidence that it's working
properly, I'll install a copy of the Bacula SD onto the
ARCserve box and liberate the tape changer as the last
step of freeing ourselves from CA's pile of toss software.....
-Arthur
-------------------------------------------------------------------------
Arthur Emerson III Email: [EMAIL PROTECTED]
Network Administrator InterNIC: AE81
Mount Saint Mary College MaBell: (845) 561-0800 Ext. 3109
330 Powell Ave. Fax: (845) 562-6762
Newburgh, NY 12550 SneakerNet: Aquinas Hall Room 13
-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
