On 8/12/22 11:40, Eric Bollengier via Bacula-devel wrote:
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).
DOH!!! I misread.
That's line 3. Line 3 is NOT the FileMedia table, which is why I was
unable to reproduce this error on a copy of the FileMedia table restored
from last night's DB backup.
The offending command is not:
ALTER TABLE FileMedia ADD FileMediaId integer auto_increment primary key;
It's this, I believe:
ALTER TABLE JobHisto MODIFY COLUMN JobId int PRIMARY KEY AUTO_INCREMENT;
And if I test THIS against a copy restored from the last backup...
MySQL 127.0.0.1> CREATE TABLE `JobHisto` (
-> `JobId` int(10) unsigned NOT NULL,
-> `Job` varbinary(64) NOT NULL,
-> `Name` varbinary(96) NOT NULL,
-> `Type` binary(1) NOT NULL,
-> `Level` binary(1) NOT NULL,
-> `ClientId` int(11) DEFAULT 0,
-> `JobStatus` binary(1) NOT NULL,
-> `SchedTime` datetime DEFAULT NULL,
-> `StartTime` datetime DEFAULT NULL,
-> `EndTime` datetime DEFAULT NULL,
-> `RealEndTime` datetime DEFAULT NULL,
-> `JobTDate` bigint(20) unsigned DEFAULT 0,
-> `VolSessionId` int(10) unsigned DEFAULT 0,
-> `VolSessionTime` int(10) unsigned DEFAULT 0,
-> `JobFiles` int(10) unsigned DEFAULT 0,
-> `JobBytes` bigint(20) unsigned DEFAULT 0,
-> `ReadBytes` bigint(20) unsigned DEFAULT 0,
-> `JobErrors` int(10) unsigned DEFAULT 0,
-> `JobMissingFiles` int(10) unsigned DEFAULT 0,
-> `PoolId` int(10) unsigned DEFAULT 0,
-> `FileSetId` int(10) unsigned DEFAULT 0,
-> `PriorJobId` int(10) unsigned DEFAULT 0,
-> `PurgedFiles` tinyint(4) DEFAULT 0,
-> `HasBase` tinyint(4) DEFAULT 0,
-> `HasCache` tinyint(4) DEFAULT 0,
-> `Reviewed` tinyint(4) DEFAULT 0,
-> `Comment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> `FileTable` char(20) COLLATE utf8mb4_unicode_ci DEFAULT 'File',
-> `PriorJob` tinyblob DEFAULT NULL,
-> PRIMARY KEY (`JobId`),
-> KEY `StartTime` (`StartTime`),
-> KEY `jobtdate_idx` (`JobTDate`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.090 sec)
MySQL 127.0.0.1> ALTER TABLE JobHisto MODIFY COLUMN JobId int PRIMARY
KEY AUTO_INCREMENT;
ERROR 1068 (42000): Multiple primary key defined
MySQL 127.0.0.1>
Bingo. There's the problem line.
Though ... since JobIds come from the Job table it's not clear to me
why we're even making this change, since Job.JobId is still an INT
UNSIGNED. Surely JobHisto.JobId should ALSO be an INT INSIGNED? And
since values are copied to it from the Job table it should not be
AUTO_INCREMENT. JobId values should get autoincremented once, in the
Job table when they are created, and nowhere else. The JobId column is
an INT UNSIGNED in every other table (except PathVisibility, in which it
is *also* erroneously a signed INT(11)).
I would just remove that ALTER directive, period. It is unnecessary and
wrong. What we OUGHT to be doing there is changing PathVisibility.JobId
to the correct INT UNSIGNED.
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.
Oh sure, it's harmless, it's just unnecessary considering that the
TINYBLOB type is a maximum of 255 bytes in the first place. The real
problem there is the presumed typo (object_type_idx in place of
object_category_idx) that results in trying to create an index that
already exists.
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?
A length-restricted index can be UNIQUE without causing problems, that's
fine as long as there are no NULLs. The differences between PRIMARY and
UNIQUE are:
- A table can (and should) have only one PRIMARY key, but may have
multiple UNIQUE keys
- A PRIMARY key is not permitted to contain NULLABLE columns, while a
UNIQUE key CAN contain NULLable columns, but UNIQUE processing *stops*
at the first NULL encountered in any covered column of the index.
Otherwise their semantics are identical.
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.
Then why not use BEGIN/COMMIT in the mysql update as well?
I mean, true, BEGIN/COMMIT is not supported on MYISAM tables. But at
this point anyone running a post-2010 version of MySQL is running InnoDB
(or its improved Percona or MariaDB versions) by default *anyway* unless
they *deliberately took active steps* to change their default back to
MyISAM. I imagine the performance of Bacula on MyISAM tables would be
terrible, because the performance of *anything* non-trivial on MyISAM
tables is terrible. It should have been obsoleted and removed at least
fifteen years ago. People forget that one of the principal design
constraints for the MyISAM storage engine was that it needed to provide
*acceptable* performance on a small, shared PC server, in an age when a
large PC server was one that had a whole 32MB of RAM.
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."
That's actually not strictly correct. TEXT columns are sorted according
to the charset collation in use, while BLOB columns are sorted on pure
byte values. This has a more-or-less-accidental side effect of making
SORTs on string values in BLOBs case sensitive. You can achieve the
same case-sensitive searching on a TEXT field by using ORDER BY BINARY
... instead or a simple ORDER BY ... on the column.
But VARCHAR and VARBINARY have the exact same differences between them
as TEXT and BLOB, so that behavior would transfer. The only functional
differences between a TINYBLOB and a VARBINARY(255) is that a VARBINARY
can be in a MEMORY table, which any BLOB cannot, and can (and should)
have a DEFAULT value while a BLOB cannot.
You can do the same thing with VARCHARs, using ORDER BY BINARY ... but
then you effectively have a VARBINARY, so you may as well just declare
it as a VARBINARY in the first place.
...Shouldn't that say 13.0, not 11.4?
Yes of course, thanks for the feedback
Best Regards,
Eric
Sorry for the delay after release finding this, btw; Gentoo's
app-backup/bacula went straight from 11.0.6-r2 to 13.0.1, which only
appeared today, and I've been too busy to pull and test manual builds.
--
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
