[FFe] Please update to MySQL 5.7 series

[cooolman]

upgrade to MySQL 5.7.10 and above, Ubuntu 16.04

[Transition Status]

https://bugs.launchpad.net/ubuntu/+bugs?field.tag=mysql-5.7-transition

Test in ppa:mysql-ubuntu/mysql-5.7. We included all reverse build depends of libmysqlclient-dev and all reverse depends of libmysqlclient18. Could there be anything else that we've missed? Packages that did not rebuild smoothly are:

plugin_auth_common.h: fix agreed, patch to mysql-5.7 uploaded to bug 1565414:
* anope - fixed with patch
* cctools - fixed with patch
* cvm - fixed with patch
* dsyslog - fixed with patch, but hit libmysqlclient_r issue
* gentle - fixed with patch
* kamailio - fixed with patch
* libdbi-drivers - Also runs obsolete mysql_install_db. Should be fairly simple to fix
* mailutils - fixed with patch
* mysqlmail - fixed with patch
* opensmtpd-extras - fixed with patch
* libodb-mysql - fixed with patch

libmysqlclient_r: replaced by libmysqlclient, need to link against that instead, so needs patching for: (bug 1564871) - Robie
* cacti-spine: fixed
* dsyslog: (revealed after fixing header include path)
* hhvm: fixed, but hit isnan issue
* icinga2: fixed
* mysql++: pending; hit autoreconf issue
* resiprocate
* sysbench; when fixed, hit autoreconf issue
* tarantool-lts
* tango - Fails in a weird way. Instead of complaining that it can't find libmysqlclient_r, it just doesn't build a couple of essential binaries, so the error is from dh_install

test failures:
* cfengine3: FAILING (archive: all archs; PPA: all archs) - Lars
* libgda5: FAILING - (archive: i386,amd64,s390x; PPA: all archs) - Lars
* lighttpd: FAILING - (archive: arm64,s390x; PPA: all archs) - Lars
  This seems unstable. On my test box (amd64) it was failing yesterday, but now it's passing

uses internal client functions:
* myodbc: needs version bump; looks difficult/invasive. Bug 1564856. Needs patch (upstream working on it) and packaging update (Lars).
* mysql-proxy: mysql-proxy has been replaced by mysql-router. Suggestion from upstream is to drop it from the release
* mysql-workbench: WB is updated to 6.3.6 in the ppa, and connector-c++ to 1.1.7. Both green, but still not certain if more of the patches from older versions should be included - Lars
* net-snmp: fixed with patch uploaded to bug 1565003 - Norvald
* pure-ftpd: uses make_scrambled_password function. We might export - Norvald?

maybe not mysql-related (missing symbols may be things that used to leak from the client library):
* gnokii: compile error (archive: FTBFS all archs)
* kannel-sqlbox: configure error: Kannel gwlib is required! (archive: all OK) - This is now green in the ppa without any updates to it (that I can see)
* kodi (compile error: isnan)
* libreoffice (compile error: isnan): pending rebuild test
* netopng: not needed (same missing archs as archive)
* php5: not needed (pending deletion)
* zoneminder: FTBFS on powerpc because vlc FTBFS on powerpc

other:
* coturn - warning in client_plugin.h + -Werror? (PPA: FTBFS on armhf only; archive: no FTBFS)

[Robie Basak (racb)]

Lars is currently working on this.

[Lars Tangvald (lars-tangvald)]

I've uploaded the packaging to https://github.com/ltangvald/mysql-5.7/tree/mysql-alt (will add to a ppa as well).
Should be ready to start testing and reviewing.

[Lars Tangvald (lars-tangvald)]

Packaging has been moved to master branch of https://github.com/ltangvald/mysql-5.7/
and uploaded to https://launchpad.net/~lars-tangvald/+archive/ubuntu/mysql-5.7

[Robie Basak (racb)]

Requesting an FFe for this please. Lars has had a branch ready for a while now, and we've already had one review round, but I haven't managed to review again in time. I expect to get this uploaded in 1-3 weeks.

