1#!/bin/sh 2# 3# Copyright (C) 2000-2020 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# 6# Shell script to update MySQL tables from Bacula Enterprise version from 4.0.x to 12.4.x 7# 8echo " " 9echo "This script will update a Bacula MySQL database" 10echo " from any from version 12-15 or 1014-1021 to version 1022" 11echo " which is needed to convert from any Bacula Enterprise" 12echo " version 4.0.x or later to version 12.4.x" 13echo " or Bacula Community versions 5.0.x, 5.2.x, 7.4.x, 9.x.x to" 14echo " Community version 11.0" 15echo " " 16echo "Depending on the current version of your catalog, you may" 17echo " have to run this script multiple times" 18echo " " 19bindir=@MYSQL_BINDIR@ 20PATH="$bindir:$PATH" 21db_name=@db_name@ 22 23# Special trick to not do the upgrade from 1015 to 1016 with this script 24if [ "$1" = "--stop1015" ]; then 25 STOP1015=yes 26 shift # remove this option from $* 27fi 28 29ARGS=$* 30 31getVersion() 32{ 33 mysql $ARGS -D ${db_name} -e "select VersionId from Version LIMIT 1\G" >/tmp/$$ 34 DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$` 35} 36 37getVersion 38 39if [ "x$DBVERSION" = x ]; then 40 echo " " 41 echo "Unable to detect database version, you can specify connection information" 42 echo "on the command line." 43 echo "Error. Cannot upgrade this database." 44 echo " " 45 exit 1 46fi 47 48if [ "$DBVERSION" -lt 1014 -o "$DBVERSION" -gt 1021 ] ; then 49 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 16 ] ; then 50 echo " " 51 echo "The existing database is version $DBVERSION !!" 52 echo "This script can only update an existing version 12-16 or 1014-1021 database to version 1022." 53 echo "Error. Cannot upgrade this database." 54 echo " " 55 exit 1 56 fi 57fi 58 59# For all versions, we need to create the Index on Media(StorageId) 60# It may fail, but it's not a big problem 61# mysql $* -f <<END-OF-DATA >/dev/null 2> /dev/null 62# CREATE INDEX media_storageid_idx ON Media (StorageId); 63# END-OF-DATA 64 65if [ "$DBVERSION" -eq 12 ] ; then 66 if mysql $* -f <<END-OF-DATA 67USE ${db_name}; 68 69CREATE TABLE RestoreObject ( 70 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 71 ObjectName BLOB NOT NULL, 72 RestoreObject LONGBLOB NOT NULL, 73 PluginName TINYBLOB NOT NULL, 74 ObjectLength INTEGER DEFAULT 0, 75 ObjectFullLength INTEGER DEFAULT 0, 76 ObjectIndex INTEGER DEFAULT 0, 77 ObjectType INTEGER DEFAULT 0, 78 FileIndex INTEGER UNSIGNED DEFAULT 0, 79 JobId INTEGER UNSIGNED NOT NULL, 80 ObjectCompression INTEGER DEFAULT 0, 81 PRIMARY KEY(RestoreObjectId), 82 INDEX (JobId) 83); 84 85CREATE INDEX jobhisto_jobid_idx ON JobHisto (JobId); 86UPDATE Version SET VersionId=13; 87 88END-OF-DATA 89 then 90 echo "Update of Bacula MySQL tables from 12 to 13 succeeded." 91 getVersion 92 else 93 echo "Update of Bacula MySQL tables from 12 to 13 failed." 94 exit 1 95 fi 96fi 97 98if [ "$DBVERSION" -eq 13 ] ; then 99 if mysql $* -f <<END-OF-DATA 100USE ${db_name}; 101 102ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0; 103UPDATE Version SET VersionId=14; 104 105END-OF-DATA 106 then 107 echo "Update of Bacula MySQL tables from 13 to 14 succeeded." 108 getVersion 109 else 110 echo "Update of Bacula MySQL tables from 13 to 14 failed." 111 exit 1 112 fi 113fi 114 115if [ "$DBVERSION" -eq 14 ] ; then 116 if mysql $* -f <<END-OF-DATA 117USE ${db_name}; 118 119INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 120 ('I', 'Incomplete Job',25); 121UPDATE Version SET VersionId=1014; 122END-OF-DATA 123 then 124 echo "Update of Bacula MySQL tables from 14 to 1014 succeeded." 125 getVersion 126 else 127 echo "Update of Bacula MySQL tables from 14 to 1014 failed." 128 exit 1 129 fi 130fi 131 132if [ "$DBVERSION" -eq 1014 ] ; then 133 if mysql $* -f <<END-OF-DATA 134USE ${db_name}; 135 136ALTER TABLE Media 137 ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0, 138 ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0, 139 ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0, 140 ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0; 141 142UPDATE Version SET VersionId=1015; 143END-OF-DATA 144 then 145 echo "Update of Bacula MySQL tables from 1014 to 1015 succeeded." 146 getVersion 147 else 148 echo "Update of Bacula MySQL tables from 1014 to 1015 failed." 149 exit 1 150 fi 151fi 152 153if [ "$DBVERSION" -eq 15 -o "$DBVERSION" -eq 16 ]; then 154 # In version 16, the upgrade 1017-1018 is already done 155 if [ "$DBVERSION" -eq 16 ]; then 156 SKIP1018=1 157 fi 158 159# If running on a Galera cluster, YOU MUST BRING THE CLUSTER DOWN TO A 160# SINGLE NODE before performing this migration (and preferably also 161# disable Galera temporarily) because the schema rewrites will almost 162# certainly exceed the hard limits on Galera writeset size (128K maximum 163# rows or 4GB total writeset size, whichever comes first). 164 165 if mysql --skip-reconnect $* <<END-OF-DATA 166USE ${db_name}; 167 168-- Revert the FileIndex if needed 169ALTER TABLE RestoreObject 170 MODIFY FileIndex INTEGER UNSIGNED DEFAULT 0; 171ALTER TABLE BaseFiles 172 MODIFY FileIndex INTEGER UNSIGNED DEFAULT 0; 173 174-- If you re-execute the procedure, you can comment out 175-- these two lines 176DROP INDEX JobId on File; 177DROP INDEX JobId_2 on File; 178 179DROP TABLE IF EXISTS file_temp; 180 181CREATE TABLE file_temp ( 182 FileId BIGINT UNSIGNED NOT NULL, 183 FileIndex INTEGER DEFAULT 0, 184 JobId INTEGER UNSIGNED NOT NULL, 185 PathId INTEGER UNSIGNED NOT NULL, 186 Filename BLOB NOT NULL, 187 DeltaSeq SMALLINT UNSIGNED DEFAULT 0, 188 MarkId INTEGER UNSIGNED DEFAULT 0, 189 LStat TINYBLOB NOT NULL, 190 MD5 TINYBLOB 191 ); 192 193INSERT INTO file_temp (FileId, FileIndex, JobId, PathId, Filename, DeltaSeq, 194 MarkId, LStat, Md5) 195 SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, 196 MarkId, LStat, Md5 197 FROM File JOIN Filename USING (FilenameId); 198 199SELECT 'DO NOT BREAK THE SCRIPT AFTER THIS POINT'; 200 201DROP TABLE Filename; 202DROP TABLE File; 203ALTER TABLE file_temp RENAME TO File; 204 205ALTER TABLE File 206 ADD PRIMARY KEY (FileId), 207 MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 208 ADD INDEX JobId (JobId), 209 ADD INDEX JobId_2 (JobId, PathId, Filename(255)); 210 211ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED DEFAULT 0; 212ALTER TABLE UnsavedFiles 213 DROP COLUMN FilenameId, 214 ADD COLUMN Filename BLOB NOT NULL; 215 216UPDATE Version SET VersionId = 1017; 217 218END-OF-DATA 219 then 220 echo "Update of Bacula MySQL tables from $DBVERSION to 1017 succeeded." 221 getVersion 222 else 223 echo "Update of Bacula MySQL tables from $DBVERSION to 1017 failed." 224 exit 1 225 fi 226fi 227 228if [ "$STOP1015" = "" -a "$DBVERSION" -eq 1015 ]; then 229 if mysql --skip-reconnect $* <<END-OF-DATA 230USE ${db_name}; 231 232DROP INDEX IF EXISTS JobId on File; 233DROP INDEX IF EXISTS JobId_2 on File; 234 235CREATE TABLE IF NOT EXISTS file_temp ( 236 FileId BIGINT UNSIGNED NOT NULL, 237 FileIndex INTEGER DEFAULT 0, 238 JobId INTEGER UNSIGNED NOT NULL, 239 PathId INTEGER UNSIGNED NOT NULL, 240 Filename BLOB NOT NULL, 241 DeltaSeq SMALLINT UNSIGNED DEFAULT 0, 242 MarkId INTEGER UNSIGNED DEFAULT 0, 243 LStat TINYBLOB NOT NULL, 244 MD5 TINYBLOB 245 ); 246 247TRUNCATE TABLE file_temp; 248 249INSERT INTO file_temp (FileId, FileIndex, JobId, PathId, Filename, DeltaSeq, 250 MarkId, LStat, Md5) 251 SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, 252 MarkId, LStat, Md5 253 FROM File JOIN Filename USING (FilenameId); 254 255SELECT 'DO NOT BREAK THE SCRIPT AFTER THIS POINT'; 256 257DROP TABLE Filename; 258DROP TABLE File; 259 260ALTER TABLE file_temp RENAME TO File; 261ALTER TABLE File 262 ADD PRIMARY KEY (FileId), 263 MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 264 ADD INDEX JobId (JobId), 265 ADD INDEX JobId_2 (JobId, PathId, Filename(255)); 266 267ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED DEFAULT 0; 268ALTER TABLE Job ADD COLUMN FileTable CHAR(20) default 'File'; 269ALTER TABLE JobHisto ADD COLUMN FileTable CHAR(20) default 'File'; 270 271 272CREATE TABLE Snapshot ( 273 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT, 274 Name TINYBLOB NOT NULL, 275 CreateTDate BIGINT NOT NULL, 276 CreateDate DATETIME NOT NULL, 277 ClientId INTEGER UNSIGNED DEFAULT 0, 278 Volume TINYBLOB NOT NULL, 279 Device TINYBLOB NOT NULL, 280 Type TINYBLOB NOT NULL, 281 Retention INTEGER DEFAULT 0, 282 Comment BLOB, 283 primary key (SnapshotId) 284); 285 286CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255)); 287 288UPDATE Version SET VersionId = 1016; 289 290END-OF-DATA 291 then 292 echo "Update of Bacula MySQL tables from 1015 to 1016 succeeded." 293 getVersion 294 else 295 echo "Update of Bacula MySQL tables from 1015 to 1016 failed." 296 exit 1 297 fi 298fi 299 300if [ "$DBVERSION" -eq 1016 ] ; then 301 if mysql $* -f <<END-OF-DATA 302USE ${db_name}; 303CREATE INDEX jobtdate_idx on JobHisto (JobTDate); 304ALTER TABLE Snapshot ADD COLUMN JobId INTEGER UNSIGNED DEFAULT 0; 305ALTER TABLE Snapshot ADD COLUMN FileSetId INTEGER UNSIGNED DEFAULT 0; 306UPDATE Version SET VersionId=1017; 307END-OF-DATA 308 then 309 echo "Update of Bacula MySQL tables from 1016 to 1017 succeeded." 310 getVersion 311 else 312 echo "Update of Bacula MySQL tables from 1016 to 1017 failed." 313 exit 1 314 fi 315fi 316 317if [ "$DBVERSION" -eq 1017 ] ; then 318 if mysql $* -f <<END-OF-DATA 319USE ${db_name}; 320CREATE TABLE FileMedia 321( 322 JobId integer UNSIGNED not null, 323 FileIndex integer UNSIGNED not null, 324 MediaId integer UNSIGNED not null, 325 BlockAddress bigint UNSIGNED default 0, 326 RecordNo integer UNSIGNED default 0, 327 FileOffset bigint UNSIGNED default 0, 328 INDEX (JobId, FileIndex) 329); 330UPDATE Version SET VersionId=1018; 331END-OF-DATA 332 then 333 echo "Update of Bacula MySQL tables from 1017 to 1018 succeeded." 334 getVersion 335 else 336 echo "Update of Bacula MySQL tables from 1017 to 1018 failed." 337 exit 1 338 fi 339fi 340 341# We are coming from version 16, so it's already done 342if [ "$SKIP1018" = 1 -a "$DBVERSION" -eq 1018 ]; then 343 if mysql $* -f <<END-OF-DATA 344USE ${db_name}; 345ALTER TABLE File MODIFY FileIndex INTEGER DEFAULT 0; 346ALTER TABLE RestoreObject MODIFY FileIndex INTEGER DEFAULT 0; 347ALTER TABLE BaseFiles MODIFY FileIndex INTEGER DEFAULT 0; 348UPDATE Version SET VersionId=1019; 349END-OF-DATA 350 then 351 echo "Update of Bacula MySQL tables 1018 to 1019 succeeded. (From 16)" 352 getVersion 353 else 354 echo "Update of Bacula MySQL tables 1018 to 1019 failed. (From 16)" 355 exit 1 356 fi 357fi 358 359if [ "$DBVERSION" -eq 1018 ] ; then 360 if mysql $* -f <<END-OF-DATA 361USE ${db_name}; 362ALTER TABLE BaseFiles MODIFY COLUMN BaseId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; 363ALTER TABLE Media 364 CHANGE COLUMN VolParts VolType INTEGER UNSIGNED DEFAULT 0, 365 ADD COLUMN VolParts INTEGER DEFAULT 0, 366 ADD COLUMN VolCloudParts INTEGER DEFAULT 0, 367 ADD COLUMN LastPartBytes BIGINT DEFAULT 0, 368 ADD COLUMN CacheRetention BIGINT DEFAULT 0; 369 370ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0; 371 372-- If you switch to MySQL 5.7 373ALTER TABLE Device ALTER COLUMN CleaningDate DROP DEFAULT; 374ALTER TABLE Job 375 ALTER COLUMN SchedTime DROP DEFAULT, 376 ALTER COLUMN StartTime DROP DEFAULT, 377 ALTER COLUMN EndTime DROP DEFAULT, 378 ALTER COLUMN RealEndTime DROP DEFAULT; 379ALTER TABLE JobHisto 380 ALTER COLUMN SchedTime DROP DEFAULT, 381 ALTER COLUMN StartTime DROP DEFAULT, 382 ALTER COLUMN EndTime DROP DEFAULT, 383 ALTER COLUMN RealEndTime DROP DEFAULT; 384ALTER TABLE LocationLog ALTER COLUMN Date DROP DEFAULT; 385ALTER TABLE FileSet ALTER COLUMN CreateTime DROP DEFAULT; 386ALTER TABLE Media 387 ALTER COLUMN FirstWritten DROP DEFAULT, 388 ALTER COLUMN LastWritten DROP DEFAULT, 389 ALTER COLUMN LabelDate DROP DEFAULT, 390 ALTER COLUMN InitialWrite DROP DEFAULT; 391ALTER TABLE Log ALTER COLUMN Time DROP DEFAULT; 392 393UPDATE Version SET VersionId=1019; 394END-OF-DATA 395 then 396 echo "Update of Bacula MySQL tables 1018 to 1019 succeeded." 397 getVersion 398 else 399 echo "Update of Bacula MySQL tables 1018 to 1019 failed." 400 exit 1 401 fi 402fi 403 404 405if [ "$DBVERSION" -eq 1019 ] ; then 406 if mysql $* -f <<END-OF-DATA 407USE ${db_name}; 408ALTER TABLE Pool ADD COLUMN MaxPoolBytes BIGINT UNSIGNED DEFAULT 0; 409ALTER TABLE Job ADD COLUMN PriorJob TINYBLOB; 410ALTER TABLE JobHisto ADD COLUMN PriorJob TINYBLOB; 411UPDATE Version SET VersionId=1020; 412END-OF-DATA 413 then 414 echo "Update of Bacula MySQL tables 1019 to 1020 succeeded." 415 getVersion 416 else 417 echo "Update of Bacula MySQL tables 1019 to 1020 failed." 418 exit 1 419 fi 420fi 421 422if [ "$DBVERSION" -eq 1020 ] ; then 423 if mysql $* -f <<END-OF-DATA 424USE ${db_name}; 425ALTER TABLE File 426 MODIFY FileIndex INTEGER DEFAULT 0; 427ALTER TABLE RestoreObject 428 MODIFY FileIndex INTEGER DEFAULT 0; 429ALTER TABLE BaseFiles 430 MODIFY FileIndex INTEGER DEFAULT 0; 431UPDATE Version SET VersionId=1021; 432END-OF-DATA 433 then 434 echo "Update of Bacula MySQL tables 1020 to 1021 succeeded." 435 getVersion 436 else 437 echo "Update of Bacula MySQL tables 1020 to 1021 failed." 438 exit 1 439 fi 440fi 441 442if [ "$DBVERSION" -eq 1021 ] ; then 443 if mysql $* -f <<END-OF-DATA 444USE ${db_name}; 445 446CREATE TABLE TagJob 447( 448 JobId INTEGER UNSIGNED not null, 449 Tag TINYBLOB not null, 450 primary key (JobId, Tag(255)) 451); 452 453CREATE TABLE TagClient 454( 455 ClientId INTEGER UNSIGNED not null, 456 Tag TINYBLOB not null, 457 primary key (ClientId, Tag(255)) 458); 459 460CREATE TABLE TagMedia 461( 462 MediaId INTEGER UNSIGNED not null, 463 Tag TINYBLOB not null, 464 primary key (MediaId, Tag(255)) 465); 466 467CREATE TABLE TagObject 468( 469 ObjectId INTEGER UNSIGNED not null, 470 Tag TINYBLOB not null, 471 primary key (ObjectId, Tag(255)) 472); 473 474CREATE TABLE Object 475( 476 ObjectId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 477 478 JobId INTEGER UNSIGNED not null, 479 Path BLOB NOT NULL, 480 Filename BLOB NOT NULL, 481 PluginName TINYBLOB NOT NULL, 482 483 ObjectType TINYBLOB NOT NULL, 484 ObjectName TINYBLOB NOT NULL, 485 ObjectSource TINYBLOB NOT NULL, 486 ObjectUUID TINYBLOB NOT NULL, 487 ObjectSize BIGINT NOT NULL, 488 primary key (ObjectId) 489); 490 491create index object_jobid_idx on Object (JobId); 492create index object_type_idx on Object (ObjectType(255)); 493create index object_name_idx on Object (ObjectName(255)); 494create index object_source_idx on Object (ObjectSource(255)); 495 496CREATE TABLE Events 497( 498 EventsId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 499 EventsCode TINYBLOB NOT NULL, 500 EventsType TINYBLOB NOT NULL, 501 EventsTime DATETIME, 502 EventsInsertTime DATETIME, 503 EventsDaemon TINYBLOB NOT NULL, 504 EventsSource TINYBLOB NOT NULL, 505 EventsRef TINYBLOB NOT NULL, 506 EventsText BLOB NOT NULL, 507 primary key (EventsId) 508); 509create index events_time_idx on Events (EventsTime); 510UPDATE Version SET VersionId=1022; 511END-OF-DATA 512 then 513 echo "Update of Bacula MySQL tables 1021 to 1022 succeeded." 514 getVersion 515 else 516 echo "Update of Bacula MySQL tables 1021 to 1022 failed." 517 exit 1 518 fi 519fi 520 521 522exit 0 523