1#!/bin/sh 2# 3# Copyright (C) 2000-2017 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# 6# Shell script to update MySQL tables from Bacula Community version 7# 5.0.x, 5.2.x, 7.0.x, 7.2.x, 7.4.x 8# 9echo " " 10echo "This script will update a Bacula MySQL database from version 12-15 to 16" 11echo " " 12echo "Depending on the current version of your catalog," 13echo "you may have to run this script multiple times." 14echo " " 15 16bindir=@MYSQL_BINDIR@ 17PATH="$bindir:$PATH" 18db_name=@db_name@ 19 20ARGS=$* 21 22getVersion() 23{ 24 mysql $ARGS -D ${db_name} -e "select VersionId from Version LIMIT 1\G" >/tmp/$$ 25 DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$` 26} 27 28getVersion 29 30if [ "x$DBVERSION" = x ]; then 31 echo 32 echo "Unable to detect database version, you can specify connection information" 33 echo "on the command line." 34 echo "Error. Cannot upgrade this database." 35 exit 1 36fi 37 38if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then 39 echo " " 40 echo "The existing database is version $DBVERSION !!" 41 echo "This script can only update an existing version 12-15 to version 16." 42 echo "Error. Cannot upgrade this database." 43 echo " " 44 exit 1 45fi 46 47# For all versions, we need to create the Index on Media(StorageId) 48# It may fail, but it's not a big problem 49# mysql $* -f <<END-OF-DATA >/dev/null 2> /dev/null 50# CREATE INDEX media_storageid_idx ON Media (StorageId); 51# END-OF-DATA 52 53if [ "$DBVERSION" -eq 12 ] ; then 54 if mysql $* -f <<END-OF-DATA 55USE ${db_name}; 56 57CREATE TABLE RestoreObject ( 58 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 59 ObjectName BLOB NOT NULL, 60 RestoreObject LONGBLOB NOT NULL, 61 PluginName TINYBLOB NOT NULL, 62 ObjectLength INTEGER DEFAULT 0, 63 ObjectFullLength INTEGER DEFAULT 0, 64 ObjectIndex INTEGER DEFAULT 0, 65 ObjectType INTEGER DEFAULT 0, 66 FileIndex INTEGER UNSIGNED DEFAULT 0, 67 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, 68 ObjectCompression INTEGER DEFAULT 0, 69 PRIMARY KEY(RestoreObjectId), 70 INDEX (JobId) 71); 72 73CREATE INDEX jobhisto_jobid_idx ON JobHisto (JobId); 74UPDATE Version SET VersionId=13; 75 76END-OF-DATA 77 then 78 echo "Update of Bacula MySQL tables from 12 to 13 succeeded." 79 getVersion 80 else 81 echo "Update of Bacula MySQL tables from 12 to 13 failed." 82 exit 1 83 fi 84fi 85 86if [ "$DBVERSION" -eq 13 ] ; then 87 if mysql $* -f <<END-OF-DATA 88USE ${db_name}; 89 90ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0; 91UPDATE Version SET VersionId=14; 92 93END-OF-DATA 94 then 95 echo "Update of Bacula MySQL tables from 13 to 14 succeeded." 96 getVersion 97 else 98 echo "Update of Bacula MySQL tables from 13 to 14 failed." 99 exit 1 100 fi 101fi 102 103if [ "$DBVERSION" -eq 14 ] ; then 104 if mysql $* -f <<END-OF-DATA 105USE ${db_name}; 106 107INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 108 ('I', 'Incomplete Job',25); 109ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0; 110ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0; 111ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0; 112ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0; 113ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED; 114 115CREATE TABLE Snapshot ( 116 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT, 117 Name TINYBLOB NOT NULL, 118 JobId INTEGER UNSIGNED DEFAULT 0, 119 FileSetId INTEGER UNSIGNED DEFAULT 0, 120 CreateTDate BIGINT NOT NULL, 121 CreateDate DATETIME NOT NULL, 122 ClientId INTEGER UNSIGNED DEFAULT 0, 123 Volume TINYBLOB NOT NULL, 124 Device TINYBLOB NOT NULL, 125 Type TINYBLOB NOT NULL, 126 Retention INTEGER DEFAULT 0, 127 Comment BLOB, 128 primary key (SnapshotId) 129); 130 131CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255)); 132 133CREATE INDEX jobtdate_idx on JobHisto (JobTDate); 134 135UPDATE Version SET VersionId=15; 136END-OF-DATA 137 then 138 echo "Update of Bacula MySQL tables from 14 to 15 succeeded." 139 getVersion 140 else 141 echo "Update of Bacula MySQL tables from 14 to 15 failed." 142 exit 1 143 fi 144fi 145 146if [ "$DBVERSION" -eq 15 ] ; then 147 if mysql $* -f <<END-OF-DATA 148USE ${db_name}; 149ALTER TABLE BaseFiles MODIFY COLUMN BaseId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; 150ALTER TABLE Media CHANGE COLUMN VolParts VolType INTEGER UNSIGNED DEFAULT 0; 151ALTER TABLE Media ADD COLUMN VolParts INTEGER DEFAULT 0; 152ALTER TABLE Media ADD COLUMN VolCloudParts INTEGER DEFAULT 0; 153ALTER TABLE Media ADD COLUMN LastPartBytes BIGINT DEFAULT 0; 154ALTER TABLE Media ADD COLUMN CacheRetention BIGINT DEFAULT 0; 155ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0; 156 157 158-- If you switch to MySQL 5.7 159ALTER TABLE Device ALTER COLUMN CleaningDate DROP DEFAULT; 160ALTER TABLE Job ALTER COLUMN SchedTime DROP DEFAULT; 161ALTER TABLE Job ALTER COLUMN StartTime DROP DEFAULT; 162ALTER TABLE Job ALTER COLUMN EndTime DROP DEFAULT; 163ALTER TABLE Job ALTER COLUMN RealEndTime DROP DEFAULT; 164ALTER TABLE JobHisto ALTER COLUMN SchedTime DROP DEFAULT; 165ALTER TABLE JobHisto ALTER COLUMN StartTime DROP DEFAULT; 166ALTER TABLE JobHisto ALTER COLUMN EndTime DROP DEFAULT; 167ALTER TABLE JobHisto ALTER COLUMN RealEndTime DROP DEFAULT; 168ALTER TABLE LocationLog ALTER COLUMN Date DROP DEFAULT; 169ALTER TABLE FileSet ALTER COLUMN CreateTime DROP DEFAULT; 170ALTER TABLE Media ALTER COLUMN FirstWritten DROP DEFAULT; 171ALTER TABLE Media ALTER COLUMN LastWritten DROP DEFAULT; 172ALTER TABLE Media ALTER COLUMN LabelDate DROP DEFAULT; 173ALTER TABLE Media ALTER COLUMN InitialWrite DROP DEFAULT; 174ALTER TABLE Log ALTER COLUMN Time DROP DEFAULT; 175# speeds up restore selection if many files and accurate 176CREATE INDEX job_jobtdate_idx ON Job (JobTDate); 177 178UPDATE Version SET VersionId=16; 179END-OF-DATA 180 then 181 echo "Update of Bacula MySQL tables 15 to 16 succeeded." 182 getVersion 183 else 184 echo "Update of Bacula MySQL tables 15 to 16 failed." 185 exit 1 186 fi 187fi 188 189 190exit 0 191