View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001461bareos-coredirectorpublic2022-05-13 18:012022-05-16 10:11
Reporteriwi Assigned Tobruno-at-bareos  
PriorityurgentSeverityblockReproducibilityalways
Status closedResolutionnot fixable 
Product Version21.1.3 
Summary0001461: Postgres error DECLARE CURSOR can only be used in transaction blocks
DescriptionAfter an upgrade of Bareos to version 21, Ubuntu to version 20.04 and Postgresql to version 12 I'm getting the following error each time I'm starting a backup job :

Fatal error: error in jcr->db_batch->GetBaseFileList:Query failed: DECLARE _bac_cursor CURSOR FOR SELECT Path.Path, T1.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq , Fhinfo, Fhnode FROM ( SELECT DISTINCT ON (Name, PathId) JobTDate, JobId, FileId, FileIndex, PathId, FileName AS Name, LStat , DeltaSeq, Fhinfo, Fhnode FROM (SELECT FileId, JobId, PathId, Name AS FileName, FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM File WHERE JobId IN (128422,128509,128596,128683,128770) UNION ALL SELECT File.FileId, File.JobId, PathId, File.Name AS FileName, File.FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM BaseFiles JOIN File USING (FileId) WHERE BaseFiles.JobId IN (128422,128509,128596,128683,128770) ) AS T JOIN Job USING (JobId) ORDER BY Name, PathId, JobTDate DESC ) AS T1 JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC: ERR=ERROR: DECLARE CURSOR can only be used in transaction blocks

The backup job then fails.

The Bareos upgrade script completed without problems. I can access the Bareos web interface, I can see all the backup jobs, all clients, storages, ... so everything else seems to be ok after the upgrade.

Any ideas what's wrong with my system?
Steps To ReproduceStart a backup job.
TagsNo tags attached.

Activities

iwi

iwi

2022-05-13 18:14

reporter   ~0004623

OK, looks like just posting something here helps to resolve the problem ;-)

I turned logging of the postgres statements and got the following :

2022-05-13 17:52:25.100 CEST [62682] bareos@bareos LOG: statement: SET standard_conforming_strings=on
2022-05-13 17:52:25.100 CEST [62682] bareos@bareos LOG: statement: SELECT getdatabaseencoding()
2022-05-13 17:52:25.100 CEST [62682] bareos@bareos LOG: statement: SET client_encoding TO 'SQL_ASCII'
2022-05-13 17:52:25.100 CEST [62682] bareos@bareos LOG: statement: BEGIN
2022-05-13 17:52:25.100 CEST [62682] bareos@bareos LOG: statement: DECLARE _bac_cursor CURSOR FOR SELECT Path.Path, T1.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq , Fhinfo, Fhnode FROM ( SELECT DISTINCT ON (Name, PathId) JobTDate, JobId, FileId, FileIndex, PathId, FileName AS Name, LStat , DeltaSeq, Fhinfo, Fhnode FROM (SELECT FileId, JobId, PathId, Name AS FileName, FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM File WHERE JobId IN (128422,128509,128596,128683,128770) UNION ALL SELECT File.FileId, File.JobId, PathId, File.Name AS FileName, File.FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM BaseFiles JOIN File USING (FileId) WHERE BaseFiles.JobId IN (128422,128509,128596,128683,128770) ) AS T JOIN Job USING (JobId) ORDER BY Name, PathId, JobTDate DESC ) AS T1 JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC
2022-05-13 17:52:25.139 CEST [62682] bareos@bareos ERROR: permission denied for table file
2022-05-13 17:52:25.139 CEST [62682] bareos@bareos STATEMENT: DECLARE _bac_cursor CURSOR FOR SELECT Path.Path, T1.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq , Fhinfo, Fhnode FROM ( SELECT DISTINCT ON (Name, PathId) JobTDate, JobId, FileId, FileIndex, PathId, FileName AS Name, LStat , DeltaSeq, Fhinfo, Fhnode FROM (SELECT FileId, JobId, PathId, Name AS FileName, FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM File WHERE JobId IN (128422,128509,128596,128683,128770) UNION ALL SELECT File.FileId, File.JobId, PathId, File.Name AS FileName, File.FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM BaseFiles JOIN File USING (FileId) WHERE BaseFiles.JobId IN (128422,128509,128596,128683,128770) ) AS T JOIN Job USING (JobId) ORDER BY Name, PathId, JobTDate DESC ) AS T1 JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC
2022-05-13 17:52:25.145 CEST [62683] bareos@bareos LOG: statement: SET datestyle TO 'ISO, YMD'
2022-05-13 17:52:25.145 CEST [62683] bareos@bareos LOG: statement: SET cursor_tuple_fraction=1
2022-05-13 17:52:25.145 CEST [62683] bareos@bareos LOG: statement: SET standard_conforming_strings=on
2022-05-13 17:52:25.145 CEST [62683] bareos@bareos LOG: statement: DECLARE _bac_cursor CURSOR FOR SELECT Path.Path, T1.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq , Fhinfo, Fhnode FROM ( SELECT DISTINCT ON (Name, PathId) JobTDate, JobId, FileId, FileIndex, PathId, FileName AS Name, LStat , DeltaSeq, Fhinfo, Fhnode FROM (SELECT FileId, JobId, PathId, Name AS FileName, FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM File WHERE JobId IN (128422,128509,128596,128683,128770) UNION ALL SELECT File.FileId, File.JobId, PathId, File.Name AS FileName, File.FileIndex, LStat , DeltaSeq, Fhinfo, Fhnode FROM BaseFiles JOIN File USING (FileId) WHERE BaseFiles.JobId IN (128422,128509,128596,128683,128770) ) AS T JOIN Job USING (JobId) ORDER BY Name, PathId, JobTDate DESC ) AS T1 JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC
2022-05-13 17:52:25.147 CEST [62683] bareos@bareos ERROR: DECLARE CURSOR can only be used in transaction blocks

The important part is : permission denied for table file

I have no idea what this means but decided to re-run /usr/lib/bareos/scripts/grant_bareos_privileges

This "fixed" the CURSOR problem.

This bug report can be closed.
bruno-at-bareos

bruno-at-bareos

2022-05-16 10:11

manager   ~0004624

The grant_bareos_privileges has to be run after any update anyway. But honestly no clue about why the permission would have change between version.

Issue History

Date Modified Username Field Change
2022-05-13 18:01 iwi New Issue
2022-05-13 18:14 iwi Note Added: 0004623
2022-05-16 10:11 bruno-at-bareos Assigned To => bruno-at-bareos
2022-05-16 10:11 bruno-at-bareos Status new => closed
2022-05-16 10:11 bruno-at-bareos Resolution open => not fixable
2022-05-16 10:11 bruno-at-bareos Note Added: 0004624