Error when listing jobs in bconsole: Fatal error: cats/sql_list.cc:593

[jens.gr...@gmail.com]

Hi guys,

I'm using Version: 23.0.1~pre7.606b211eb (19 December 2023)

The following command does not work after I upgraded my server to Debian 12 and the database from postgres 13 to 15:

*list jobid=36197 hours=24
You have messages.
*m
03-Jan 08:54 bareos-dir JobId 0: Fatal error: cats/sql_list.cc:593 cats/sql_list.cc:593 query SELECT DISTINCT Job.JobId,Job.Name, Client.Name as Client, Job.StartTime, CASE WHEN Job.endtime IS NOT NULL AND Job.endtime >= Job.starttime THEN Job.endtime - Job.starttime ELSE CURRENT_TIMESTAMP(0) - Job.starttime END as Duration, Job.Type,Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus FROM Job LEFT JOIN Client ON Client.ClientId=Job.ClientId LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId WHERE Job.JobId > 0 AND Job.JobId=36197AND Job.SchedTime > '2024-01-02 08:54:37'  ORDER BY StartTime;  failed:
FEHLER:  Müll folgt auf numerische Konstante bei »36197A«
ZEILE 1: ...d=Job.FileSetId WHERE Job.JobId > 0 AND Job.JobId=36197AND J...

Seems like the missing blank between the JobId and the `AND` in the SQL-command is causing the trouble. Running the command without `hours=24` works as expected:

*list jobid=36197
+-------+-------------------------------------------+------------------------------------+---------------------+----------+------+-------+----------+----------+-----------+
| jobid | name                                      | client                             | starttime           | duration | type | level | jobfiles | jobbytes | jobstatus |
+-------+-------------------------------------------+------------------------------------+---------------------+----------+------+-------+----------+----------+-----------+
| 36197 | backup-laptop-XXXX-fd | laptop-XXXX-fd | 2024-01-02 09:07:08 | 00:00:02 | C    | I     |        1 |       81 | T         |
+-------+-------------------------------------------+------------------------------------+---------------------+----------+------+-------+----------+----------+-----------+

Does anyone know what could be the reason for this?

btw: I'm using this command inside a python script to regularly backup remote clients.

Greetings, Jens

[Bruno Friedmann (bruno-at-bareos)]

Hi Jen,

I guess you already verified that you didn't forget to update the database schema and the privileges ?

either with dbconfig or manually by applying the manual script update_bareos_tables and grant_bareos_privileges

```

Enter SQL query: select * from version;
+-----------+
| versionid |
+-----------+
|     2,230 |
+-----------+

```

Otherwise I wonder what kind of output you are expecting with 

list jobid=9999 hours=24

Aren't you looking for `show me the jobs run in the last 24 jours`

which is 

list jobs hours=24 

For example here with a 23 version

Using your command give me the right result

*list jobid=14970 hours=24
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

| jobid | name    | client  | starttime           | duration | type | level | jobfiles | jobbytes    | jobstatus |

+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+
| 14970 | catalog | yoda-fd | 2024-01-05 07:02:01 | 00:00:06 | B    | F     |      272 | 770,112,797 | T         |
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

Show me the jobs for specific jobname during the last 48 hours.

*list jobs jobname=catalog hours=48
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

| jobid | name    | client  | starttime           | duration | type | level | jobfiles | jobbytes    | jobstatus |

+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+
| 14961 | catalog | yoda-fd | 2024-01-04 07:02:02 | 00:00:08 | B    | F     |      272 | 767,798,259 | T         |
| 14970 | catalog | yoda-fd | 2024-01-05 07:02:01 | 00:00:06 | B    | F     |      272 | 770,112,797 | T         |
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

While trying to ask for a specific jobid will always return non or one result.

*list jobid=14970 hours=48
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

| jobid | name    | client  | starttime           | duration | type | level | jobfiles | jobbytes    | jobstatus |

+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+
| 14970 | catalog | yoda-fd | 2024-01-05 07:02:01 | 00:00:06 | B    | F     |      272 | 770,112,797 | T         |
+-------+---------+---------+---------------------+----------+------+-------+----------+-------------+-----------+

Regards

[jens.gr...@gmail.com]

Hi Bruno,

thanks for your reply.

The db-scheme seems correct.

bareos@blabla:~$ psql -U bareos bareos
psql (15.5 (Debian 15.5-0+deb12u1))
Geben Sie »help« für Hilfe ein.

bareos=> \c
Sie sind jetzt verbunden mit der Datenbank »bareos« als Benutzer »bareos«.
bareos=> select * from version;
 versionid
-----------
      2230
(1 Zeile)

I've had some trouble when I upgraded from bareos v22 to v23 but that was some time ago.

The intention for the command `list jobid=36197 hours=24` is this: I want to check if the given job ran in the last 24 hours. If the result is not empty I don't need a backup, but if not I do need a new backup. I'm calling my scripts every 30 minutes to backup up remote clients incrementally once every 24 hours and fully once every 7 days.

But with your hints I think that I could do my check more correctly like this:

list jobs client=laptop-XXXX-fd hours=24

The script code is now a little clearer than it was before so that's already a benefit from our conversation.

But nevertheless, if I type in bconsole:

* list jobid=36197 hours=48

I get the error as mentioned above. With the solution the error doesn't bother me but it seems a little strange to me.

Greetings, Jens

[Bruno Friedmann (bruno-at-bareos)]

Hi Thanks for your report,

I've now being able to reproduce it in the master and 23 branch, we will certain get more information/fix from our beloved dev team soon.