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...
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;
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.
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.)
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?
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.
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?
--
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
