[Bacula-bugs] [bacula 0001974]: dbcheck does single delete for cleanup database

[Mantis B. T. <no...@bu...>]

The following issue has been CLOSED 
====================================================================== 
http://bugs.bacula.org/view.php?id=1974 
====================================================================== 
Reported By:                emundo
Assigned To:                
====================================================================== 
Project:                    bacula
Issue ID:                   1974
Category:                   dbcheck
Reproducibility:            always
Severity:                   major
Priority:                   high
Status:                     closed
Resolution:                 open
Fixed in Version:           
====================================================================== 
Date Submitted:             2013-01-09 14:01 UTC
Last Modified:              2013-01-17 08:15 UTC
====================================================================== 
Summary:                    dbcheck does single delete for cleanup database
Description: 
I migrated from bacula 2.4 to 5.0 (sqlite3 to mysql)

As we did never a dbcheck before I did one last night.


> dbcheck -vfc bacula-dir.conf
9 Eliminate orphaned Path records

Checking for orphaned Path entries. This may take some time!
Found 75750 orphaned Path records.
Print them? (yes/no): no
Deleting 75750 orphaned Path records.
Deleting: DELETE FROM Path WHERE PathId=34211
Deleting: DELETE FROM Path WHERE PathId=34212
Deleting: DELETE FROM Path WHERE PathId=34213


Now I got 75750 single delete statements - everyone taking about 0.5 Seconds for
execution

While this being really slow I found the SQL which select this:

SELECT DISTINCT Path.PathId,File.PathId 
FROM Path
LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
WHERE File.PathId IS NULL LIMIT 300000

which could be reformatted as
select DISTINCT Path.PathId FROM Path where not exists (select 1 from File where
Path.PathId=File.PathId)

and therefor be used as
delete FROM Path where not exists (select 1 from File where
Path.PathId=File.PathId)

I had same Problem on some other tables which I did manually

SELECT Filename.FilenameId,File.FilenameId 
FROM Filename 
LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) 
WHERE File.FilenameId IS NULL 

delete from Filename where not exists ( select 1 from File where
Filename.FilenameId=File.FilenameId);
====================================================================== 

---------------------------------------------------------------------- 
 (0006589) ebollengier (administrator) - 2013-01-17 08:15
 http://bugs.bacula.org/view.php?id=1974#c6589 
---------------------------------------------------------------------- 
Thanks for your help on Bacula project, however this tool is designed to handle
only Bugs, new features and enhancements should be discussed through the devel
and users mailing list. You can also search in the history of the devel list to
see why it's implemented like this you might find some useful hints.

If you want to have users to test your idea, I suggest to provide a patch.

Regards 

Issue History 
Date Modified    Username       Field                    Change               
====================================================================== 
2013-01-09 14:01 emundo         New Issue                                    
2013-01-17 08:15 ebollengier    Note Added: 0006589                          
2013-01-17 08:15 ebollengier    Status                   new => closed       
======================================================================