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: li...@lihas.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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
