MySQL lessons learned (the hard way): Casper v9.82 upgrade

[mthakur]

Some MySQL lessons learned, the hard way, as I upgraded our Casper installation to v9.82 this past weekend:

• Do NOT upgrade MySQL to v5.7.x for use with JSS if you are restoring data from an older version of MySQL! If you do, the JSS will fail to access data correctly from the MySQL v5.7.x database server. For example, the policy history will be missing or incomplete, and the policy status page will have black pie charts (rather than the yellow/green ones).
  To fix: Use the latest version of MySQL v5.6.x (currently v5.6.28) instead.

• Do NOT attempt to use a filesystem copy (directory to directory) to speed up the MySQL database backup / restore process! Yes, the JAMF-provided (and Java-based) JSSDatabaseUtility is slow as molasses — taking over 90 mins to backup and restore less than half a gigabyte (~460MB) of data — but the utility has the huge advantage that it works.
  To fix: Use JSSDatabaseUtility to backup/restore the MySQL data.

• Do NOT use a forward slash (‘/’) in the MySQL password for the jamfsoftware database in MySQL! If you do, a postinstall script will fail in the JSS installer pkg. This is because the postinstall script invokes the Unix command /usr/bin/sed to edit the database settings, which treats a forward slash in the password string as a field separator — oops — and therefore JSS attempts to login to the database will fail.
  To workaround: Don't use any characters in the MySQL password that sed might interpret as a field separator. Alternatively, edit the /Library/JSS/Tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml file manually.

It took several hours of pulling my hair out to track down the above. Hopefully, this will help others avoid these pitfalls now.

[npynenberg]

Sharing mistakes is aways appreciated. Thanks for taking the time to share your less than optimal experiences!

[DavidSu]

When you did the restore to MySQL 5.7, was that using the file system copy you also mentioned? If so, did you run the mysql_upgrade binary after doing the restore? That would have been required to ensure the data files were 100% 5.7 compatible.

The other thing that I'd call out here is that a filesystem backup/restore can work, but there is one pretty significant caveat with that method and MyISAM tables (which the JSS uses): A global lock on all tables needs to be held or MySQL should be shutdown for the duration of the backup. Otherwise, it can lead to table corruption, which could also explain the issues with the policy data after restore.

[mthakur]

@david.suehring

When you did the restore to MySQL 5.7, was that using the file system copy you also mentioned?

I tried both the file system copy as well as JSSDatabaseUtility. Both failed to restore the MySQL data correctly, i.e. both methods resulted inJSS showed the same missing policy data and black pie charts mentioned above.

If so, did you run the mysql_upgrade binary after doing the restore?

No, I did not attempt to run mysql_upgrade. At that point, I was under pressure to get the server up and running.

MyISAM tables (which the JSS uses): A global lock on all tables needs to be held or MySQL should be shutdown for the duration of the backup.

Thanks for the suggestion, but in my case, MySQL was indeed shutdown during the filesystem copy, so that probably wasn't it.
In the future, I may try MySQL v5.7.x and I may also try a filesystem copy, but for now, sticking with MySQL v5.6.28 was the quickest way to getting the JSS up and running again.

[guidotti]

Thanks for the hard-earned tips.

[jtaveras]

So i think I have this problem. Obviously i was thinking since the post is 10 months old, this issue would have been resolved but its not. My policies are all out of whack. ANy steps to revert back to 5.6 from 5.7 mysql? before i go nuts and break everything

Thanks for the info

My Problem NOW JSS 9.96 mysql 5.7.x
"Do NOT upgrade MySQL to v5.7.x for use with JSS if you are restoring data from an older version of MySQL! If you do, the JSS will fail to access data correctly from the MySQL v5.7.x database server. For example, the policy history will be missing or incomplete, and the policy status page will have black pie charts (rather than the yellow/green ones).
To fix: Use the latest version of MySQL v5.6.x (currently v5.6.28) instead. "

[mthakur]

@jtaveras

I can offer sympathy, but not much else. The only workaround is to uninstall MySQL v5.7.x, reinstall v5.6.x, and then restore your tables from backup.
That's what I did, and I'm still on MySQL v5.6.x.

[DavidSu]

@jtaveras

That looks like a known issue we've seen with the default config of MySQL 5.7. You may be able to workaround that one without needing to try to rollback the MySQL version. Check in your my.cnf/my.ini for a sql_mode parameter. If it's in there, removing the "ONLY_FULL_GROUP_BY" and restarting MySQL hopefully get things working again. Once you've restarted, you can verify that mode is disabled by running a "show variables like 'sql_mode';" in MySQL.

Hope that helps!

[Chris_Hafner]

I also ended up falling back to 5.6. However, I was successful restoring from a mysql dump