[Bacula-bugs] [bacula 0000066]: Error in SQL query when restoring file based on path/filename

[<no...@bu...>]

The following bug has been RESOLVED.
======================================================================
http://bugs.bacula.org/bug_view_advanced_page.php?bug_id=0000066
======================================================================
Reported By:                jpasher
Assigned To:                
======================================================================
Project:                    bacula
Bug ID:                     66
Category:                   Director
Reproducibility:            always
Severity:                   major
Priority:                   normal
Status:                     resolved
Resolution:                 fixed
======================================================================
Date Submitted:             15-07-2004 11:23 PDT
Last Modified:              15-07-2004 11:49 PDT
======================================================================
Summary:                    Error in SQL query when restoring file based on path/filename
Description: 
When restoring a file based on path and filename, the incorrect Job number
will sometimes be selected (which leads to restoring the incorrect file).
The SQL query that performs this in the file src/dird/sql_cmds.c does not
correctly filter out the table joins. Here is the query:

char *uar_jobid_fileindex =
   "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
   "WHERE Job.JobId=File.JobId "
   "AND Job.StartTime<'%s' "
   "AND Path.Path='%s' "
   "AND Filename.Name='%s' "
   "AND Client.Name='%s' "
   "AND Path.PathId=File.PathId "
   "AND Filename.FilenameId=File.FilenameId "
   "ORDER BY Job.StartTime DESC LIMIT 1";

The join between the Job table and Client is not filtered in any way when
it should be filtered on the ClientId column shared between the two. It
should be as follows:

char *uar_jobid_fileindex =
   "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
   "WHERE Job.JobId=File.JobId "
   "AND Job.StartTime<'%s' "
   "AND Path.Path='%s' "
   "AND Filename.Name='%s' "
   "AND Client.Name='%s' "
   "AND Job.ClientId=Client.ClientId "
   "AND Path.PathId=File.PathId "
   "AND Filename.FilenameId=File.FilenameId "
   "ORDER BY Job.StartTime DESC LIMIT 1";

I belive the only case where you would possibly notice this is when you
are backing up two servers that have the same file (ie, /etc/lilo.conf).
The first server is backed up, then the second server is backed up. If you
try to restore the file on the first server, the query will erroneously
select the Job for the SECOND server, since it finds the file there too,
and limits the query to the most recent entry (based on Job.StartTime)
======================================================================

----------------------------------------------------------------------
 kern - 15-07-2004 11:49 PDT 
----------------------------------------------------------------------
Nice call. You are absolutely right. It just goes to show (at least me) how
tricky SQL can be.

I've attached a patch containing your fix for version 1.34.5 to this bug
report. It is now also officially fixed in version 1.35.1

Bug History
Date Modified  Username       Field                    Change              
======================================================================
15-07-04 11:23 jpasher        New Bug                                      
15-07-04 11:46 kern           File Added: restore_file.patch                    
15-07-04 11:49 kern           Bugnote Added: 0000127                       
15-07-04 11:49 kern           Resolution               open => fixed       
15-07-04 11:49 kern           Status                   new => resolved     
======================================================================