Hello Phil, Thanks for the suggestions. I'm almost ready to apply them to the current update script.
The only point that worries me is that if the break is done during the drop of the Filename/File tables or during the rename of the file_temp table, then if you restart the script with your modification, all the data is lost and you need to restore the catalog. I will add a couple of warning messages. I hope that the MySQL team will work to allow schema modification inside transactions (like with PostgreSQL) to avoid this kind of issues. Thanks, Best Regards, Eric On 5/19/21 20:42, Phil Stracchino wrote: > 1. I recommend the following changes to update_mysql_tables: > > > --- update_mysql_tables.orig 2021-05-19 12:54:15.226740443 -0400 > +++ update_mysql_tables 2021-05-19 12:54:53.833949117 -0400 > @@ -161,14 +161,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 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, > @@ -176,10 +176,12 @@ > 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); > > > These changes allow the script to be retried (after making corrections) > if it fails for any reason. > > > 2. If running on a Galera cluster, YOU MUST BRING THE CLUSTER DOWN TO A > SINGLE NODE before performing this migration (and preferably also > disable Galera temporarily) because the schema rewrites will almost > certainly exceed the hard limits on Galera writeset size (128K maximum > rows or 4GB total writeset size, whichever comes first). > > _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
