#127 running jobs page error

[Alexander]

webacula/job/running

An error occurred

Message :

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION j.TIMEDIFF does not exist, query was: SELECT DISTINCT j.JobId, j.Name AS JobName, j.Level, j.ClientId, j.StartTime, j.EndTime, j.VolSessionId, j.VolSessionTime, j.JobFiles, j.JobBytes, j.JobErrors, j.Reviewed, j.PoolId, j.FileSetId, j.PurgedFiles, j.JobStatus, j.TIMEDIFF(NOW(), StartTime) AS DurationTime, s.JobStatusLong, c.Name AS ClientName, p.Name AS PoolName, sd.* FROM Job AS j LEFT JOIN Status AS s ON j.JobStatus = s.JobStatus LEFT JOIN Client AS c ON j.ClientId = c.ClientId LEFT JOIN Pool AS p ON j.PoolId = p.PoolId LEFT JOIN webacula_jobdesc AS sd ON j.Name = sd.name_job WHERE ((j.EndTime = 0) OR (j.EndTime IS NULL) OR (j.JobStatus IN ('C','R','B','e','F','S','m','M','s','j','c','d','t','p','i','a','l','L'))) AND (j.StartTime > '2015-01-10 22:40:50') ORDER BY StartTime ASC, JobId ASC

Trace:

0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)

1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)

2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)

3 /usr/share/php/Zend/Db/Select.php(686): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))

4 /usr/share/webacula/application/models/Job.php(311): Zend_Db_Select->query()

5 /usr/share/webacula/application/controllers/JobController.php(64): Job->getRunningJobs()

6 /usr/share/php/Zend/Controller/Action.php(516): JobController->runningAction()

7 /usr/share/php/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('runningAction')

8 /usr/share/php/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))

9 /usr/share/webacula/html/index.php(258): Zend_Controller_Front->dispatch()

10 {main}

System Information:

DB Bacula : PDO_MYSQL 5.5.40
Version for Bacula Catalog database : 14
Director : vpsbackup-dir Version: 5.2.6 (21 February 2012) x86_64-pc-linux-gnu debian jessie/sid
Bconsole : Version: 5.2.6 (21 February 2012) x86_64-pc-linux-gnu debian jessie/sid
Webacula version : 7.0.0, build 2014.10.05
Zend Framework : 1.12.9
PHP : 5.6.4-4
id : uid=33(www-data) gid=33(www-data) groups=33(www-data),120(bacula)

[Robert Oschwald]

Similiar error when in webacula 7.0.0 when running on PostgreSQL 9.3.
The "running" query defined in Job.php line 275 produces an error:

An error occurred

Message :

