Eric,
The following would be my first-cut patch.
The other way to handle restarting/resuming the copy operation when
updating to 11.0.2 would be instead of truncating the file_temp table if
it already exists, create it with its PRIMARY key already in place and
then use INSERT IGNORE instead of a simple INSERT. This would ensure
that no record is copied twice and allow resuming the copy operation
where it left off. (There would still be some loss of efficiency in
skipping records already copied, but at least they would not have to be
written to storage a second time.)
However, there is a non-obvious schema issue that this does not address.
There are many places where we are currently using TINYBLOB types to
contain data as small as md5 checksums. This is a common, but bad,
idea. It is bad because the MySQL MEMORY storage engine, used for
explicit and implicit temporary tables, does not support the BLOB/TEXT
types, and so any temporary table that contains any column of a BLOB or
TEXT type will be forced to disk, with obvious performance impact.
Clearly there are places where the size of the data is unknown and
potentially large, and there we have little alternative but to use an
appropriate BLOB type. But where we want to store binary data of a
known and manageable maximum length, we should be using VARBINARY
instead for performance reasons.
I have already in the past converted many of the TINYBLOB columns in my
Bacula catalog schema to VARBINARY with no ill effects. I now need to
redo a few of them because we just rewrote the File table. :)
>From my most recent nightly DB backup, these are the columns I currently
have converted to VARBINARY:
minbar:root:/dbdumps/minbar-20210521-04:55:25 # zgrep -i varbinary
bacula*schema.sql.gz
bacula.Client-schema.sql.gz: `Name` varbinary(64) NOT NULL,
bacula.Client-schema.sql.gz: `Uname` varbinary(255) NOT NULL,
bacula.Counters-schema.sql.gz: `Counter` varbinary(64) NOT NULL,
bacula.Counters-schema.sql.gz: `WrapCounter` varbinary(255) NOT NULL,
bacula.Device-schema.sql.gz: `Name` varbinary(64) NOT NULL,
bacula.FileSet-schema.sql.gz: `FileSet` varbinary(64) NOT NULL,
bacula.FileSet-schema.sql.gz: `MD5` varbinary(32) NOT NULL,
bacula.Job-schema.sql.gz: `Job` varbinary(64) NOT NULL,
bacula.Job-schema.sql.gz: `Name` varbinary(96) NOT NULL,
bacula.JobHisto-schema.sql.gz: `Job` varbinary(64) NOT NULL,
bacula.JobHisto-schema.sql.gz: `Name` varbinary(96) NOT NULL,
bacula.Location-schema.sql.gz: `Location` varbinary(64) NOT NULL,
bacula.Media-schema.sql.gz: `VolumeName` varbinary(32) NOT NULL,
bacula.Media-schema.sql.gz: `MediaType` varbinary(32) NOT NULL,
bacula.MediaType-schema.sql.gz: `MediaType` varbinary(32) NOT NULL,
bacula.Path-schema.sql.gz: `Path` varbinary(4096) NOT NULL,
bacula.Pool-schema.sql.gz: `Name` varbinary(64) NOT NULL,
bacula.Pool-schema.sql.gz: `LabelFormat` varbinary(255) DEFAULT NULL,
bacula.RestoreObject-schema.sql.gz: `PluginName` varbinary(255) NOT NULL,
bacula.Snapshot-schema.sql.gz: `Name` varbinary(64) NOT NULL,
bacula.Snapshot-schema.sql.gz: `Volume` varbinary(64) NOT NULL,
bacula.Snapshot-schema.sql.gz: `Device` varbinary(64) NOT NULL,
bacula.Snapshot-schema.sql.gz: `Type` varbinary(64) NOT NULL,
bacula.Storage-schema.sql.gz: `Name` varbinary(64) NOT NULL,
I also need to re-convert File.MD5 to VARBINARY(32) and, now, change
File.Filename to VARBINARY(255).
What is the maximum possible size of File.LStat? That is another good
candidate to become a VARBINARY.
--- update_mysql_tables.in.orig 2021-03-26 11:34:44.000000000 -0400
+++ update_mysql_tables.in 2021-05-21 12:04:42.271123740 -0400
@@ -131,14 +131,16 @@
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
echo "Update of Bacula MySQL tables from 1014 to 1015 succeeded."
getVersion
@@ -161,14 +163,14 @@
ALTER TABLE RestoreObject
MODIFY FileIndex INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE BaseFiles
MODIFY FileIndex INTEGER UNSIGNED DEFAULT 0;
-DROP INDEX JobId on File;
-DROP INDEX JobId_2 on File;
+DROP INDEX IF EXISTS JobId on File;
+DROP INDEX IF EXISTS JobId_2 on File;
-CREATE TABLE file_temp (
+CREATE TABLE IF NOT EXTSTS file_temp (
FileId BIGINT UNSIGNED NOT NULL,
FileIndex INTEGER DEFAULT 0,
JobId INTEGER UNSIGNED NOT NULL,
PathId INTEGER UNSIGNED NOT NULL,
Filename BLOB NOT NULL,
@@ -176,28 +178,32 @@
MarkId INTEGER UNSIGNED DEFAULT 0,
LStat TINYBLOB NOT NULL,
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,
MarkId, LStat, Md5
FROM File JOIN Filename USING (FilenameId);
+RENAME TABLE File to File_old, file_temp to File;
+DROP TABLE File_old;
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;
END-OF-DATA
then
@@ -211,14 +217,14 @@
if [ "$STOP1015" = "" -a "$DBVERSION" -eq 1015 ]; then
if mysql --skip-reconnect $* <<END-OF-DATA
USE ${db_name};
-DROP INDEX JobId on File;
-DROP INDEX JobId_2 on File;
+DROP INDEX IF EXISTS JobId on File;
+DROP INDEX IF EXISTS JobId_2 on File;
-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,
PathId INTEGER UNSIGNED NOT NULL,
Filename BLOB NOT NULL,
@@ -226,26 +232,33 @@
MarkId INTEGER UNSIGNED DEFAULT 0,
LStat TINYBLOB NOT NULL,
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,
MarkId, LStat, Md5
FROM File JOIN Filename USING (FilenameId);
+RENAME TABLE File to File_old, file_temp to File;
+DROP TABLE File_old;
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,
+ ADD COLUMN Filename BLOB NOT NULL;
+
ALTER TABLE Job ADD COLUMN FileTable CHAR(20) default 'File';
ALTER TABLE JobHisto ADD COLUMN FileTable CHAR(20) default 'File';
CREATE TABLE Snapshot (
@@ -337,33 +350,37 @@
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;
END-OF-DATA
then
--
Phil Stracchino
Babylon Communications
ph...@caerllewys.net
p...@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
