Database backup/connection issues

[smith_kyle]

I have been having issues backing up my database for some reason, which I noticed when I tried to manually back it up before upgrading to 9.5 yesterday. It actually turns out that the auto backups, which were set to run nightly at 2am, had actually been failing for the past two weeks almost with an "unable to connect to database" error in the database backup log. The JSS has been functioning fine though without any issue since then, so I wasn't sure why the database utility would have trouble backing it up. Running Mavericks 10.9.5 and OSX server 3.2.1 with JSS 9.5 currently (first ran into issue with 9.4 yesterday).

I also kept getting the following error when trying to back it up:

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

Restarting the mysql server from the database utility seems to let the backup run properly after, but that error has appeared fairly regularly and I don't know why.

Also, going to be adding a feature request shortly to notify admins if database backups are failing...wasn't a great feeling when I thought my last resort solution was to restore from a backup...only to find there were no current backups.

Kyle

[smith_kyle]

Just tried another manual backup, same error (this time in its entirety), although different table name I think:

encountered an error during autorun_exceptions 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

Feature request for database backup notifications is here:

https://jamfnation.jamfsoftware.com/featureRequest.html?id=2651

Kyle

[smith_kyle]

The JSS already has backup notifications...ignore the feature request...If anyone in the future is looking for it, it's under your JSS account in the Notifications section - it's not a system-wide setting.

[were_wulff]

@smith.kyle

Typically errors with backups while show something a bit more useful in the backupDatabase.log which is located in /path/to/JSS/logs by default.

The most common causes of lost connections during backups are:

- Excessively large table sizes.

- Table corruption.

- max_allowed_packet variable for MySQL is too small.

- Tomcat is still running and log flushing has kicked off; if the table is in use, the utility may have trouble locking it so it can make a backup. This can be avoided by making sure there is at least a 2 hour gap between when the automatic backups run and when automatic log flushing runs.
By default, both are set to midnight.

Sometimes, errors to these effects show up in that backupDatabase.log file.

We can check table sizes or look more closely into what may be causing the issue by taking a look at a full JSS Summary, but that is something you’d want to get in contact with your Technical Account Manager to get a case going for, as it tends to contain environment specific information and we wouldn’t want to have it posted publicly.
You can contact your Technical Account Manager either by giving us a call, sending an e-mail to support@jamfsoftware.com (it will go right to your TAM’s case queue), or by using the My Support section of JAMF Nation.

Thanks!
Amanda Wulff
JAMF Software Support

[smith_kyle]

Will do - thanks!

Kyle

[GabeShack]

We just had this issue as well but found a specific problem with SQL 5.6x and the jss 9.5 or later. (My theory is the new version of tomcat installed with 9.5 conflicts with the newer MySQL.)

After extensive testing with support we found that the only solve was to quit tomcat then the backup goes through without issue.

Still waiting to see what the ultimate fix will be though.

Gabe Shackney
Princeton Public Schools

[curullij]

I'm seeing this issue with JSS 9.93 and SQL 5.6.25
Does anyone have a fix?

[tomt]

Getting the same error when restoring a backup to my test 9.96 JSS.

Newly built 10.11.6 server with latest JAVA and MySQL 5.6.33.