>>>>> On Wed, 19 Sep 2007 11:54:37 +0200, Cousin Marc said: > > I think the problem is linked to the fact dbcheck works more or less row by > row. > > If I understand correctly, the problem is that you have duplicates in the > path > table as the error comes from > SELECT PathId FROM Path WHERE Path='%s' returning more than one row > > You could try this query, it would probably be much faster : > > delete from path > where pathid not in ( > select min(pathid) from path > where path in > (select path from path group by path having count(*) >1) > group by path) > and path in ( > select path from path group by path having count(*) >1); > > I've just done it very quickly and haven't had time to doublecheck, so make a > backup before if you want to try it... :) > Or at least do it in a transaction so you can rollback if anything goes wrong.
Deleting from path like that could leave the catalog in a worse state than before, with dangling references in the File table. The dbcheck routine updates the File table to replace references to deleted pathids. Moreover, if deleting duplicate pathids is slow (i.e. there are many of them), then the catalog could be badly corrupted, so I don't see how you can be sure that the File records are accurate. It might be better to wipe the catalog and start again, or at least prune all of the file records before running dbcheck. __Martin > Le Monday 17 September 2007 19:24:17 Cedric Devillers, vous avez écrit : > > Hello, > > > > I must run dbcheck on my bacula database (postgresql backend) because of > > "sql_create.c:767 More than one Path!" warning on every file backed up > > (don't know where it comes from, actually). > > > > The operation is extremely slow, it ran for the all week-end and only > > deleted 2000 from the 51000 duplicate records. > > > > I've had already added the suggested indexes, but no differences. The > > counting of duplicate records is not slow at all (one or two minutes), > > only deleting. > > > > I know that the topic has been discussed before, but i don't find any > > solution in archives. I must say i'm not a database expert (at all), so > > the postgresql setup is almost the default one. I had also read that > > dbcheck is probably not optimized for big databases, but what else can i > > use to get rid of thoses "More than one Path!" warnings ? > > > > Any help would be appreciated :) > > > > Have a nice day. > > > > > > ------------------------------------------------------------------------- > > This SF.net email is sponsored by: Microsoft > > Defy all challenges. Microsoft(R) Visual Studio 2005. > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > > _______________________________________________ > > Bacula-users mailing list > > Bacula-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/bacula-users > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2005. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
