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