Fix database update scripts

[bacula/bacula] / bacula / src / cats / update_mysql_tables.in

diff --git a/bacula/src/cats/update_mysql_tables.in b/bacula/src/cats/update_mysql_tables.in
index f2862fe234e9aedd65fecf88f5fffa6793c3d8c7..0c7329281cc182560775a344f2f6efe3d1332151 100644 (file)
--- a/bacula/src/cats/update_mysql_tables.in
+++ b/bacula/src/cats/update_mysql_tables.in
@@ -1,16 +1,16 @@
 #!/bin/sh
 #
-# Copyright (C) 2000-2015 Kern Sibbald
+# Copyright (C) 2000-2017 Kern Sibbald
 # License: BSD 2-Clause; see file LICENSE-FOSS
 #
 # Shell script to update MySQL tables from Bacula Community version 
-#  5.0.x, 5.2.x, 7.0.x
+#  5.0.x, 5.2.x, 7.0.x, 7.2.x, 7.4.x
 #
 echo " "
-echo "This script will update a Bacula MySQL database from version 12-14 to 15"
+echo "This script will update a Bacula MySQL database from version 12-15 to 16"
 echo " "
-echo "Depending on the current version of your catalog, you may have to run this script"
-echo " multiple times"
+echo "Depending on the current version of your catalog,"
+echo "you may have to run this script multiple times."
 echo " "
 
 bindir=@MYSQL_BINDIR@
@@ -35,10 +35,10 @@ if [ "x$DBVERSION" = x ]; then
     exit 1
 fi
 
-if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
+if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then
     echo " "
     echo "The existing database is version $DBVERSION !!"
-    echo "This script can only update an existing version 12-14 version 15."
+    echo "This script can only update an existing version 12-15 to version 16."
     echo "Error. Cannot upgrade this database."
     echo " "
     exit 1
@@ -143,7 +143,48 @@ END-OF-DATA
     fi
 fi
 
-
+if [ "$DBVERSION" -eq 15 ] ; 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 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 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 Log        ALTER COLUMN Time DROP DEFAULT;
+# speeds up restore selection if many files and accurate
+CREATE INDEX job_jobtdate_idx ON Job (JobTDate);
+
+UPDATE Version SET VersionId=16;
 END-OF-DATA
+    then
+       echo "Update of Bacula MySQL tables 15 to 16 succeeded."
+       getVersion
+    else
+       echo "Update of Bacula MySQL tables 15 to 16 failed."
+       exit 1
+    fi
+fi
+
 
 exit 0