Re: Bacula 11.0.2 update notes

Hello Phil,

Thanks for your patch, I will apply it for 11.0.4, some more comments are inline.

On 21.05.21 18:26, Phil Stracchino wrote:
> 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.)

Yes, I'm not an expert here, if you have a good script to convert the catalog, I'm sure it will be appreciated.

> 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.


This is the kind of explanation I was looking for, thanks.

> 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,

A directive name can be up to 127 bytes, and I'm looking to extend it at 256 or even more in a short term.

A VolumeName, MediaType, etc.. have the same possible length.

> 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.

At this time, we can store up to a SHA512 in base64, I would suggest to do a test.

Thanks for your patch, this is very much appreciated.

Best Regards,

Eric