bacula mysql support requires server configuration changes

[Robie Basak]

[Impact]

 * dbconfig configuration for bacula-director-mysql fails with:
   "invalid default value for 'CleaningDate'"

[Test Case]

 * Do a fresh install of 16.04. Install mysql-server and then install bacula-director-mysql. The install will fail during the postinst step when the database is installed.

[Regression Potential]

 * The regression likelihood is low, as the fix proposed simply lowers the strictness of strict mode during database installation so that zero values are allowed for DATETIME fields.

See https://bugs.launchpad.net/ubuntu/+source/bacula/+bug/1570923/comments/5 for the explanation and workaround.

[Norvald H. Ryeng (nryeng)]

The default SQL mode in 5.7 is "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

These are good defaults, so I recommend not changing the default SQL mode for the installation, but rather setting a suitable SQL mode in bacula sessions, using SET SESSION sql_mode = '...'. I also recommend that as many of the modes are kept and that only those causing problems are removed.

See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html for more info.

[Robie Basak (racb)]

Nish, FYI. If there isn't a good fix for this available right now, perhaps we should SRU the other bugs and let users work around this one for the time being?

[Kern Sibbald (kern)]

I consider this a MySQL bug, because they have apparently made the value 0 illegal as a default for a DATETIME value which is nothing other than a standard Unix time variable. That said, they are not likely to agree this is a bug and even if they do it will take a long time to fix.

The solution is to remove all the "DEFAULT 0" values from the creation of the MySQL tables which is done in the Bacula source file <bacula-source>/src/cats/make_mysql_tables.in

You can get a copy of the current Bacula release file (will be released tomorrow) from the public Bacula repository.

[Nish Aravamudan (nacc)]

Please test the potential fix version at:

https://launchpad.net/~nacc/+archive/ubuntu/bacula

Which results in 7.4.1~dfsg-1ubuntu1~ppa1 on yakkety or 7.4.1~dfsg-1.1~ppa8.is.actually.7.0.5+dfsg-4.1~ppa2 on xenial.

[Nish Aravamudan (nacc)]

@Kern, quick question. We can obviously backport http://www.bacula.org/git/cgit.cgi/bacula/commit/?h=Branch-7.4&id=36d647345df3c98a2cc1ad24af31e73aaff4a2e9 [note the bug referenced in the commit message seems to be incorrect? http://bugs.bacula.org/view.php?id=1849 ].

But that only helps with fresh installs of bacula in 16.04.

If a user is upgrading from 14.04 or 15.10 -> 16.04 and going through the MySQL transition, we also need to update their tables (aiui). Would that normally be done by an update_mysql_tables call? Should there have been a corresponding change in the upstream commit?

Thanks!
-Nish

[Nish Aravamudan (nacc)]

Hello all,

After reviewing the other open Bacula bugs, I've set up a new PPA with a more normal versioning and (hopefully) fixes to all the issues I know about on 16.04 and 16.10 (and I think handles upgrading from 14.04 to 16.04)

https://launchpad.net/~nacc/+archive/ubuntu/bacula3

16.04: 7.0.5+dfsg-4ubuntu0.1~ppa1
16.10: 7.4.2+dfsg-1ubuntu1~ppa1

Please test these versions and report back, I'd like to see if we can these both sponsored quickly.

[Nish Aravamudan (nacc)]

bacula 7.4.3+dfsg-1 is in 16.10 and installs fine.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Brian Murray (brian-murray)]

Hello Robie, or anyone else affected,

Accepted bacula into xenial-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/bacula/7.0.5+dfsg-4ubuntu0.1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-needed to verification-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[Nish Aravamudan (nacc)]

Verified with: 7.0.5+dfsg-4ubuntu0.1

[Launchpad Janitor (janitor)]

This bug was fixed in the package bacula - 7.0.5+dfsg-4ubuntu0.1

---------------
bacula (7.0.5+dfsg-4ubuntu0.1) xenial; urgency=medium

  * debian/patches/libmysqlclient_r.patch: Fix bug where MySQL 5.7 is
    improperly linked on Ubuntu 16.04. Closes LP: #1570923.
  * debian/patches/disable_mysql_NO_ZERO_DATE.patch: Disable
    NO_ZERO_DATE mode for MySQL table creation. Closes LP: #1591397,
    LP: #1577858.
  * d/rules: do not use -Bsymoblic-functions when linking. Closes
    LP: #1553563, LP: #1567824.
  * debian/patches/fix_batch_mode.patch: Detect that MySQL 5.7 is thread
    safe and enable batch mode. Closes LP: #1592749.

 -- Nishanth Aravamudan <email address hidden> Fri, 15 Jul 2016 14:34:59 -0700

[Chris J Arges (arges)]

The verification of the Stable Release Update for bacula has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.