SQLSTATE[42601]: Syntax error: 7 ERROR: Syntaxerror at 
„(“ LINE 1: ...oolId, j.FileSetId, j.PurgedFiles, j.JobStatus, j.(NOW() - S... ^, 

query was: SELECT DISTINCT j.JobId, j.Name AS JobName, j.Level, j.ClientId, j.StartTime, j.EndTime, j.VolSessionId, j.VolSessionTime, j.JobFiles, j.JobBytes, j.JobErrors, j.Reviewed, j.PoolId, j.FileSetId, j.PurgedFiles, j.JobStatus, j.(NOW() - StartTime) AS DurationTime, s.JobStatusLong, c.Name AS ClientName, p.Name AS PoolName, sd.* FROM Job AS j LEFT JOIN Status AS s ON j.JobStatus = s.JobStatus LEFT JOIN Client AS c ON j.ClientId = c.ClientId LEFT JOIN Pool AS p ON j.PoolId = p.PoolId LEFT JOIN webacula_jobdesc AS sd ON j.Name = sd.name_job WHERE ((j.EndTime IS NULL) OR (j.JobStatus IN ('C','R','B','e','F','S','m','M','s','j','c','d','t','p','i','a','l','L'))) AND (j.StartTime > ( NOW() - INTERVAL '7 days' )) ORDER BY StartTime ASC, JobId ASC

Trace:

#0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/php/Zend/Db/Select.php(686): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#4 /usr/share/webacula/application/models/Job.php(311): Zend_Db_Select->query()
#5 /usr/share/webacula/application/controllers/JobController.php(64): Job->getRunningJobs()
#6 /usr/share/php/Zend/Controller/Action.php(516): JobController->runningAction()
#7 /usr/share/php/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('runningAction')
#8 /usr/share/php/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#9 /usr/share/webacula/html/index.php(258): Zend_Controller_Front->dispatch()
#10 {main}
System Information:

DB Bacula : PDO_PGSQL 9.3.0
Version for Bacula Catalog database : 14
Director : bacula-dir Version: 7.0.4 (04 June 2014) x86_64-redhat-linux-gnu redhat
Bconsole : Version: 7.0.4 (04 June 2014) x86_64-redhat-linux-gnu redhat
Webacula version : 7.0.0, build 2014.10.05
Zend Framework : 1.12.9
PHP : 5.3.3

Reason:

j.(NOW() - StartTime) AS DurationTime

does not work.

if written as "(NOW() - StartTime) as DurationTime"

it works when executed on the db:

SELECT DISTINCT j.JobId, j.Name AS JobName, j.Level, j.ClientId, j.StartTime, j.EndTime, j.VolSessionId, j.VolSessionTime, j.JobFiles, j.JobBytes, j.JobErrors, j.Reviewed, j.PoolId, j.FileSetId, j.PurgedFiles, j.JobStatus, 
        (NOW() - StartTime) AS DurationTime, 
        s.JobStatusLong, c.Name AS ClientName, p.Name AS PoolName, sd.* 
        FROM Job AS j LEFT JOIN Status AS s 
        ON j.JobStatus = s.JobStatus 
        LEFT JOIN Client AS c ON j.ClientId = c.ClientId 
        LEFT JOIN Pool AS p ON j.PoolId = p.PoolId 
        LEFT JOIN webacula_jobdesc AS sd ON j.Name = sd.name_job 
        WHERE ((j.EndTime IS NULL) OR (j.JobStatus IN ('C','R','B','e','F','S','m','M','s','j','c','d','t','p','i','a','l','L'))) 
    AND (j.StartTime > ( NOW() - INTERVAL '7 days' )) 
    ORDER BY StartTime ASC, JobId ASC

Result:

26671;"CopyDiskToTape";"F";4;"2015-02-05 12:46:31";"";0;0;0;0;0;0;7;9;0;"R";"00:50:26.544041";"Running";"None";"Tape";;"";"";"";""
26672;"server-job";"F";5;"2015-02-05 12:46:31";"";0;0;0;0;0;0;7;25;0;"R";"00:50:26.544041";"Running";"server-fd";"Tape";;"";"";"";""

How to rebuild the query in Job.php to omit "j." I don't know, because I'm not into PHP.

[Robert Oschwald]

Patch to workaround the bug on PostgreSQL:

--- Job.php.org 2015-02-05 13:29:50.461021729 +0100
+++ Job.php 2015-02-05 14:03:14.242386116 +0100
@@ -272,7 +272,8 @@
              'StartTime' => "j.StartTime", 'EndTime'   => "j.EndTime",
              'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId',
               'FileSetId', 'PurgedFiles', 'JobStatus',

-              'DurationTime' => '(NOW() - StartTime)'
+                   // remarked as workaround for reported bug https://sourceforge.net/p/webacula/bugs/127/
+              //'DurationTime' => '(NOW() - StartTime)'
            ));
            $select->joinLeft(array('s' => 'Status'), 'j.JobStatus = s.JobStatus', array('JobStatusLong' => 'JobStatusLong'));
            $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));

[Pt_Djefferson]

Patch for MySQL and mostly for PgSQL:

[Robert Oschwald]

Patch works with PostgreSQL 8.1 on CentOS6. Thanks.