JSS – Troubleshooting MySQL Connection Errors

23 Dec JSS – Troubleshooting MySQL Connection Errors

Overview

On our JAMF Software Server (JSS) we noticed and error occurring during scheduled JSS database backup using the JSS Database Utility. The bug is in the implementation of the MySQL binary running on OS X, and isn’t seen on other platforms like Windows or other Unix servers. The Symptoms include, under heavy work loads we see the error: “Lost connection to MySQL server at ‘sending authentication information’, and system error: 32”. Our server is running, JAMF Software Server (JSS) 9.82, OS X 10.10.5 “Yosemite” with server application version 5.0.15, and MySQL MySQL version 14.14 distribution 5.6.24.

JSS Database Utility Error
The following error will occur when trying to do a manual backup of your JSS database using the “JSS Database Utility”:

JSS Database Schedule Backup Error
Also, you will get errors with scheduled backups:

MySQL Logs
By default, MySQL, no logs are enabled, except the error log on Windows. The following web page outlines the process of enabling MySQL logging:

http://dev.mysql.com/doc/refman/5.7/en/server-logs.html

And JAMF Software doesn’t recommend enabling MySQL logging by default, but only temporarily for troubleshooting purposes, since it may take up a significant amount of disk space without a script to rotate/remove old logs.

http://dev.mysql.com/doc/refman/5.7/en/log-file-maintenance.html

JSS Database Utility Logs
The JSS Database Utility logs are located here:

/Library/JSS/Logs/backupDatabase.log

These logs only appear to output information when the databases are backing up, and this MySQL prevented backup.

MySQL Authentication Error
The issue was repeatable with a simple database import through the mysql client you get the authentication error.

# mysql -u root -p
Enter password: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

MySQL Exception
Also, passing in the host causes MySQL to throw a different exception:

# mysql -u root -p -h 127.0.0.1
Enter password: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

Restart MySQL
A temporary workaround to make a manual JSS database backup is restarting MySQL:

JSS Database Utility
Using the JSS Database Utility, select the “Restart MySQL…” command from the “Utilities” menu.

Command Line
On OS X to restart MySQL pre 5.7 from the command line:

sudo /usr/local/mysql/support-files/mysql.server restart

On OS X Yosemite/El Capitan to restart MySQL post 5.7 from the command line:

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Then proceed with the manual backup using the JSS Database Utility’s “Save Backup Now…” option.

Workaround

On workaround that might resolve the issue is to edit the configuration of the “my.cnf” file.

This file could be in two different locations:

/etc/my.cnf

or

/usr/local/mysql/my.cnf

Open up the “my.cnf” file with a text editor, here is the default configuration for MySQL 5.6:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Under the |mysqld| section, add the following three variables:

innodb_file_per_table = OFF
table_definition_cache = 400
table_open_cache = 400

For example:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_file_per_table = OFF
table_definition_cache = 400
table_open_cache = 400

Then restart MySQL using the JSS Database Utility or command line noted above and test modifications.

Our testing has shown these steps will allow MySQL to resume successful backups.

MySQL Bug

This appeared tied to the following MySQL bug:

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

If you are seeing this issue, please report a confirmation with details.