From f4ee3b2fcaac449e77beea3f91cfb8561b602cb1 Mon Sep 17 00:00:00 2001
From: Phil Stracchino <phils@caerllewys.net>
Date: Sat, 29 May 2021 11:30:05 +0200
Subject: [PATCH] Improve MySQL upgrade procedure

---
 bacula/src/cats/update_mysql_tables.in | 83 +++++++++++++++-----------
 1 file changed, 47 insertions(+), 36 deletions(-)

diff --git a/bacula/src/cats/update_mysql_tables.in b/bacula/src/cats/update_mysql_tables.in
index a929fdd06..0d8094f29 100644
--- a/bacula/src/cats/update_mysql_tables.in
+++ b/bacula/src/cats/update_mysql_tables.in
@@ -133,10 +133,12 @@ if [ "$DBVERSION" -eq 1014 ] ; then
     if mysql $* -f <<END-OF-DATA
 USE ${db_name};
 
-ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
+ALTER TABLE Media
+    ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0,
+    ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0,
+    ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
+    ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
+
 UPDATE Version SET VersionId=1015;
 END-OF-DATA
     then
@@ -171,9 +173,8 @@ ALTER TABLE BaseFiles
 
 DROP INDEX IF EXISTS JobId on File;
 DROP INDEX IF EXISTS JobId_2 on File;
-DROP TABLE IF EXISTS file_temp;
 
