mysql-server-5.7.postinst fails on upgrade if the datadir has been changed from default

[Wayne]

The systemd service script for MySQL will report an error if it doesn't find a database in the default datadir, /var/lib/mysql

[Impact]
For users with custom datadir, this means they have to keep the default database in /var/lib/mysql as well, or the systemd service will refuse to start.

We change the systemd service script to check that a database exists in the directory found in the config, rather than the default.

[Test case]
* Install the mysql-server package, then stop the service
* Edit /etc/mysql/mysql.conf.d/mysqld.cnf and change datadir
* Delete all contents of /var/lib/mysql/
* Update apparmor profile to allow read/write access to new datadir
* Make sure the custom datadir exists, and run mysqld --initialize
* Try to start the service

[Regression Potential]
This change has been in Yakkety for some time (5.7.15-0ubuntu2).

Should the commands to fetch the value from the config not work right, it could break the systemd script whether the user has a proper database or not.

[Original description]
mysql upgrade fails if datadir (mysqld.cnf) is not /var/lib/mysql

ProblemType: Package
DistroRelease: Ubuntu 16.04
Package: mysql-server-5.7 5.7.12-0ubuntu1
ProcVersionSignature: Ubuntu 4.4.0-21.37-generic 4.4.6
Uname: Linux 4.4.0-21-generic x86_64
ApportVersion: 2.20.1-0ubuntu2
Architecture: amd64
Date: Mon Apr 25 19:31:55 2016
ErrorMessage: subprocess installed post-installation script returned error exit status 1
InstallationDate: Installed on 2015-11-30 (146 days ago)
InstallationMedia: Ubuntu 15.10 "Wily Werewolf" - Release amd64 (20151021)
Logs.var.log.daemon.log:

MySQLConf.etc.mysql.conf.d.mysql.cnf: [mysql]
MySQLConf.etc.mysql.conf.d.mysqldump.cnf:
 [mysqldump]
 quick
 quote-names
 max_allowed_packet = 16M
MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: ['debian-5.7.flag']
ProcCmdline: BOOT_IMAGE=/boot/vmlinuz-4.4.0-21-generic root=UUID=402be1c6-5faf-4404-9598-4ec21d975aa8 ro quiet splash vt.handoff=7
RelatedPackageVersions:
 dpkg 1.18.4ubuntu1
 apt 1.2.10ubuntu1
SourcePackage: mysql-5.7
Title: package mysql-server-5.7 5.7.12-0ubuntu1 failed to install/upgrade: subprocess installed post-installation script returned error exit status 1
UpgradeStatus: Upgraded to xenial on 2016-04-22 (3 days ago)
modified.conffile..etc.apparmor.d.usr.sbin.mysqld: [modified]
modified.conffile..etc.mysql.mysql.conf.d.mysqld.cnf: [modified]
mtime.conffile..etc.apparmor.d.usr.sbin.mysqld: 2016-04-24T12:43:49.748706
mtime.conffile..etc.mysql.mysql.conf.d.mysqld.cnf: 2016-04-24T12:45:04.992172

[Launchpad Janitor (janitor)]

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

[Lars Tangvald (lars-tangvald)]

Files affected:
* d/additions/mysqld-systemd-start
 Sanity check fails is /var/lib/mysql and /var/lib/mysql/mysql do not exist
 Maybe not needed since the server error by itself should be sufficient if the configured datadir is missing

* d/additions/apparmor-profile
 Read/write permissions to datadir. Can probably expect users to change the profile if they want a custom datadir, but document/warn about it somewhere?

* d/postinst:
 Creates datadir, sets ownership and runs initialize if empty. Will need to check config for correct one to prevent it from trying to create and populate /var/lib/mysql even if user wants it somewhere else (upstream scripts have some code to do this).

* d/postrm:
 Data removal on purge. Not all that important

5.7 also creates /var/lib/mysql-files and /var/lib/mysql-keyring, but they aren't as important for installation

[Lars Tangvald (lars-tangvald)]

The minimum to fix it would be modifying mysqld-systemd-start to check the config in the sanity check, doing the same in postinst so it doesn't create junk data in /var/lib, and requiring the user to handle apparmor.

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-5.7 - 5.7.15-0ubuntu2

---------------
mysql-5.7 (5.7.15-0ubuntu2) yakkety; urgency=medium

  * Add working dir to perl lib path for dep8 upstream.
    New versions of perl will no longer automatically include working dir in
    the path. This was causing the mtr suite to fail to start.
  * Change mysql-testsuite dependency from python to libjson-perl.
    Tests written in python were rewritten in perl, so testsuite no longer
    depends on python, but tests fail if libjson-perl is missing.
  * Build with DWITH_LZ4=system
    If the flag is not set the package will be built with a bundled liblz4
    instead of the one provided by the liblz4-dev build-dep.
  * Add support for custom datadir to systemd service (LP: #1574782)
    The service was reporting errors if no database was found in /var/lib/mysql
    It will now check the path specified in the config
  * Fix support for soft-link datadir (LP: #1474212)
    Upgrading more than once when datadir is soft-link was causing errors
  * Escape special characters in password (LP: #1598992)
    The password string was not escaped, causing SQL errors if certain special
    characters such as apostrophes were included.
  * Removed always-false check on purge in postrm (LP: #1602945)
    Users were never seeing the query to remove data on a package purge

 -- Lars Tangvald <email address hidden> Fri, 16 Sep 2016 22:23:41 +0200

[Andy Whitcroft (apw)]

Hello Wayne, or anyone else affected,

Accepted mysql-5.7 into xenial-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/mysql-5.7/5.7.17-0ubuntu0.16.04.2 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!

[Lars Tangvald (lars-tangvald)]

Verified that 5.7.17-0ubuntu0.16.04.2 fixes this issue.

Note that the test case was missing a couple of steps:
You need to erase the database in /var/lib/mysql to trigger the bug, and update the apparmor profile to make the custom datadir work

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-5.7 - 5.7.17-0ubuntu0.16.04.2

---------------
mysql-5.7 (5.7.17-0ubuntu0.16.04.2) xenial; urgency=medium

  * Add libjson-perl dependency for test suite (LP: #1631338)
    The last two python tests in the mtr suite were rewritten
    in perl, and require this package. Added for mysql-testsuite
    and as a build-dep.
  * Build with DWITH_LZ4=system (LP: #1631339)
    Package was previously built with bundled liblz4, though it
    was specified as a build-dep.
  * Add support for custom datadir to systemd service (LP: #1574782)
    The service was reporting an error if no database could be found in
    /var/lib/mysql. It now checks the location specified in the config.
    Note that user must still handle apparmor access for custom datadir.
  * Fix copy of soft-link datadir to /var/lib/mysql-upgrade (LP: #1474212)
    Upgrade would sometimes fail if mysql-upgrade already contained a
    link copy from a previous upgrade.
  * Escape special characters in password (LP: #1598992)
    Special characters in the root password would cause syntax errors
    and postinst failures.
  * Failing tests on platforms supported by upstream fail build (LP: #1646488)
    A passing test suite is now enforced on i386 and amd64 platforms at
    build-time.
  * d/copyright: Updated with information about new source files
  * Unstable test main.xa_prepared_binlog_off disabled pending upstream fix.
    Upstream bug report: http://bugs.mysql.com/bug.php?id=83340
  * d/lintian-overrides: Updated line numbers

 -- Lars Tangvald <email address hidden> Fri, 03 Feb 2017 08:53:04 +0100

[Brian Murray (brian-murray)]

The verification of the Stable Release Update for mysql-5.7 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.