Uninstall mysql and reinstall...and mysql 5.7

We usually shut down MySQL, delete /usr/local/mysql and /Library/Preferences/MySQL.prefPane and /Library/StartupItems/MySQLCOM. And forget all MySQL related receipts. Then follow JAMF's install/configure steps for MySQL and the jamfsoftware database.

Has anyone tested 5.7 yet? 5.5 is stable and I've seen some threads about 5.6 where folks ended up moving to it and then reverting back (which ain't pretty). :)

We have been running version 5.6.14 since October or so. I think it has a few issues. At some point I'd like to move to something else. It hasn't been that bad so I didn't really want to try downgrading. I hadn't heard about 5.7. Will be interesting to hear about how it works for you and others.

@donmontalvo are those instructions for moving from one version of MySQL to another ?

@rcorbin][/url][/url Those are the steps we usually follow to wipe MySQL off a test box, but most of the time doing a DROP DATABASE jamfsoftware; is all we need to do to purge and then reimport. From what I read, MySQL 5.7 hasn't been released yet, but yea, wow, 2x speed increase, wow.

http://www.cio.com/article/750494/Oracle_Doubles_the_Speed_of_MySQL_Query_Handling

PS, I'd still like to see MSSQL support. ;)

From #mysql IRC:

[13:38:47]  <donmontalvo>  Does anyone know when MySQL will be EOL'd?
[13:38:56]  <donmontalvo>  ...er, MySQL 5.5.x :)
[13:39:09]  <ebergen>  haha
[13:39:15]  <donmontalvo>  (Sorry!)
[13:39:17]  <ebergen>  probably a few years
[13:39:46]  <donmontalvo>  We are building a Casper Suite (JSS) server, I was asked to provide estimated EOL for 5.5, didn't see anything at oracle.com site.
[13:42:01]  <ebergen>  probably 10 years from when 5.5 when GA
[13:42:11]  <ebergen>  so 2021?
[13:42:20]  <ebergen>  I mean it depends on how much you want to pay oracle
[13:47:11]  <donmontalvo>  ebergen: Thanks, I'll pass this along to the team, so they can reach out to Oracle.
[13:47:38]  <ebergen>  most of the world is still running 5.1 so I wouldn't worry too much about it
[13:47:56]  <donmontalvo>  ebergen: Wow, well, that's good to know. :D
[13:48:12]  <ebergen>  databases don't move that fast :)
[13:48:38]  <donmontalvo>  ebergen: I like slow. ;)

So @donmontalvo to update MySQL you just shut down MySQL, delete /usr/local/mysql and /Library/Preferences/MySQL.prefPane and /Library/StartupItems/MySQLCOM. Then re-install MySQL according to Jamf's instructions ? I'm still running that 5.6.14 version on my production server. Slightly newer versions seem to run better on test servers. I've never tried an in place
update of MySQL on a production server.

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.

A description : "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 great so I will probably run with it for now until they fix the bug. Then I may look at upgrading MySQL.

It looks to me like 5.7.x is now the current release version.

https://dev.mysql.com/downloads/mysql/

Has anyone tried it yet ?

I tried it. I recommend against it.

Environment:
- JSS 9.81
- OS X 10.10.5
- MySQL 5.7.10
- JDK 8u66 + Crypto extensions

The two issues I ran into are:

1. Policy logs aren't handled properly. Policies themselves continue to run, but you can't get accurate information from the policy's logs. This impacts dashboard displays -- all policies will show 0 completed, 0 failed, and the full count of scoped devices as pending (and therefore, a solid yellow pie chart). Viewing a policy's log from the "Logs" button on the policy's page will accurately show the pending devices, but nothing else. If no devices are actually pending, the list is empty.

2. MySQL stops accepting logins. This affects the JSS Database Utility and logins via the command line. Command line logins will report "ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 102", and the JSS Database Utility will report a similar error. You can unload and reload the LaunchDaemon at /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist to work around this.

I spent a few days working with JAMF Support on this, and we tried various things. Support finally recommended going back to 5.6. I backed up the jamfsoftware database and uninstalled MySQL 5.7.10, then installed MySQL 5.6.28 and restored the database. The JSS and I are both much happier.

I have to admit, some time ago we reverted to MySQL 5.5.34 and just recently, downgraded from Java JDK 8u60 to JDK 7u79 to clear up a significant issue we were having with blank APN pushes. We are also running JSS 9.81 until I can finish testing 9.82 (on OS X 10.9.5)

I expect official MySQL 5.7 support for Jamf Pro in the early part of 2017.

Regarding policy logs, on MySQL 5.7, on some platform default installs, the ONLY_FULL_GROUP_BY variable is added by default, which we have confirmed causes issues. We have found success if we remove that option from the ‘sql_mode’ options in the my.cnf.

Here are steps:

1.) Log into MySQL and run:
show variables like 'sql_mode';

2.) Copy out the string that is returned (it will be something like 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION')

3.) Remove the ONLY_FULL_GROUP_BY from the string, copy the rest of it to a clipboard.

4.) Edit the my.cnf file, so that the sql_mode variable under [mysqld] includes the information you copied (not including the ONLY_FULL_GROUP_BY part)

5.) Your my.cnf entry will look something like:
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

6.) Restart MySQL