Fixing Bacula Database Manually

So, yesterday I decided to update my server. I had backed up things – not just daily backups but extra back ups. The main problem was the down time as I timed it wrong – I actually started the operation shortly before I had to leave for the day. And although I did get it back up, I did not get certain things up, web server included. But, what I did not anticipate, is having to fix the backup database. What happened and how I fixed it, is below.

Firstly, I use Bacula for my backup purposes. Pretty nice program. However, not only did I have to update the version, I had another problem with the database: a new field in one of the table ‘Job’. This prevented any data restoring from happening with an ugly error. So, firstly, to update the version I did the following (ignoring any warnings) :

 

/usr/libexec/bacula/update_mysql_tables

This allowed me to use the program ‘bconsole’ – which is, upon restore command, gave me the following error:

22-Jul 07:28 bacula-dir JobId 0: Fatal error: sql_get.c:311 sql_get.c:311 query SELECT VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase FROM Job WHERE JobId=106 failed:
Unknown column ‘ReadBytes’ in ‘field list’

I definitely had a problem. Not one I had foreseen. However, I thought of a fix. Firstly, since I had the backup of the mysql databases anyway, I’d drop the bacula database, create a new bacula database to find the proper structure. Then reimport the old database and alter the Job table to be the same. Would it work? Based on the error, I was missing a column called ReadBytes. So, after making a clean database, I discovered it should come after JobBytes column in the Job table. So, I did the following:

alter table Job add column ReadBytes bigint after JobBytes;

And then the restore worked fine. Sure, you could say this should not be necessary. But it was a major OS update and so I’m not surprised some things came up. And perhaps if I let it backup the database in addition, it wouldn’t have been a problem. I don’t know, and I don’t really mind, either. I backup mysql databases anyway, so I didn’t feel the need to let it do an additional backup. And since simply adding the field fixed it, I see no harm. It also brings to mind that no matter what you anticipate, other things can go wrong – or right. It doesn’t really matter what they are. What matters is how you approach them and how you resolve them. In my case, a bit of thinking and having been mostly prepared, allowed me to fix the problem.