MySQL 8.0.11 removed support for old_password

[passcod]

Steps to Reproduce

1. Install MySQL 8.0.11 or above
2. Attempt to install Ghost

This error message:

Message: Creating new MySQL user errored with message:
ER_UNKNOWN_SYSTEM_VARIABLE: Unknown system variable 'old_passwords'

This is because MySQL 8.0.11 removed the old_passwords variable! See here: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_old_passwords

Log file

ghost-cli-debug-2018-10-02T03_29_13_544Z.log

Technical details

• OS: Ubuntu, v18.04
• Node Version: v8.12.0
• Ghost-CLI Version: 1.9.5
• Environment: production
• Command: 'ghost setup mysql'

Bug submission checklist

Please fill out this checklist to acknowledge that you followed the requirements to submit a bug report.

• Tried to find help in the forum & docs
• Checked for existing issues
• Attached log file
• Provided technical details incl. operating system

[passcod]

The PASSWORD() function was also removed.

[acburdine]

@passcod unfortunately we don't support mysql 8.0 at this time. Our documentation doesn't clarify that at the moment and I'll update it, but from the looks of it a decent amount of things were changed/removed in 8.0 that might cause issues not only with Ghost-CLI, but also with Ghost itself (see: mysqljs/mysql#1959)

I'm going to close this issue for now, when we've figured out how exactly to support MySQL 8.0 and we've ensured it works in Ghost itself, we can reopen and fix the issue.

[passcod]

Fair enough. Just another note that we're now running on MySQL 8. It's not in production and we'll migrate down if necessary, but so far there's been no issue. I edited the setup script to skip the separate password hashing to get up and running, but that was it.

[acburdine]

@passcod hmm that’s good to know - thanks for sharing!

[ahrib]

Lack of mysql 8 support is unfortunate - FYI this prevents ghost use/consideration for Australian federal & state government workloads where there is a requirement to use the latest version.

[ej-mitchell]

Hi, @passcod - I am currently running into the same issue. Would you mind sharing the modified setup script? Since you last posted, have you noticed any drawbacks?

I am just trying to run a personal website. Switched over to another server that's running Ubuntu 20.04 (instead of 18.04) and I only seem to have access to different versions of MySQL 8, nothing below that. Many thanks!

[passcod]

It's been two years, this has been trashed for a long time, sorry!

[ej-mitchell]

No worries! Have a way around it :)

[kasindhu]

No worries! Have a way around it :)

Could you share? Having the same issue

[ej-mitchell]

Sure! I worked with someone else on this as well, but my "step" in the process was to say "no" to creating a user in the newly made database during the setup. We manually added a user after that, but I don't know if the developer working on that portion of things did anything special with the user we added - I will ask him, just in case.

[ej-mitchell]

@kasindhu, here is the response from my partner:
"""
When prompted for the mysql user/password we used the root user/password so that ghost would use it to create the db, but we answered no when prompted to have to create a ghost user.

Then manually created a ghost user by manually executing the steps done by the code at:

Ghost-CLI/extensions/mysql/index.js

Line 83 in 9cb3138

createUser(ctx, dbconfig) {

Those are:

CREATE USER 'ghost-whatever'@'localhost' IDENTIFIED WITH mysqlnativepassword AS 'PASSWORD';
GRANT ALL PRIVILEGES ON 'ghostdatabasename.*' TO 'ghost-whatever'@'localhost';
FLUSH PRIVILEGES;

Then, manually replaced the mysql user/password from the root user to the user manually created in the file /var/www/yoursite.com/config.production.json

"""

Let me know if you have any questions!

[ferdi2005]

@acburdine 2 years...

[manuelfedele]

I'm experiencing the same behavior. Still unfixed?

[acburdine]

@ferdi2005 @manuelfedele Unfortunately, there's still an open issue/pr on the upstream mysql driver adding some fixes for MySQL 8 authentication: mysqljs/mysql#2233

I can try and add some smart detection of mysql versions, and have the "old_passwords" bit not run on MySQL 8, but my suspicion is that we'll need to make other updates to the mysql queries we're running as part of the setup process.

Also of note - see this comment: TryGhost/Ghost#11625 (comment) about the general support of MySQL 8 in Ghost itself.

I can try experimenting a bit with MySQL 8 to see if I can get something working, but it may end up being the case that we need to wait until the node mysql driver is updated.

[acburdine]

note for those watching this issue - I'm working on adding tentative support for MySQL 8 + ubuntu 20.04 support, tracked in #1265

if all goes well, should be able to get that support released sometime this week.

[acburdine]

for those watching this ticket - just released Ghost-CLI 1.15.0 which includes support for Ubuntu 20 LTS as well as MySQL 8. Feel free to raise an issue if you see any problems with it 😄