On Wednesday 07 September 2005 12:56, Philipp Steinkrueger wrote: > Hi Kern, > > >Perhaps it didn't drop the database but it did drop each table -- > > otherwise, how can the example from the Catalog Maintenance chapter of > > the manual work? > > > >mysqldump -f --opt bacula > bacula.sql > >mysql bacula < bacula.sql > >rm -f bacula.sql > > because of the --opt parameter in the mysqldump command which adds > drop-commands into bacula.sql which > are performed when you reinsert bacula.sql and so the tables are droped... > > >>what might have happend is the following: when you reload the database > >>by dumping it and > >>restoring it, autoincrement values may have been renumberd. usually the > >>autoincrement column > >>is defined as a key and due to this would not be allowed to be not > >> unique. > > > >Can you explain this or give an example? I don't see your point but would > >like to understand it, because a dump followed by a single restore should > >never create a condition that would cause Bacula to fail, otherwise there > > is a *big* problem. > > allright. first, this is based on speculation, because i dont have > enough time to read the bacula code. you > have to confirm or deny if the following is possible with bacula. > lets assume we have two tables. the first tables consists of two > columns, one of them is an autoincrement > column. the table would look like this after sometime: > > 324 fsdfd > 325 fsasr4g > 326 24325 > > now the other tables consists of two columns too, but there are no > autoincrement columns. the content > could look like this: > > 324 file1 > 325 file2 > 326 file3 > > if there is some relationship between the tables, lets say that the > second tables content is created based on the values > of the first tables content, then something could happend resulting in > affects described by christian. > 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.
This should not happen because when you dump the tables using the options specified above (or perhaps any options), the dump that mysql creates causes the tables to be put back *exactly* as they were. It explicitly specifies the Ids it wants for each INSERT so that any references between tables remain valid. > > anyway, if the autoincrement value is renumberd and bacula is performing > some action which > creates the content of table two, it looks into table one for "fsdfd" > and picks up the > value, which used to be 324 but is 1 after the dump-reload-procedure. > now its going to insert this into the second table, but > there is allready a row with id 1, and so it will complain about > duplicate rows. Although Bacula uses autoincrement, there is nothing in the code that cares what value is used for the Id. Of course, once the Id value is returned, it may be stored in other tables, so the Id cannot change unless Bacula explicitly changes the records itself (usually simply deletes unwanted records, but unless there is a bug, it also deletes all references to those deleted records). > perhaps the unproofed assumption of renumberd autoincrement values is > not even needed. if you dump and restore > without dropping first, you could mess up the content, too, producing > relation-problems like this... If you restore without dropping first, depending how MySQL does the restore, it will either totally mess up the database (duplicating records) or reject pretty much everything in the restore -- there are some tables that don't use keys if I remember right, so those tables would be messed up. At that point, anything could happen when trying to run Bacula since the database will not be consistent from a Bacula point of view. Anyway, I hope this doesn't happen to other users. If I were sure of the problem, I would document it ... > > with this argumentation one could perhaps even try to explain why > christians problem did not rise immediatly, but after two days. > > > best regards, > philipp ------------------------------------------------------- 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