One complicating factor is that this will trigger a library transition for libmysqlclient, and I know this is undesirable after FF. In mitigation, upstream aren't expecting any API changes to cause breakage - the ABI bump was to clean up unused symbols that shouldn't be exported.

Upstream are working closely with us on this, and I am a DM for this package in Debian.

[Adam Conrad (adconrad)]

17:00 <infinity> If you're thinking in a weekish, then it'll be fine.
17:01 <infinity> My answer approaches no as time goes on.

[Lars Tangvald (lars-tangvald)]

The package libmysqld-pic has been removed as it is obsolete (there was no difference between libmysqld-pic and libmysqld-dev in 5.6 either).

The amarok package depends on it, so this dependency needs changing. Attaching debdiff output for the patch.

Another note is that it also depends on mysql-server-core-5.6 | mysql-server-core, and the latter isn't actually provided. Will change this so the 5.7 packaging provides the non-versioned core package.

[Lars Tangvald (lars-tangvald)]

Alternate amarok patch:
Changes it to depend on virtual-mysql-server-core instead of changing 5.7 packaging to provide mysql-server-core.

[Robie Basak (racb)]

I've uploaded an initial cut of the proposed 5.7 packaging to our PPA: https://launchpad.net/~mysql-ubuntu/+archive/ubuntu/mysql-5.7/+packages and my current work tree (still being rebased) is available at https://git.launchpad.net/~racb/ubuntu/+source/mysql-5.7/log/?h=5.7v5ubuntu_dev2

I'd appreciate anyone who can test these packages to help inform the release team about the regression risk for this FFe.

Here are my thoughts on what will be valuable to test and what probably doesn't need testing.

What doesn't need testing first. This is about the packaging. We generally trust upstream's QA process for MySQL itself as they have a good track record on this. So once MySQL is running, we expect it to work. So no need to test in detail that MySQL is functioning as expected, expect where functionality interacts with packaging. Testing that mysql-files and mysql-keyring are working is good, as is that plugins work, since that interacts with how we ship the files. Testing the functionality of the SELECT statement probably isn't, since that's a core thing that is unlikely to be regressed by packaging.

Note that migrating from MariaDB to MySQL is not expected to work currently unless you manage this process manually yourself.

Next, specific paths to test. We want to check that the package works both when installed freshly on Xenial and also when upgrading from Trusty or Wily. Other things that could influence success are how the user installed on the previous release (eg. which packages were installed and whether and how configuration files in /etc were changed - see https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=813413 for an example of a failure case in this area; this one is known to not be fixed yet as we don't have a good answer).

Previous upgrade paths might also change behaviour - that is, the state of /var/lib/mysql as it ended up by use of previous MySQL packaging releases.

If you can help out with testing, please report back in this bug detailing exactly what you tested and what your results are. Please report both success and failure. Thanks!

[Lars Tangvald (lars-tangvald)]

I've been testing the packages as we work on them, ironing out issues with upgrading from both 5.5 and 5.6 as we go (I'll need to rerun these tests for the currently uploaded package, though).
But I've only been testing on Xenial, i.e. installing 5.5/5.6 then replacing with 5.7, so I haven't done anything with testing the full upgrade from previous Ubuntu releases.
I've also not done much with testing custom configurations and databases with any significant amount of data in them.

[Lars Tangvald (lars-tangvald)]

I've redone the basic smoke testing for verifying just the package upgrades.
No issues were found during testing.

These tests do not include full Ubuntu release upgrades; They were only run on
16.04.

To include 5.5 I rebuilt it from the current 5.5.47 source in Debian. The 5.6
packages were used from the main Ubuntu repository, and the 5.7 packages were
installed from the ppa at
https://launchpad.net/~mysql-ubuntu/+archive/ubuntu/mysql-5.7

For each test I added minimal database data. One table with a couple of rows
and a test user with a password.

I ran through each test both with and without setting a password for the root
user.

For each test I verified that:

* mysql_upgrade was run successfully during installation (except for the clean
 install.
* If the root user had a password that the password was still valid
* If the root user had a blank password that the auth_socket plugin was enabled,
 requiring host system root access to log in with it.
* That the test user login was still valid
* That the test table data was intact

Upgrade paths tested:

* Fresh 5.7 install
* 5.7 installed on existing 5.7 database
* Fresh 5.6 install, upgraded to 5.7
* Fresh 5.5 install, upgraded to 5.7
* Fresh 5.5 install, upgraded to 5.6, then upgraded to 5.7
* Fresh 5.5 install, upgraded to 5.6, mysql_upgrade run, then upgraded to 5.7

We'll move on to more comprehensive upgrade testing including a full test
database and Ubuntu release upgrades.

[cooolman (coolman)]

Who is he Lars Tanvald if he could trust, and whether in the release of MySQL 5.7?

[Lars Tangvald (lars-tangvald)]

I'm a part of the MySQL engineering team, and have been working with Robie Basak on this for a while

[Robie Basak (racb)]

Lars discovered an upgrade issue from Trusty to Xenial. We think the issue is that the Trusty apparmor does not support Unix domain socket rules, and during the upgrade the userspace tools are too old so mysqld fails to start.

I'm currently building a version in my test PPA that adds an apparmor dependency to ensure the new apparmor is configured first during the upgrade. Hopefully this will fix it for anyone upgrading from the latest Trusty HWE kernel. We're not sure about the Trusty release kernel yet.

We're doing this in ppa:racb/experimental so as not to affect any test results from those testing for us at the moment. If this works, then I'll update the main testing ppa:mysql-ubuntu/mysql-5.7 tonight (UK time). Please note that if you're already testing from the latter PPA then this may affect your results. If you need me to hold back from uploading because you need the old version for consistency across test runs, please let me know and I'll delay uploading.

[Lars Tangvald (lars-tangvald)]

I'll do a more complete writeup once the testing is complete, but want to follow up on the above comment:

I can confirm that the package from ppa:racb/experimental fixes the apparmor issue we saw before.
I've also tested the same starting from Precise.

So the upgrade from 5.5 in Trusty to 5.7 in Xenial is now tested to work with kernel versions 3.13 and 4.2 in Trusty

[Lars Tangvald (lars-tangvald)]

1 Background
============

These tests were conducted to verify that MySQL upgrades are possible
from from previous Ubuntu releases to Ubuntu 16.04 (Xenial Xerus).

There are 3 live Ubuntu versions:

 - Ubuntu 12.04 LTS (Precise Pangolin)
    - Ships with MySQL 5.5

- Ubuntu 14.04 LTS (Trusty Tahr)
    - Ships with MySQL 5.5 (default) and 5.6

- Ubuntu 15.10 (Wily Werewolf)
    - Ships with MySQL 5.6

Normally, Oracle only supports upgrades from one major version to the
next (5.5 and 5.6 are considered major versions), so an upgrade from
5.5 to 5.7 should go through 5.6.

Upgrades skipping one major release are not explicitly blocked, but it
is not guaranteed to work in the general case since it is not a design
requirement in MySQL Engineering. However, it may still work, but it
is not generally tested.

On request from Debian/Ubuntu, Oracle has already tested direct
upgrades from 5.5 to 5.7 and found them to be viable. There are no
known problems with upgrading directly from 5.5 to 5.7.

This round of tests is to verify the packaging of MySQL 5.7 in Ubuntu
16.04. In particular, it verifies that all documented upgrade paths
work with a database containing example data.

2 Test design
=============

Testing was done using VirtualBox VMs, reverting to snapshots to
test new upgrade paths from the same starting point.

The test installs MySQL on one version of Ubuntu, sets up a simple
database with example data and users, follows the upgrade path to
Ubuntu 16.04 with MySQL 5.7 and verifies that the database is still
usable by running a few simple queries.

The database used was the Sakila Sample Database [1], and the test
queries were based on the example queries [2] for the Sakila Sample
Database and do a couple of INSERT, SELECT and DELETE queries.

MySQL 5.7 packages for Ubuntu 16.04 were installed from the MySQL
Ubuntu Packagers Team PPA [3]. A dependency issue was found and fixed
during testing. The fixed package was uploaded to a separate PPA [4],
which was used to retest.

For each upgrade path, the following steps are taken:

1. Install the starting version of Ubuntu.

2. Install the mysql-server package (if applicable, c.f. Sect 3.2.3).

3. Create a MySQL user "sakila" with a non-empty password.

4. Install the Sakila Sample Database as the "sakila" user.

5. Log in as the "sakila" user and run the example queries.

6. Do the Ubuntu release upgrade process using
   do-release-upgrade.

   When upgrading from 14.04 to 16.04, add the MySQL 5.7 PPA before
   upgrading.

7. If the upgrade is from 14.04 with MySQL 5.6, install the new
   mysql-server metapackage.

8. Verify that MySQL has been upgraded to 5.7.11.

9. Log in as the "sakila" user and run the example queries again.

10. Compare the results of steps 5 and 9.

3. Upgrade Paths
================

The main paths for upgrading to Ubuntu 16.04 and MySQL 5.7 are
described below.

In addition to this, there are variations of whether or not the user
specified a password for the MySQL "root" user account. If not, the
upgrade to 5.7 should enable the auth_socket plugin (UNIX socket
authentication) to improve security.

For each starting point (Ubuntu version and MySQL version), both empt...

[Lars Tangvald (lars-tangvald)]

The previous comment contains the overview of the upgrade testing we've done.

[Robie Basak (racb)]

Thank you for the detailed testing on this, Lars!

I think that the second issue would not constitute a regression for this proposed upload; that bug exists already in Xenial. It also doesn't happen if the Trusty user has mysql-server installed (since that will cause mysql-server-5.6 to be removed in favour of mysql-server-5.7 in Xenial). After discussion with Lars, we therefore think that it'll disappear as soon as we delete src:mysql-5.6 from Xenial and process NBS, since it is only triggered by mysql-server-5.6.postinst in Xenial. Or we could try fixing it in Xenial by uploading a new src:mysql-5.6, but I think that would be a waste of effort since that will be deleted anyway.

As this bug already exists and we therefore can't regress it by uploading our new src:mysql-5.7, I presume this should be fine from an FFe point of view?

So in that case, I think we're ready to upload now. Release Team: please could you re-ack the FFe?

[Robie Basak (racb)]

I've just confirmed that src:mysql-5.6 version 5.6.28-1ubuntu3 fixes Lars' second issue.

[Robie Basak (racb)]

11:15 <infinity> rbasak: Okay, caught up on the bug. LGTM, but please see the transition and migration through to completion ASAP.

11:15 <rbasak> infinity: ack. Thanks!

[Robie Basak (racb)]

14:49 <superm1> rbasak: ok i'm hoping not by beta because mythbuntu has some hardcoded stuff in the ubiquity plugin to configure 5.6 mysql

14:50 <superm1> it's not a problem to change it to 5.7 other than it's a timing thing to make sure they both land in the right ISO image

block-proposed until I understand what we need to do about the beta.

[Mario Limonciello (superm1)]

Just adding some myth* tasks that will need to be adjusted too:
mythtv:
debian/control.in debian.control in mythtv need to include mysql-server-5.7

mythbuntu-live-autostart:
ubiquity plugin refers directly to 5.6 and needs to be changed

[Mario Limonciello (superm1)]

We discovered a problem with mythtv compiling against mysql 5.7.

It boils down to mysql expects that if you #include <mysql/mysql.h> that you have the mysql installation's include path included at compile time to get the rest of the headers. This is presumptuous as it worked fine for 5.5 and 5.6 previously without it.

I've filed an issue upstream MythTV here: https://code.mythtv.org/trac/ticket/12696

For now to at least let mythtv compile against 5.7 in Ubuntu xenial I've written a small patch:
https://github.com/MythTV/packaging/commit/8112bd623cffc90a6ef9e15be6192fecb3c01d40

That will be in the next upload.

I'm sure mythtv isn't the only project that is going to fail compiling against mysql in this way (checking a #include header is common in configure scripts), so it would really be best to work with upstream to sort this out.

Thanks!

[Lars Tangvald (lars-tangvald)]

Some of the headers that are in include/mysql/mysql are also copied (not moved) to include/mysql, probably to patch this sort of problem. With the way the headers are structured right now I don't really see a very nice way to solve this, so one suggestion is to simply copy everything.

[Lars Tangvald (lars-tangvald)]

The test failures for dbconfig-common and redmine are due to a) The tests setting empty port options in config files and b) MySQL 5.7 no longer allowing empty options in config file.

