mysql error on restore of bareos catalog dump

[Kai Zimmer]

Hi,

one of my disks crashed and now i need to recover a mysqldump of the
bareos table (catalog). When i try that i get the following error:

ERROR 1071 (42000) at line 115331: Specified key was too long; max key
length is 255 bytes


It looks like this is the result of an update to mysql 5.7 (see also
https://bugs.bareos.org/view.php?id=705)

My system config:

Ubuntu 16.04

bareos 15.2.2-37.1

The dumpfile is about 112 GB (!) in size. It is a secondary catalog,
i.e. i have two catalogs integrated in my bareos-dir.conf file, my
current catalog works with postgresql. But i still need to have the old
mysql catalog at hand.

I see that this bug has been fixed in bareos 17.2.4. But how do i
recover existing mysqldumps from older bareos/mysql versions? Is there a
script available to fix the dump file? Or should i try to recover the
dump in an older version of mysql first?

Best,

Kai

[Steffen Knauf]

Hi Kai,

i migrate from bacula to bareos with an update from mysql 5.1 to 5.7
(140 GB mysql dump).

I decided to import the dump into the original (old) mysql Version and
then upgrade to the new mysql version (step by step). I'm not sure this
is the best solution, but it works for me. I hope i don't forget one
step:

1) create database bareos
2) import dump
3) Change bareos update scripts (/usr/lib/bareos/scripts/ddl/updates/)

Change from 256 to 255:

PRIMARY KEY (`ClientId`,`FileSetId`,`FileSystem`(255))
PRIMARY KEY (`JobId`,`FileIndex`,`EnvName`(255))

4) /usr/lib/bareos/scripts/./update_bareos_tables (it takes a loooong
time)

5) /usr/lib/bareos/scripts/./grant_bareos_privileges

6) mysql update (with mysqlupgrade). Perhaps you need a mysqlcheck
--repair after that

7) Change defaults:

ALTER TABLE Job ALTER SchedTime set default NULL;
ALTER TABLE Job ALTER StartTime set default NULL;
ALTER TABLE Job ALTER EndTime set default NULL;
ALTER TABLE Job ALTER RealEndTime set default NULL;
ALTER TABLE Device ALTER CleaningDate set default NULL;
ALTER TABLE JobHisto ALTER SchedTime set default NULL;
ALTER TABLE JobHisto ALTER StartTime set default NULL;
ALTER TABLE JobHisto ALTER EndTime set default NULL;
ALTER TABLE JobHisto ALTER RealEndTime set default NULL;
ALTER TABLE LocationLog ALTER Date set default NULL;
ALTER TABLE FileSet ALTER CreateTime set default NULL;
ALTER TABLE Media ALTER FirstWritten set default NULL;
ALTER TABLE Media ALTER LastWritten set default NULL;
ALTER TABLE Media ALTER LabelDate set default NULL;
ALTER TABLE Media ALTER InitialWrite set default NULL;
ALTER TABLE Log ALTER Time set default NULL;


greets

Steffen

[Kai Zimmer]

Hi Steffen,

thanks for your comprehensive answer. I'll try it - beginning with mysql
5.5.

Best regards,
Kai

Am 27.06.19 um 14:19 schrieb Steffen Knauf: