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 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-16 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 16 ] ; then 39 echo " " 40 echo "The existing database is version $DBVERSION !!" 41 echo "This script can only update an existing version 12-16 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 178ALTER TABLE Device 179 MODIFY CleaningDate DATETIME DEFAULT NULL; 180ALTER TABLE Media 181 MODIFY FirstWritten DATETIME DEFAULT NULL, 182 MODIFY LastWritten DATETIME DEFAULT NULL, 183 MODIFY LabelDate DATETIME DEFAULT NULL, 184 MODIFY InitialWrite DATETIME DEFAULT NULL; 185ALTER TABLE Job 186 MODIFY SchedTime DATETIME DEFAULT NULL, 187 MODIFY StartTime DATETIME DEFAULT NULL, 188 MODIFY EndTime DATETIME DEFAULT NULL, 189 MODIFY RealEndTime DATETIME DEFAULT NULL; 190ALTER TABLE JobHisto 191 MODIFY SchedTime DATETIME DEFAULT NULL, 192 MODIFY StartTime DATETIME DEFAULT NULL, 193 MODIFY EndTime DATETIME DEFAULT NULL, 194 MODIFY RealEndTime DATETIME DEFAULT NULL; 195ALTER TABLE LocationLog 196 MODIFY Date DATETIME DEFAULT NULL; 197ALTER TABLE CDImages 198 MODIFY LastBurn DATETIME DEFAULT NULL; 199 200UPDATE Version SET VersionId=16; 201END-OF-DATA 202 then 203 echo "Update of Bacula MySQL tables 15 to 16 succeeded." 204 getVersion 205 else 206 echo "Update of Bacula MySQL tables 15 to 16 failed." 207 exit 1 208 fi 209fi 210 211# 212# Update version 16 to be more robust for newer more 213# strict MySQLs. Note: this is a post-first release 214# fix. 215# 216if [ "$DBVERSION" -eq 16 ] ; then 217 if mysql $* -f <<END-OF-DATA 218USE ${db_name}; 219ALTER TABLE Device 220 MODIFY CleaningDate DATETIME DEFAULT NULL; 221ALTER TABLE File 222 MODIFY FileIndex INTEGER DEFAULT 0; 223ALTER TABLE RestoreObject 224 MODIFY FileIndex INTEGER DEFAULT 0; 225ALTER TABLE BaseFiles 226 MODIFY FileIndex INTEGER DEFAULT 0; 227ALTER TABLE Media 228 MODIFY FirstWritten DATETIME DEFAULT NULL, 229 MODIFY LastWritten DATETIME DEFAULT NULL, 230 MODIFY LabelDate DATETIME DEFAULT NULL, 231 MODIFY InitialWrite DATETIME DEFAULT NULL; 232ALTER TABLE Job 233 MODIFY SchedTime DATETIME DEFAULT NULL, 234 MODIFY StartTime DATETIME DEFAULT NULL, 235 MODIFY EndTime DATETIME DEFAULT NULL, 236 MODIFY RealEndTime DATETIME DEFAULT NULL; 237ALTER TABLE JobHisto 238 MODIFY SchedTime DATETIME DEFAULT NULL, 239 MODIFY StartTime DATETIME DEFAULT NULL, 240 MODIFY EndTime DATETIME DEFAULT NULL, 241 MODIFY RealEndTime DATETIME DEFAULT NULL; 242ALTER TABLE LocationLog 243 MODIFY Date DATETIME DEFAULT NULL; 244ALTER TABLE CDImages 245 MODIFY LastBurn DATETIME DEFAULT NULL; 246 247END-OF-DATA 248 then 249 echo "Update of Bacula MySQL tables 16 to 16 succeeded." 250 getVersion 251 else 252 echo "Update of Bacula MySQL tables 16 to 16 failed." 253 exit 1 254 fi 255fi 256 257 258exit 0 259