Any Compatibility Issues with MySQL 5.6 GA

I just built a JSS today and it seems to run fine with MySQL 5.6.10 Community Server.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/

Don

As it turns out 5.6 enforces strict mode. Might want to check with your JAMF Buddy if you're on 5.5 and considering going to 5.6.

Cool. Strict mode can be disabled:

SET GLOBAL innodb_strict_mode=off

However, JAMF is recommending SQL 5.5 for the 8.6X series.

In our testing environment running MySQL5.6 the JSS backup utility fails at restoring the database

We've been able to point out enough issues that we're going back to 5.5 today. FYI

Is it possible to roll back an existing database from 5.6.x to 5.5.x?

Don

That's a great question. I'm running a mysqldump at the moment. I've seen threads where folks talk about that working, but I should be able to let you know if something get's nuked shortly.

Here's Oracles documentation on it
http://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

From my favorite JAMF support guy Mitch:

Thanks for reaching out. I have some information here on removing MySQL altogether, if we need to install a different version (as I've walked through this with a few other customers):

** DO NOT PROCEED IF THERE IS NOT A USABLE DATABASE BACKUP. **

1) Make sure to stop all MySQL processes. Run the following command from Terminal:
ps auxww | grep mysql
If there are no running processes, the output will look similar to this with only the process of you grepping for mysql:

If there are processes that are running, we'll want to quit these processes:

We will want to kill these processes by running the following command and replacing XXXXX with the PID of the process (the second item in the ps output):
sudo kill -9 XXXXX

For example, from the screenshot above (see screenshot), we would run the following two commands:
sudo kill -9 42741
sudo kill -9 43023
2) Delete all known MySQL files. Run all the commands below from Terminal:
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*
sudo rm -rf /var/mysql
sudo rm -f /etc/my.cnf
sudo rm -f /usr/bin/mysql*

3) Remove MySQL from any startup processes or launchd items.
- edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
- Search the following locations for anything with mysql in the name and delete it
- /Library/LaunchDaemons
- /Library/LaunchAgents
- /System/Library/LaunchAgents
- /System/Library/LaunchDaemons

4) Restart the server
5) Run "ps auxww | grep mysql" again to double check that it is not running at all
6) Download and install the 5.5 MySQL. (for Mac, use the 10.7 DMG 64 bit download, which is compatible with 10.8 and 10.9 as well)

Oh, and yes the DB should work just fine assuming that you run mysqldump. I'm not sure about the JSSDatabaseUtilities backup if you use that.

@nicktong][/url wrote:

Cool. Strict mode can be disabled:

SET GLOBAL innodb_strict_mode=off

However, JAMF is recommending SQL 5.5 for the 8.6X series.

https://jamfnation.jamfsoftware.com/article.html?id=318

Yep. Though it would have been better if they did say 5.5 or newer in their documentation ;-)

Page 13 now says 5.5 or later:

http://www.jamfsoftware.com/sites/default/files/Casper_Suite-9.2_JSS_Installation_Guide_for_OSX.pdf

Very true... I apologize, I meant "Though it would have been better if they DID NOT say 5.5 or newer." In any event, perhaps it isn't even my issue. We've officially downgraded to 5.5.35 and are restoring the database now. We will see if our performance issues are resolved.

I also forgot to mention in this thread that we have been running 9.2

Alright. went through everything last night to bring the MySQl database down form 5.6.14 to 5.5.35. The instructions posed above worked quite well though you have to do a few extra things like copying the my-huge.cnf to /etc/my.cnf and resetting various memory settings and thread counts.

It was my impression that MySQL 5.6.16 was OK to use with Casper Suite 9.24 and so I upgraded six days ago. All Casper Suite apps and services appeared to function, except the nightly automatic backups would fail with the following error:

Encountered an error during package_receipts repair: Warning: Using a password on the command line interface can be insecure.ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

I noticed that the process for the backups via JSSDatabaseUtility repairs each table before backing up. Some tables would repair in seconds and others would take over 2 hours before the backup would fail and exit.
JAMF support suggested, adjusted the flushing database schedule, backup times, increased the max packet size a couple of times, and it would fail more often than desired. Yesterday, I downgraded MySQL from 5.6.14 to 5.5.36 and last nights backup was good!

On a few occasions, I was able to use the JSSDatabaseUitility to get a backup. The best chance for it to work was if I did it immediately after a mysqlcheck optimize - thanks Rich @rtrouton and his post on http://derflounder.wordpress.com/2014/02/01/fixing-caspers-mysql-database-with-mysqlcheck/

The directions posted by @Chris_Hafner were very helpful. The only extra step I took was to turn off tomcat so that clients would not connect. I think the following is the correct way to turn off tomcat via command line for Mac OS X 10.9 with Casper Suite 9.24. If you know how to shutdown tomcat, please verify the following.

sudo launchctl unload /Library/LaunchDaemons/com.jamfsoftware.tomcat.plist

sudo ./Library/JSS/Tomcat/bin/shutdown.sh

This is scary...me thinks MySQL 5.5.x is the safest route.

Unless there's a compelling reason to move to 5.6.x.

Don

Our nightly backups are now failing as well. How can we verify our version of MySQL?

If you do a JSS summary, it should tell you the version of MySQL.

Settings->Information->JSS Summary->Check All->Create:

Look for something like:
version .................... 5.5.41-log

you should be able to check by calling mysql and then asking for the version

mysql -V

however, some installations will vary depending on the location of MySQL

@jhalvorson Your post sounds exactly like an issue we were having with running back ups.

Recently I found that some of my MySQL problems were being caused by a bug in MySQL 5.6.13 and higher. The main times I saw this was when using the JSS Database Utility. My backups would not run consistently or I would get messages that I could not connect to the database from the utility. In fact I got that same "ERROR 2013 (HY000): Lost connection to MySQL server" that @jhalvorson talks about.

A description of the bug. : "Under heavy workload mysql 5.6.16 (replicated on 5.6.13 and higher) throws connection errors "Lost connection to MySQL server at 'sending authentication information', system error: 32". This was tested on OSX 10.8 and 10.9. The issue is not seen on windows or other unix servers like centos."

http://bugs.mysql.com/bug.php?id=71960

A further description of this issue :

http://stackoverflow.com/questions/17813630/mysql-5-6-headaches-on-mac-osx

Jamf Support had me add the line : table_open_cache = 512 to the my.conf file

It totally fixed the issue. My guess is that you might not see it on a JSS that doesn't have as many clients or isn't that busy. Ours has about 8,000 computers and 2500 mobile devices so the database is fairly large.

The other solution is to go back to MySQL 5.5.x. I didn't really want to move backwards so I went this this fix for now. It seems to work just fine so I'll run with it for now until they fix the bug and then I'll look at upgrading MySQL.