Re: [Bacula-users] Need some help with copy jobs

Hi,

Thanks for you input.

Unfortunately, I am no SQL expert, and I get the following when trying to run your selection query against my Bacula MySQL DB:

select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY a.StartTime' at line 7

Any ideas?

Thanks for your help.

Joe

On 15 Jan 2012, at 17:28, Adrian Reyer wrote:

On Sat, Jan 14, 2012 at 11:46:21PM +0000, Joe Nyland wrote:
I have a copy job as follows:
Selection Type = SQLQuery
Selection Pattern = "SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool
WHERE Pool.Name = 'SVN_Full'
AND Pool.PoolId = Job.PoolId
AND Job.Type = 'B'
AND Job.JobStatus = 'T'
AND Job.JobId
NOT IN (SELECT PriorJobId FROM Job WHERE TYPE = 'C' AND Job.JobStatus = 'T' AND PriorJobId != 0) ORDER BY Job.StartTime;"

I do something similar and it works for me. However, in my special case
I do additional messin with the pools. Actually I backup to disk, then
copy it to one tape TAPE and one tape ARCH, this is only for the 'ARCH'
job. The 'ARCH' is actually just an offsite version and in no way an
archive, only backups within the last 10 days are considered, otherwise
you start and copy old expired purged jobs again ater you deleted them
from the db.
-> INTERVAL... should be less than your retention time if you delete
  purged jobs now and then
As my statement looks pretty much the same, I'd check the 'NOT IN'-part,
especially the 'JobStatus'.

Selection Pattern = "select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
   (SELECT PriorJobId FROM Job AS c JOIN Pool AS d
    ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;"

Postgres syntax here, but I *think* I had the very same statement a few
months ago when I started with MySQL.

Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: lihas AT lihas DOT de - Web: http://lihas.de
Linux, Netzwerke, Consulting & Support - USt-ID: DE 227 816 626 Stuttgart

------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users