MySQL 5.6 upgrade tips for Ubuntu 12.04 Precise

(Wow, 1 year to the day from last post. How funny. Been too busy this last year to write up anything!)

These tips are only based on issues I’ve run into on my development server.  Currently we have a MySQL 5.5.x Ubuntu standard package running in production on Ubuntu 12.04.  That’s getting pretty old!  And right now I’m trying to solve some issues in our apps and wanted some extra features, plus I wanted to start testing 5.6+.  I originally tried MariaDB, but this was a complete fail and broke everything.  I basically had to purge all mysql* and mariadb* packages with apt-get just to get it to run, but then nothing worked.  Rather than fight with that, for now I decided to just upgrade Mysql.  Eventually, we’ll be moving to Centos 7 and Mariadb, but we’ve got a lot of backend scripts, ODBC, and other apps that need to be tested.

Currently I have 5.6.29 installed and it works.  I can’t get my PHP stuff to work right yet, but I think its because of the custom “datadir” directive I have set.  We run our installation in /data/mysql  (right or wrong, that’s what we’ve done forever, so I need mysql to bend to my will on this one, though this does make things more difficult).

The PPA’s out there don’t seem to support 12.04 Precise anymore, which sucks, but I understand.  I mean, its an LTS, still supported! But, I’m not using the supported version by Ubuntu either, so, my bad.  I didn’t even know this, but Mysql has APT packages and repo’s you can use, so I went with that.  Use their guides, its easy.  Basically you get a .deb package you run with “dpkg -i” and it will ask a few questions about which version you want, etc.

First thing.  I can’t get my datadir to work.  The mysql installer package and startup scripts are ignoring my datadir in /etc/mysql/my.cnf.  Also, tip for anyone out there: Mysql’s installer overwrites the apparmor profile setup for usr.sbin.mysqld without any backup of it, and if you run a custom data directory like we do, you’ll need to reset any apparmor paths with the correct perms.

I  found a post/bug report on this.   https://bugs.mysql.com/bug.php?id=68807
So I proceeded to try this:

mysql_install_db –keep-my-cnf –datadir=/data/mysql –defaults-file=/etc/mysql/my.cnf

That didn’t work either!!

Gave up on that and added the datadir directly into mysqld_safe script. Which I don’t like, but I don’t feel like solving this problem for the time being.

Ok, next, I find out that Mysql still will  not launch.  I ran into an issue where its requiring AIO on the file system. (I have ZFS)  There’s a config option to disable it, but again, I’m finding many of my parameters in my.cnf are being ignored.  (  innodb_use_native_aio=0 )

Time to give up. I’d like to keep working on this, but I have more important things to do. Except, maybe we’ll try MariaDB 10.1 once more.  This time I left my.cnf alone and used their repo configurator for Precise.  After the easy install, it launched, right from my settings in my.cnf and using my datadir!   Yay!   Looks like my web is slightly faster now too!  Bonus!  And best thing of all, I can see my entire logged query that was failing, which was truncated in 5.5.  And even better, in minutes I had my app issue fixed.

All seems great, but I do have one problem, which doesn’t need to be address yet.  Looks like something in the old ODBC drivers is broken because our old Cobol app isn’t segfaulting now.  (I probably purged all those links and files I had during the process above)

In the end, I guess I didn’t have much in the way of tips for this upgrade.  Actually, my ultimate goal is to move everything to MariaDB anyway, and also upgrade from Ubuntu Precise to CentOS 7 some time this year.