14.04 to 16.04 *KILLED* mysql

Question

Definition: trust - Something that the Ubuntu LTS packagers have lost from me.

I scrupulously maintained my 14.04 LTS web server machine. It only had four primary functions (ssh, ftp, http, and mysql (for use by web pages)). Before I started my release upgrade from 14.04 to 16.04, I made sure that both

 sudo apt-get update
 sudo apt-get upgrade
 sudo apt-get dist-upgrade

to be absolutely certain I had the latest 14.04 packages before trying to upgrade the release. Once that was done, and the system was rebooted (and ran the above commands again, to be sure they didn't see anything new), just to be safe, I ran 'sudo do-release-upgrade -d' to go from 14.04 to 16.04.

Everything we quite smoothly of the previous two systems upgraded the same way, but the web server upgrade hung during the mysql part and it appears to have destroyed any ability I have to run mysql on the system. Period.

From what I've read, people are STRONGLY recommended to upgrade mysql 5.5->5.6->5.7, because jumping directly from 5.5 to 5.7 can cause some table compatibility problems. Unfortunately, my (fully up-to-date, remember) web server was still running mysql-5.5.

I have copies of /var/lib/mysql that I captured while do-release-upgrade was hung, but mysql-5.7 AND mysql-5.6 both refuse to use them. It appears to be impossible to install mysql-5.5 on Ubuntu 16.04 in order to manually step through the releases. I tried getting the packages from the mysql web site, but I can only find mysql-server there, not -client, -common or anything else.

So, the 14.04 to 16.04 upgrade appears to have destroyed my database. Yes, I know, not having a current backup is my fault, but after two flawless upgrades, and under a deadline (that has evaporated, since the database seems to be gone for good, now) I was too comfortable -- I had trust in the people who built the upgrade. I know I should check in advance for anything that might break but, honestly, I thought that was the packager's job. Saying the customer should check everything is like saying a car buys should have the car disassembled and every part checked for flaws prior to purchase -- it ain't gonna happen.

If there is any way to return to mysql 5.5 on Ubuntu 16.04 so that I can run through the CORRECT/RECOMMENDED (by mysql) upgrade procedure, please let me know. If not, does anyone know of a good primal scream therapy group in Central Florida?

UPDATE: I've managed to get the database in good working order again by building a virtual 14.04 server and repairing/rebuilding the database files on it. I successfully upgraded the virtual 14.04's mysql from 5.5 to 5.6. Next, I need to learn what I must do in order to be sure the 5.6 to 5.7 upgrade works.

UPDATE 2: Despite the scoldings, I've managed to get my repaired database to work under mysql 5.6 on the upgraded system. Now I'm getting errors because PHP support for mysql is not installed. They may be baby steps, but it is progress!

Update 3: SUCCESS! I reinstall all of the php7 /mysql packages and made sure my web app was using the mysqli database type and, voila! I think I'll stay at mysql 5.6 for a while, though. Until I'm a bit more comfortable with the upgrade path to 5.7.x. Hopefully, this may provide a cautionary tale to others like me -- but if they're too much like me, then they won't see this until it is too late.

Solution

This happens because upgrading from 14.04 to 16.04 causes an unsupported mysql upgrade (5.5 -> 5.7), which is not a supported upgrade path as documented by mysql. The easiest way around this is to upgrade mysql to 5.6 whilst still under 14.04. You achieve this by first (of course!) dumping all your databases;

mysqldump --lock-all-tables -u root -p --all-databases > backup.sql

then upgrading to mysql 5.6;

apt-get install mysql-server-5.6 mysql-client-5.6 mysql-server-core-5.6 mysql-client-core-5.6

This way, all your databases are upgraded in place and (on my machines so far) entirely safely and transparently to 5.6. The only issue after the upgrade may be TIMESTAMP entries. Now, after the do-release-upgrade, mysql is still left at 5.6 and the issues surrounding the non-supported upgrade from 5.5 directly to 5.7 during the release upgrade never even surface.

Comments

[You'reAGitForNotUsingGit]

FYI: 14.04 --> 16.04 isn't a supported upgrade path...

[Arronical]

Hopefully you'll find something useful in this question askubuntu.com/q/760724/320386

[Steve Valliere]

Since when is moving from LTS release to the next LTS not a supported path? I thought that was the entire point of the things!

[Steve Valliere]

@Arronical : that was one of the many pages I found in my earlier searches. I followed every one of the procedures referenced and the BEST I could get was that the mysql.user table was damaged, probably because it never got upgraded through mysql-5.6 (based on other things I've been finding)

[dadexix86]

@SteveValliere it is written in the ReleaseNotes wiki.ubuntu.com/XenialXerus/… that you should have read before upgrading :S

[Raga]

@AndroidDev 14.04->16.04 isn't yet supported. See askubuntu.com/questions/125392