Hello Phil,
On 8/12/22 16:45, Phil Stracchino wrote:
minbar:root:/usr/libexec/bacula:16 # ./update_mysql_tables
This script will update a Bacula MySQL database
from any from version 12-15 or 1014-1022 to version 1024
which is needed to convert from any Bacula Community
versions 5.0.x, 5.2.x, 7.4.x, 9.x.x to Community version 11.4
Depending on the current version of your catalog, you may
have to run this script multiple times
ERROR 1068 (42000) at line 3: Multiple primary key defined
ERROR 1061 (42000) at line 6: Duplicate key name 'object_type_idx'
Update of Bacula MySQL tables 1022 to 1023 succeeded.
Investigating...
Thanks for the valuable feedback.
The first failure above (line 3) was because there was already a primary key
defined on the table.
Do this instead:
ALTER TABLE FileMedia DROP PRIMARY KEY,
ADD FileMediaId INT AUTO_INCREMENT PRIMARY KEY;
I don't see which query to replace with this one, the FileMedia doesn't have
a primary key defined (created in step 1022). Is MySQL taking the index defined
on JobId,Fileindex as the primary key ? (would be a bit strange).
The second operation (line 6) failed because the index you told mysql to create
already existed. There is already an index object_type_idx over ObjectType,
then you tried to create a second index ALSO called object_type_idx over
ObjectCategory:
ALTER TABLE Object ADD ObjectCategory TINYBLOB NOT NULL;
create index object_type_idx on Object (ObjectCategory(255));
I'm presuming that new index was meant to be object_category_idx. Also, it's
pointless to specify an index field length that's the same size as the column,
and mysql quite sensibly ignores the length specification because it is a NOOP.
I believe it's done this way because in the past it was mandatory. Maybe it is
not anymore, but between all flavors and versions, I rather prefer a NOOP
operation versus a syntax error.
Also, it makes more clear that only the first 255 bytes are used in the index,
which can cause errors when the index has the UNIQUE property. Do you know if
it's still the case?
The script then terminated after incorrectly reporting update to 1023 succeeded,
but did not go on to update to 1024, and will not separately update 1023 to 1024
because it says it cannot update 1023. So as it now exists, this script will
leave you stuck at version 1023 which neither 11.x nor 13.x will run on. After
fixing the errors above, I had to manually apply the 1024 update section. (With
corrections.)
It is done on purpose, if a part of the upgrade doesn't work, we cannot
continue. With PostgreSQL, we use BEGIN/COMMIT to make sure to rollback
to the previous state if one single operation cannot be done. Here, all
operations up to the error point are done and cannot be undone, so the
user has to edit the upgrade script manually.
In the 1024 update section, don't do this:
ALTER TABLE Object ADD ObjectStatus BINARY(1) DEFAULT 'U';
ALTER TABLE Object ADD ObjectCount INTEGER UNSIGNED DEFAULT 1;
CREATE INDEX object_status_idx on Object (ObjectStatus);
Do this:
ALTER TABLE Object
ADD ObjectStatus BINARY(1) DEFAULT 'U',
ADD ObjectCount INTEGER UNSIGNED DEFAULT 1,
ADD INDEX object_status_idx (ObjectStatus);
Why alter a table three separate times when you can alter it once in the same
amount of time?
It is a good idea, adding a column is not taking time, but
it's probably better to do it once.
Also I see a lot of TINYBLOB columns in the Object table and the Tag tables.
Don't do this in MySQL. Really. Use a VARBINARY(255) instead. It's
functionally equivalent from a data standpoint, but MEMORY storage engine does
not support BLOB/TEXT types (for good and sound reasons), so every time you have
an implicit temporary table (from a subSELECT or a JOIN) that contains a
BLOB/TEXT column, the operation will be forced to disk, impacting performance.
There is no good reason to use a TINYBLOB here instead of a VARBINARY and it
doesn't save you any storage. I have changed every TINYBLOB NOT NULL in my
Bacula database to VARBINARY(255) NOT NULL DEFAULT '' with no impact other than
improved performance.
According to my notes:
"We use BLOB rather than TEXT because in MySQL, BLOBs are identical to TEXT
except that BLOB is case sensitive in sorts, which is what we want, and TEXT
is case insensitive."
So, changing TINYBLOB to VARBINARY(255) can be tested, but very carefully I
think, specially in this kind of aspect.
Also, in that initial message...
This script will update a Bacula MySQL database
from any from version 12-15 or 1014-1022 to version 1024
which is needed to convert from any Bacula Community
versions 5.0.x, 5.2.x, 7.4.x, 9.x.x to Community version 11.4
...Shouldn't that say 13.0, not 11.4?
Yes of course, thanks for the feedback
Best Regards,
Eric
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