-CREATE TABLE file_temp (
+CREATE TABLE IF NOT EXISTS file_temp (
    FileId BIGINT UNSIGNED NOT NULL,
    FileIndex INTEGER DEFAULT 0,
    JobId INTEGER UNSIGNED NOT NULL,
@@ -185,6 +186,8 @@ CREATE TABLE file_temp (
    MD5 TINYBLOB
    );
 
+TRUNCATE TABLE file_temp;
+
 INSERT INTO file_temp (FileId, FileIndex, JobId, PathId, Filename, DeltaSeq,
 		       MarkId, LStat, Md5)
    SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, 
@@ -197,14 +200,16 @@ DROP TABLE Filename;
 DROP TABLE File;
 ALTER TABLE file_temp RENAME TO File;
 
-ALTER TABLE File ADD PRIMARY KEY (FileId);
-ALTER TABLE File MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-CREATE INDEX JobId on File (JobId);
-CREATE INDEX JobId_2 on File (JobId, PathId, Filename(255));
+ALTER TABLE File
+    ADD PRIMARY KEY (FileId),
+    MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+    ADD INDEX JobId (JobId),
+    ADD INDEX JobId_2 (JobId, PathId, Filename(255));
 
 ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED DEFAULT 0;
-ALTER TABLE UnsavedFiles DROP COLUMN FilenameId;
-ALTER TABLE UnsavedFiles ADD COLUMN Filename BLOB NOT NULL;
+ALTER TABLE UnsavedFiles
+    DROP COLUMN FilenameId,
+    ADD COLUMN Filename BLOB NOT NULL;
 
 UPDATE Version SET VersionId = 1017;
 
@@ -225,9 +230,7 @@ USE ${db_name};
 DROP INDEX IF EXISTS JobId on File;
 DROP INDEX IF EXISTS JobId_2 on File;
 
-DROP TABLE IF EXISTS file_temp;
-
-CREATE TABLE file_temp (
+CREATE TABLE IF NOT EXISTS file_temp (
    FileId BIGINT UNSIGNED NOT NULL,
    FileIndex INTEGER DEFAULT 0,
    JobId INTEGER UNSIGNED NOT NULL,
@@ -239,6 +242,8 @@ CREATE TABLE file_temp (
    MD5 TINYBLOB
    );
 
+TRUNCATE TABLE file_temp;
+
 INSERT INTO file_temp (FileId, FileIndex, JobId, PathId, Filename, DeltaSeq,
 		       MarkId, LStat, Md5)
    SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, 
@@ -251,10 +256,11 @@ DROP TABLE Filename;
 DROP TABLE File;
 
 ALTER TABLE file_temp RENAME TO File;
-ALTER TABLE File ADD PRIMARY KEY (FileId);
-ALTER TABLE File MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-CREATE INDEX JobId on File (JobId);
-CREATE INDEX JobId_2 on File (JobId, PathId, Filename(255));
+ALTER TABLE File
+    ADD PRIMARY KEY (FileId),
+    MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+    ADD INDEX JobId (JobId),
+    ADD INDEX JobId_2 (JobId, PathId, Filename(255));
 
 ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED DEFAULT 0;
 ALTER TABLE Job ADD COLUMN FileTable CHAR(20) default 'File';
@@ -352,29 +358,34 @@ if [ "$DBVERSION" -eq 1018 ] ; then
     if mysql $* -f  <<END-OF-DATA
 USE ${db_name};
 ALTER TABLE BaseFiles MODIFY COLUMN BaseId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-ALTER TABLE Media CHANGE COLUMN VolParts VolType INTEGER UNSIGNED DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolParts INTEGER DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolCloudParts INTEGER DEFAULT 0;
-ALTER TABLE Media ADD COLUMN LastPartBytes BIGINT DEFAULT 0;
-ALTER TABLE Media ADD COLUMN CacheRetention BIGINT DEFAULT 0;
+ALTER TABLE Media 
+   CHANGE COLUMN VolParts VolType INTEGER UNSIGNED DEFAULT 0,
+   ADD COLUMN VolParts INTEGER DEFAULT 0,
+   ADD COLUMN VolCloudParts INTEGER DEFAULT 0,
+   ADD COLUMN LastPartBytes BIGINT DEFAULT 0,
+   ADD COLUMN CacheRetention BIGINT DEFAULT 0;
+
 ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0;
 
 -- If you switch to MySQL 5.7
 ALTER TABLE Device ALTER COLUMN CleaningDate DROP DEFAULT;
-ALTER TABLE Job    ALTER COLUMN SchedTime    DROP DEFAULT;
-ALTER TABLE Job    ALTER COLUMN StartTime    DROP DEFAULT;
-ALTER TABLE Job    ALTER COLUMN EndTime      DROP DEFAULT;
-ALTER TABLE Job    ALTER COLUMN RealEndTime  DROP DEFAULT;
-ALTER TABLE JobHisto ALTER COLUMN SchedTime  DROP DEFAULT;
-ALTER TABLE JobHisto ALTER COLUMN StartTime  DROP DEFAULT;
-ALTER TABLE JobHisto ALTER COLUMN EndTime    DROP DEFAULT;
-ALTER TABLE JobHisto ALTER COLUMN RealEndTime DROP DEFAULT;
+ALTER TABLE Job
+    ALTER COLUMN SchedTime    DROP DEFAULT,
+    ALTER COLUMN StartTime    DROP DEFAULT,
+    ALTER COLUMN EndTime      DROP DEFAULT,
+    ALTER COLUMN RealEndTime  DROP DEFAULT;
+ALTER TABLE JobHisto
+    ALTER COLUMN SchedTime  DROP DEFAULT,
+    ALTER COLUMN StartTime  DROP DEFAULT,
+    ALTER COLUMN EndTime    DROP DEFAULT,
+    ALTER COLUMN RealEndTime DROP DEFAULT;
 ALTER TABLE LocationLog ALTER COLUMN Date DROP DEFAULT;
 ALTER TABLE FileSet	ALTER COLUMN CreateTime DROP DEFAULT;
-ALTER TABLE Media	ALTER COLUMN FirstWritten DROP DEFAULT;
-ALTER TABLE Media	ALTER COLUMN LastWritten DROP DEFAULT;
-ALTER TABLE Media	ALTER COLUMN LabelDate DROP DEFAULT;
-ALTER TABLE Media	ALTER COLUMN InitialWrite DROP DEFAULT;
+ALTER TABLE Media
+    ALTER COLUMN FirstWritten DROP DEFAULT,
+    ALTER COLUMN LastWritten DROP DEFAULT,
+    ALTER COLUMN LabelDate DROP DEFAULT,
+    ALTER COLUMN InitialWrite DROP DEFAULT;
 ALTER TABLE Log 	ALTER COLUMN Time DROP DEFAULT;
 
 UPDATE Version SET VersionId=1019;
-- 
2.47.3