Comment 24 for bug 1553563

On 24.06.2016 [19:07:53 -0000], Kern Sibbald wrote:
> Many thanks. I will look in detail at the links to the PIE links you
> provided to see if anything jumps out at me.
>
> One possible concern in your patch for make_mysql_tables.in:
> If the line:
>
> SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE', ''));
>
> simply sets the NO_ZERO_DATE to NULL rather than 1, all is OK and you
> can skip the rest of this paragraph.

My understanding is that it will simply elide "NO_ZERO_DATE" from the
current sql_mode in the session if it is set. That, in turn, in my
reading of the SQL Manual, since strict mode is on, just tells MySQL to
not enforce that DATETIME fields cannot have 0 as a value, which in turn
means it should be allowed, default or otherwise.

> However, on the off chance that it tells MySQL not to permit any zero
> value for a date, then Bacula will fail. Bacula does not need the
> default value to be zero, but Bacula does set a zero value (a valid
> Unix/Linux value) in a number of the DATETIME fields. If MySQL either
> changes the value or fails the SQL command, Bacula will fail.
>
> In the community version 7.4.1, I edited the make_mysql_tables.in to
> remove all the DATETIME default values of 0 which were the cause the
> incompatibility. Consequently, if you include my new
> make_mysql_tables.in you should not need for the above SET SESSION line.

Yep, I saw this. I tested in Yakkety, actually (and made a mistake in my
requests to test so far, folks ended up testing my Yakkety version,
which is still a valid test and needed before we can fix Xenial). In
Yakkety (and Xenial), bacula-director-mysql ends up failing to install
(fresh install):
https://bugs.launchpad.net/ubuntu/+source/bacula/+bug/1591397
https://bugs.launchpad.net/ubuntu/+source/bacula/+bug/1596034

The Yakkety version has your fix included (7.4.1 base), but still
trips the DATETIME issue in my testing.