[Mario Limonciello (superm1)]

Another suggestion to solve the header mess is to patch anywhere that
header files are looking for <mysql/...> global reference to patch to local
reference "mysql/..."

Not sure if that would cause more harm though.

On Tue, Mar 29, 2016, 03:16 Lars Tangvald <email address hidden> wrote:

> The test failures for dbconfig-common and redmine are due to a) The
> tests setting empty port options in config files and b) MySQL 5.7 no
> longer allowing empty options in config file.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1528583
>
> Title:
> [FFe] Please update to MySQL 5.7 series
>
> To manage notifications about this bug go to:
>
> https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1528583/+subscriptions
>

[Lars Tangvald (lars-tangvald)]

Yeah, the copying is an ugly hack, but it's already done to some degree, and has the least risk of breaking anything, I think.

[Launchpad Janitor (janitor)]

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

[Gianfranco Costamagna (costamagnagianfranco)]

Hi, can anybody let this package migrate?
autopkgtest for dbconfig-common 2.0.4: amd64: Regression ♻ , armhf: Regression ♻ , i386: Regression ♻ , ppc64el: Regression ♻ , s390x: Regression ♻

currently 4 packages are blocked because of it:
gnash
boinc
vtk
mythtv

[Martin Pitt (pitti)]

It looks like dbconfig-common needs to be adjusted for MySQL 5.7. It fails on "mysql: [ERROR] mysql: Empty value for 'port' specified."

[Lars Tangvald (lars-tangvald)]

pitti: Yeah, we have a bug in dbconfig-common (1563274), and a patch ready for mysql-5.7 that downgrades that error to a deprecation warning to give them time to fix it

[Robie Basak (racb)]

On Fri, Apr 01, 2016 at 12:36:37PM -0000, Martin Pitt wrote:
> It looks like dbconfig-common needs to be adjusted for MySQL 5.7. It
> fails on "mysql: [ERROR] mysql: Empty value for 'port' specified."

Yeah. We're planning on patching MySQL to turn that into a warning
instead of an error. Upstream may be willing to do that for future 5.7
releases for us as well. Since the alternative is to adjust
dbconfig-common, and I'm told that is pretty invasive.

I'm not sure what this means in terms of blocking other things. I think
it'd be acceptable to allow other packages through proposed despite a
MySQL dep8 test failure. We'll hold MySQL until its own dep8 failures
are fixed.

[Martin Pitt (pitti)]

> I think it'd be acceptable to allow other packages through proposed despite a MySQL dep8 test failure

There should not be any others, as mysql-5.7 only exists in xenial-proposed, and we test packages against xenial-release as much as possible.

[Lars Tangvald (lars-tangvald)]

Fixing the header issue for dsyslog revealed it also has the libmysqlclient_r issue

[Launchpad Janitor (janitor)]

This bug was fixed in the package mythtv - 2:0.28.0+fixes.20160325.2520617-0ubuntu3

---------------
mythtv (2:0.28.0+fixes.20160325.2520617-0ubuntu3) xenial; urgency=medium

  [ Nishanth Aravamudan ]
  * Update to PHP7.0 dependencies (LP: #1562184).
  * debian/patches/update_mythweb_for_php7.patch: Update documentation
    and nginx configuration for php7.0.
  * Use dpkg-maintscript-helper to move the apache conf files.

  [ Steve Langasek ]
  * Use debian/mythweb.maintscript and dh_installdeb for the
    dpkg-maintscript-helper bits, to avoid code duplication everywhere.

 -- Steve Langasek <email address hidden> Wed, 30 Mar 2016 18:03:18 +0000

[Robie Basak (racb)]

This was fixed in Xenial.