Database Connection Error

[ClaphamPrimary]

Hi All,

Came in today to find that the Mac hosting our JAMF Pro had done an automatic update and was sitting on the latest MacOS 10.14.3. JSS version is v10.9.0

MySQL had been left waaaaay behind on 5.6.39 which I understand is no longer supported (According to all documentation) ..... so I carefully followed the notes to upgrade it to 5.7.25.

All went absolutely fine, upgrade completed, server had a restart, JAMF was up and running and iPads were checking in - Great!

In preparation for the upgrade up to 10.10.1 (which was my main aim for today) I carefully read through the instructions and release notes regarding the database upgrade and noticed that MySQL 8.0 is now supported and recommended for use.

As the 5.6 >> 5.7 upgrade had just gone smoothly, I thought to myself "Why not go up to 8.0.15?" before I do the intended 10.10.1 upgrade? Seemed like a good idea at the time .......

Followed article to do preliminary checks for 5.7 > 8.0 upgrade.

https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

No errors found, check passed

Running the MySQL 8.0.15 installer , chose "Legacy Authentication" option when prompted as per JAMF article:-

https://www.jamf.com/jamf-nation/articles/630/configuring-mysql-8-0-for-jamf-pro

Annnnnnnnnnnd.............. Now I can't get into JAMF! Attempting to login gives me a database connection error and I don't know what to do next!

Also, I like that the new MySQL Preference Pane is a bit more functional now, but the "Stop / Start" buttons don't always work properly (I had this issue back on 5.6 aswell)

Any suggestions??

General Specs Again:-
mySQL: 8.0.15
macOS: 10.14.3
JSS: 10.9.0 (with eventual upgrade to 10.10.1 planned once these issues are resolved)
Java: 8 Update 201

[ClaphamPrimary]

Further information from JAMF page attempting to login:

The following error was encountered:
SQLException: Access denied for user 'jamfsoftware'@'localhost' (using password: YES) Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding' - An error occurred while connecting to the database.

Please check the following items:
Verify the username and password, then try again
MySQL is running on localhost
MySQL is accepting network connections
The user jamfsoftware@localhost has been granted privileges to the jamfsoftware database

[ClaphamPrimary]

Update: Reverted back to MySQL 5.7 and JAMF 10.9.0 and restored database backup

If anybody has any suggestions as to why MySQL 8.0 and the database upgrade didn't work properly ideas would be greatly appreciated.

and my devices are no longer checking in it seems???

[jared_f]

@ClaphamPrimary I ran into a similar issue with MySQL 8 when bringing up a new instance of Jamf Pro. Apparently when I created the database, I accidentally used the unicode command line client instead of the regular MySQl CL client. I was running into the same issue as you did. I ended up just using the MySQL commands to wipe out the Jamf Pro database and re-created it in the other client.

Not sure if what we experienced are similar issues, but I am now running on MySQL 8 without any problems.

Jared

[jared_f]

@ClaphamPrimary Forgot to mention, I am assuming you converted to InnoDB?

[jorice]

After installing mySQL 8:

If you haven't already, stop jamf-pro server services again. jamf-pro server stop

1) vim /etc/my.cnf add this line in the my.cnf file or unhash it if it's already there, save and quit.

default-authentication-plugin=mysql_native_password

2) Restart mysql: centOS 7 users

systemctl restart mysqld

3) log into mySQL; mysql -u root -p

4) run the command below

alter user 'database user name'@'localhost' identified with mysql_native_password BY 'password';

After altering the user flush your privileges to save your changes

flush privileges;

exit out of mySQL

5) start jamf-pro server services; you should be able to connect.

jamf-pro server start

Ref this jamf article below https://www.jamf.com/jamf-nation/articles/630/configuring-mysql-8-0-for-jamf-pro

Thanks - John

[andy_granger]

Thank you @jorice! This just saved me countless hours in rebuilding my Jamf Pro 10 development instance.

[snovak]

@jorice Save my bacon too, thanks!

[jorice]

@snovak @andy.granger Happy to help.