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 empty
and non-empty root passwords have been tested (see Sect. 3 for
details).
An important distinction between starting at Ubuntu 12.04 and Ubuntu
14.04 is that when upgrading from 12.04, 14.04 will have Linux kernel
3.13, but if we start with a fresh install of 14.04, the Linux Kernel
will be version 4.2. This affects apparmor behavior.
There are 6 upgrade paths:
* P1 Ubuntu 12.04 to 14.04 with MySQL 5.5 to 16.04
* P2 Ubuntu 12.04 to 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
* P3 Ubuntu 14.04 with MySQL 5.5 to 16.04
* P4 Ubuntu 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
* P5 Ubuntu 14.04 with MySQL 5.6 to 16.04
* P6 Ubuntu 15.10 to 16.04 with non-empty root password
* P7 Ubuntu 15.10 to 16.04 with empty root password
3.1 Ubuntu 12.04 to 16.04
-------------------------
Ubuntu 12.04 should be upgraded to 16.04 in a two step process, first
upgrading to 14.04 and then to 16.04 [5].
Ubuntu 12.04 ships with MySQL 5.5, while 14.04 ships with both 5.5 and
5.6, so there are two upgrade paths, one that stays on MySQL 5.5 until
upgrading to 16.04, and one that upgrades to MySQL 5.6 in 14.04.
3.1.1 P1 Ubuntu 12.04 to 14.04 with MySQL 5.5 to 16.04
------------------------------------------------------
* Install MySQL 5.5 on Ubuntu 12.04, and enter an empty root password
* Perform the testing of the Sakila Example Database
* Upgrade Ubuntu to 14.04
* Upgrade Ubuntu to 16.04
* Perform the testing of the Sakila Example Database
3.1.2 P2 Ubuntu 12.04 to 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
-------------------------------------------------------------------
* Install MySQL 5.5 on Ubuntu 12.04, and enter a non-empty root password
* Perform the testing of the Sakila Example Database
* Upgrade Ubuntu to 14.04
* Upgrade MySQL to 5.6 using the mysql-server-5.6 package
* Upgrade Ubuntu to 16.04
* Upgrade MySQL to 5.7 using the mysql-server package from the PPA
* Perform the testing of the Sakila Example Database
3.2 Ubuntu 14.04 to 16.04
-------------------------
Ubuntu 14.04 can be upgraded directly to 16.04.
Ubuntu 14.04 ships with both MySQL 5.5 and 5.6, so there are three
upgrade paths: one that starts with MySQL 5.5 and goes directly to
16.04, one that starts with MySQL 5.5 and then upgrades to MySQL 5.6
before upgrading to 16.04; and one that starts on MySQL 5.6 and then
upgrades to 16.04.
3.2.1 P3 Ubuntu 14.04 with MySQL 5.5 to 16.04
---------------------------------------------
* Install MySQL 5.5 on Ubuntu 14.04, and enter a non-empty root
password
* Perform the testing of the Sakila Sample Database
* Upgrade Ubuntu to 16.04
* Perform the testing of the Sakila Sample Database
3.2.2 P4 Ubuntu 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
----------------------------------------------------------
* Install MySQL 5.5 on Ubuntu 14.04, and enter an empty root password
* Perform the testing of the Sakila Sample Database
* Upgrade MySQL to 5.6 using the mysql-server-5.6 package
* Upgrade Ubuntu to 16.04
* Upgrade MySQL to 5.7 using the mysql-server package from the PPA
* Perform the testing of the Sakila Sample Database
3.2.3 P5 Ubuntu 14.04 with MySQL 5.6 to 16.04
---------------------------------------------
* Install MySQL 5.6 on Ubuntu 14.04 using the mysql-server-5.6
package and enter a non-empty root password
* Perform the testing of the Sakila Sample Database
* Upgrade Ubuntu to 16.04
* Upgrade MySQL to 5.7 using the mysql-server package from the PPA
* Perform the testing of the Sakila Sample Database
3.3 Ubuntu 15.10 to 16.04
-------------------------
Ubuntu 15.10 can be upgraded directly to 16.04.
Ubuntu 15.10 ships with MySQL 5.6, so there is only one upgrade path.
However, we test it twice to cover both empty and non-empty passwords
for the "root" account.
3.3.1 P6 Ubuntu 15.10 to 16.04 with non-empty root password
-----------------------------------------------------------
* Install MySQL 5.6 on Ubuntu 15.10, and enter a non-empty root
password
* Perform the testing of the Sakila Sample Database
* Upgrade Ubuntu to 16.04
* Perform the testing of the Sakila Sample Database
3.3.2 P7 Ubuntu 15.10 to 16.04 with empty root password
-------------------------------------------------------
* Install MySQL 5.6 on Ubuntu 15.10, and enter an empty root password
* Perform the testing of the Sakila Sample Database
* Upgrade Ubuntu to 16.04
* Perform the testing of the Sakila Sample Database
4 Test results
==============
A test is passed if the upgrade process succeeded with no errors,
mysql_upgrade was run successfully, all users created at the beginning
are still valid, and the database data is intact (tested by running
the example queries). If the original root user had a blank password,
'root'@'localhost' must have the auth_socket plugin enabled.
4.1 P1 - Ubuntu 12.04 to 14.04 with MySQL 5.5 to 16.04
------------------------------------------------------
Passed with packages from [4] with the dependency fix. Not tested with
packages from [3].
4.2 P2 - Ubuntu 12.04 to 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
-------------------------------------------------------------------
Passed with packages from [4] with the dependency fix. Not tested with
packages from [3].
Since MySQL 5.6 is not default in 14.04, it doesn't provide the
mysql-server metapackage. Therefore, a manual upgrade to 5.7 is
necessary after Ubuntu release upgrade. This is as expected.
4.3 P3 - Ubuntu 14.04 with MySQL 5.5 to 16.04
---------------------------------------------
Failed with packages from [3]. There was no apparmor dependency, so
apparmor was upgraded after MySQL, but the MySQLl service requires
socket access that isn't supported by the apparmor version in
14.04.
Passed with packages from [4] with the dependency fix.
4.4 P4 - Ubuntu 14.04 with MySQL 5.5 to MySQL 5.6 to 16.04
----------------------------------------------------------
A critical bug was found in the 5.6 packages that will cause the
upgrade process to hang (an infinite loop in the 14.04 MySQL 5.6
package's systemd script combined with the apparmor issue noted
above). This is Launchpad bug 1478849 (fix released, but not in
14.04).
A workaround is to use apt-mark hold to freeze the version of the 5.6
packages, preventing them from being updated. Presumably it won't be
an issue if 5.6 in 16.04 is replaced with 5.7, but this is something
that would need more testing.
After working around this issue, installing mysql-server upgrades the
database successfully.
4.5 P5 - Ubuntu 14.04 with MySQL 5.6 to 16.04
---------------------------------------------
Same result as P4.
4.6 P6 - Ubuntu 15.10 to 16.04 with non-empty root password
-----------------------------------------------------------
Passed with packages from [3].
Passed with packages from [4] with the dependency fix.
P7 Ubuntu 15.10 to 16.04 with empty root password
-------------------------------------------------
Passed with packages from [4] with the dependency fix. Not tested with
packages from [3].
5 Conclusion
============
Three issues were identified.
1. One issue (missing dependency on apparmor) was found and fixed.
2. One potentially critical issue exists when upgrading from Ubuntu
14.04 with 5.6 to Ubuntu 16.04 with 5.6, but only with the newer
kernel version (4.2) that a fresh install of Ubuntu 14.04.4
provides. This is a problem if 16.04 ships MySQL 5.6. If 5.6 is
replaced with 5.7 in 16.04, this will be fixed (should be verified
by further testing).
3. One potential issue is that the output from mysql_upgrade is
printed to the console during upgrade, this could potentially be a
lot of output depending on the size of the database, so it may be
an idea to hide the output.
Both issues 1 and 2 will be fixed by upgrading to MySQL 5.7 packages as
provided by [4] and dropping MySQL 5.6 in 16.04.
Issue 2 must be fixed in 16.04, either by updating dependencies in 5.6
packages, or by upgrading to 5.7.
Issue 3 is not critical.
6 References
============
[1] https://dev.mysql.com/doc/sakila/en/
[2] https://dev.mysql.com/doc/sakila/en/sakila-usage.html
[3] https://launchpad.net/~mysql-ubuntu/+archive/ubuntu/mysql-5.7/+packages
[4] https://launchpad.net/~racb/+archive/ubuntu/experimental
[5] https://help.ubuntu.com/community/UpgradeNotes
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
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.6/+bug/1528583/+subscriptions
--
ubuntu-bugs mailing list
ubuntu-bugs@lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
