#129 SQLSTATE[42000]: Syntax error or access violation: 1305

[Kocherin Alexey]

hello, please, how to solve this problem?

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 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 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-03-03 07:17:40') 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(289): Zend_Db_Select->query()
5 /usr/share/webacula/application/controllers/JobController.php(63): 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(249): Zend_Controller_Front->dispatch()
10 {main}

System Information:

DB Bacula : PDO_MYSQL 5.1.73
Version for Bacula Catalog database : 12
Director : bacula-dir Version: 5.0.0 (26 January 2010) x86_64-redhat-linux-gnu redhat
Bconsole : Version: 5.0.0 (26 January 2010) x86_64-redhat-linux-gnu redhat
Webacula version : 5.5.1, build 2011.09.07
Zend Framework : 1.12.9
PHP : 5.3.3
id : uid=48(apache) gid=48(apache) groups=48(apache),133(bacula)

[Vitaalz]

Hi there,

After spending some time in forums, I realized that the problem is the ZendFramework version (in our case 1.12.9). Some people suggested that version 1.12.7 works fine. So I did some experiments and found out that the problem is caused specifically by /usr/share/php5/Zend/Db/Select.php file. Comparing it to the one from 1.12.7, it has a few differences in regular expressions.

So in order to solve the issue with the displaying running jobs you can replace this file from the earlier Zend version or replace following three lines in it:

replace line 512:
if (preg_match('/^[\w]([^)])$/', (string) $val)) {
with:

 if (preg_match('/\(.*\)/', (string) $val)) {

line 604:
if (preg_match('/^[\w]([^)])$/', $val)) {

 if (preg_match('/^[\w]*\(.*\)$/', $val)) {

and line 946:
if (preg_match('/^[\w]([^)])$/', $col)) {

  if (preg_match('/\(.*\)/', $col)) {

This worked for me. Hope that helps you too.

Good luck!

[Pt_Djefferson]

duplicate #127. That`s not Zend error, just need to use new Zend_Db_Expr() around expressions.
Patch for MySQL and PgSQL: