View Issue Details

IDProjectCategoryView StatusLast Update
0000083Bacula-Webbugpublic2014-11-17 17:13
ReporterccsproAssigned Todavide 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version5.2.13 
Target Version7.0.0Fixed in Version7.0.0 
Summary0000083: Bacula-web dashboard queries to *sql does not take into account timezone differences.
DescriptionBacula-web dashboard queries to *sql does not take into account timezone differences when compiling reports.

Meaning you could end up waiting 3 hours or so for time zone differences :P


Steps To ReproduceVery easy to reproduce.

Example:
Bacula-web server - pacific time
Bacula-server1 - Eastern
Bacula-server2 - Pacific
Bacula-server3 - Mountain
Bacula-server4 - Central

run jobs on each bacula-server*
refresh bacula-web every minute or so (depending on what you are doing)
You will notice "running jobs" seem to show up fine but jobs in other statuses do not.
Additional InformationIts the simple query like this that does it (bump it X hours ahead and works):
SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-06 14:33:14' AND '2013-01-07 16:33:14') AND JobStatus = 'T';
TagsNo tags attached.

Activities

ccspro

2013-01-07 23:48

reporter   ~0000196

I'm using mysql.

ChC

2013-01-08 15:28

reporter   ~0000198

How are the different dates stored in Bacula catalog?

If there is no TimeZone information available in the catalog then Bacula-Web will not be able to fix any of this.

In MS-SQL a date is stored like '2013-01-08T15:22:25Z+1' to show that an offset of +1 is added. I am not familiar how this is stored in MySQL.
If in MySQL only '2013-01-08T15:22:25' is stored, then the client has to make sure it inserts normalized values

ccspro

2013-01-08 19:49

reporter   ~0000199

This is the Bacula catalog for the Job table in MySQL:
mysql> describe Job;
+-----------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------------------+----------------+
| JobId | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Job | tinyblob | NO | | NULL | |
| Name | tinyblob | NO | MUL | NULL | |
| Type | binary(1) | NO | | NULL | |
| Level | binary(1) | NO | | NULL | |
| ClientId | int(11) | YES | MUL | 0 | |
| JobStatus | binary(1) | NO | | NULL | |
| SchedTime | datetime | YES | | 0000-00-00 00:00:00 | |
| StartTime | datetime | YES | | 0000-00-00 00:00:00 | |
| EndTime | datetime | YES | | 0000-00-00 00:00:00 | |
| RealEndTime | datetime | YES | | 0000-00-00 00:00:00 | |
| JobTDate | bigint(20) unsigned | YES | | 0 | |
| VolSessionId | int(10) unsigned | YES | | 0 | |
| VolSessionTime | int(10) unsigned | YES | | 0 | |
| JobFiles | int(10) unsigned | YES | | 0 | |
| JobBytes | bigint(20) unsigned | YES | | 0 | |
| ReadBytes | bigint(20) unsigned | YES | | 0 | |
| JobErrors | int(10) unsigned | YES | | 0 | |
| JobMissingFiles | int(10) unsigned | YES | | 0 | |
| PoolId | int(10) unsigned | YES | | 0 | |
| FileSetId | int(10) unsigned | YES | MUL | 0 | |
| PriorJobId | int(10) unsigned | YES | | 0 | |
| PurgedFiles | tinyint(4) | YES | | 0 | |
| HasBase | tinyint(4) | YES | | 0 | |
| HasCache | tinyint(4) | YES | | 0 | |
| Reviewed | tinyint(4) | YES | | 0 | |
| Comment | blob | YES | | NULL | |
+-----------------+---------------------+------+-----+---------------------+----------------+
27 rows in set (0.00 sec)

The issue happens where the bacula-web server having a time zone that is less than the highest time zone available. When it selects the 24 hour period those upper hours are not included.

A simple solution would be to honor the time of the server where the catalog is stored by using a "select now() as CurrentDateTime;" query
This would need to be done for each catalog that is polled.
(let the DB decide what time it is now, not the bacula-web server)

We have 'client servers' all over different time zones and bacula servers (directors, storage systems) in different time zones. When the director is issuing a backup from the client server it uses the bacula director servers time zone to store the now() time in the database.

Here is a tcpdump output of where bacula-web is asking the catalog server for time/time between to count the amounts for the dashboard, note the time on the catalog server is 12:53:20 and last endtime is showing as 9:53:20 (I just performed a poll from bacula-web right now):

SELECT SUM(JobFiles) AS stored_files FROM Job WHERE ( EndTime BETWEEN '1970-01-01 00:00:00' AND '2013-01-08 09:53:20' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '1970-01-01 00:00:00' AND '2013-01-08 09:53:20' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20' )

SELECT SUM(JobFiles) AS stored_files FROM Job WHERE ( EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20' )

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus = 'T'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus IN ('f','E')

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus = 'A'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND Level = 'I'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND Level = 'D'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND Level = 'F'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus = 'T'

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus IN ('f','E')

SELECT COUNT(*) AS job_nb FROM Job WHERE (EndTime BETWEEN '2013-01-07 09:53:20' AND '2013-01-08 09:53:20') AND JobStatus = 'A'

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-01 00:00:00' AND '2013-01-01 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-02 00:00:00' AND '2013-01-02 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-03 00:00:00' AND '2013-01-03 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-04 00:00:00' AND '2013-01-04 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-05 00:00:00' AND '2013-01-05 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-06 00:00:00' AND '2013-01-06 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-07 00:00:00' AND '2013-01-07 23:59:59' )

SELECT SUM(JobBytes) as stored_bytes FROM Job WHERE ( EndTime BETWEEN '2013-01-08 00:00:00' AND '2013-01-08 23:59:59' )

Hope this helps, now I wish someone would take a look at the other 2 visual issues I've reported :P

ccspro

2013-01-08 23:52

reporter   ~0000200

Work around (for pgsql/mysql) for now is:
modify bacula-web/core/bweb.inc.php

search for $end_date = date( "Y-m-d H:i:s", $end_timestamp);
comment that line out and use this in its place (for now)
if ($end_timestamp=='NOW') {$end_date='now()';} else {$end_date="'".date( "Y-m-d H:i:s", $end_timestamp)."'";}

remove the single quotes from $end_date for the where_conditions:
mysql:
$where_conditions[] = "(EndTime BETWEEN '$start_date' AND '$end_date' )";
is now:
$where_conditions[] = "(EndTime BETWEEN '$start_date' AND $end_date )";

pgsql: (have not test this, but the pgsql docs seem to indicate this will work)
$where_conditions[] = "(EndTime BETWEEN timestamp '$start_date' AND timestamp '$end_date' )";
is now:
$where_conditions[] = "(EndTime BETWEEN timestamp '$start_date' AND timestamp $end_date )";

for sqllite now() will be something like datetime('now')
however this is just *my* temporary work around.

Once this is done modify bacula-web/index.php and change any caller that uses NOW for time calling the function object countJobs to use 'NOW' instead (change it to a string instead of NOW global constant)

You also may need to do the same modifications to getStoredFiles/getStoredBytes function object as you did countJobs

davide

2013-01-09 12:15

manager   ~0000201

This bug looks to be pretty much simple to fix.
I've found a solution on how to do this, and try to make a "bug fix" in a next version.

Regards

davide

2014-11-03 10:25

manager   ~0000406

Hello all,

Sorry to come back late on this issue.

Those any of you would be ready to test a fixed version of Bacula-Web ?

Sorry again

Best regards

davide

2014-11-17 17:13

manager   ~0000410

Fixed in release 7.0.0

From now, the code take current time the from database server instead of the server where Bacula-Web is running

Issue History

Date Modified Username Field Change
2013-01-07 23:47 ccspro New Issue
2013-01-07 23:48 ccspro Note Added: 0000196
2013-01-08 15:28 ChC Note Added: 0000198
2013-01-08 19:49 ccspro Note Added: 0000199
2013-01-08 23:52 ccspro Note Added: 0000200
2013-01-09 12:15 davide Note Added: 0000201
2013-10-15 21:24 davide Assigned To => davide
2013-10-15 21:24 davide Status new => assigned
2013-10-15 21:24 davide Product Version => 5.2.13
2014-07-30 14:47 davide Target Version => 7.0.0
2014-11-03 10:25 davide Note Added: 0000406
2014-11-03 10:25 davide Status assigned => feedback
2014-11-17 17:13 davide Note Added: 0000410
2014-11-17 17:13 davide Status feedback => resolved
2014-11-17 17:13 davide Fixed in Version => 7.0.0
2014-11-17 17:13 davide Resolution open => fixed