Strictly following the Ubuntu install guide on Ubuntu 22.04 fails, because the root MySQL user gets created with auth_socket auth, not a password

[ExplodingCabbage]

Summary

The docs at https://ghost.org/docs/install/ubuntu/:

1. instruct you to install MySQL from the OS's Apt repositories with sudo apt-get install mysql-server, then
2. don't instruct you to change the root MySQL user's password, then
3. instruct you to run ghost install as some newly-created Unix user who has sudo privilege but is not root

Assuming you follow those instructions to the letter and don't do anything extra, this is guaranteed to fail (at least on an Ubuntu 22.04 AWS EC2 instance; I have not tried in other environments).

This is because the sudo apt-get install mysql-server installs MySQL with a root user whose "plugin" (slightly odd MySQL terminology that for these purposes we can read as "authentication method") is auth_socket. This authentication method uses a Unix socket and simply checks that the Unix user trying to authenticate has the same name as the MySQL user they're trying to authenticate as; no password is used.

Thus, if you enter root as the MySQL username when prompted by ghost install, then no matter what password you enter, you will hit an error like this:

mark@ip-172-31-82-167:/var/www/sitename$ ghost install

Love open source? We’re hiring JavaScript Engineers to work on Ghost full-time.
https://careers.ghost.org



✔ Checking system Node.js version - found v16.19.1
✔ Checking current folder permissions
✔ Checking memory availability
✔ Checking free space
✔ Checking for latest Ghost version
✔ Setting up install directory
✔ Downloading and installing Ghost v5.43.0
✔ Finishing install process
? Enter your blog URL: https://explodingcabbage.com
? Enter your MySQL hostname: localhost
? Enter your MySQL username: root
? Enter your MySQL password: [hidden]
? Enter your Ghost database name: sitename_prod
✔ Configuring Ghost
✔ Setting up instance
+ sudo useradd --system --user-group ghost
+ sudo chown -R ghost:ghost /var/www/sitename/content
✔ Setting up "ghost" system user
? Do you wish to set up "ghost" mysql user? Yes
✖ Setting up "ghost" mysql user
? Do you wish to set up Nginx? Yes
+ sudo mv /tmp/explodingcabbage-com/explodingcabbage.com.conf /etc/nginx/sites-available/explodingcabbage.com.conf
+ sudo ln -sf /etc/nginx/sites-available/explodingcabbage.com.conf /etc/nginx/sites-enabled/explodingcabbage.com.conf
+ sudo nginx -s reload
✔ Setting up Nginx
? Do you wish to set up SSL? Yes
? Enter your email (For SSL Certificate) markrobertamery@gmail.com
+ sudo mkdir -p /etc/letsencrypt
+ sudo ./acme.sh --install --home /etc/letsencrypt
+ sudo /etc/letsencrypt/acme.sh --issue --home /etc/letsencrypt --server letsencrypt --domain explodingcabbage.com --webroot /var/www/sitename/system/nginx-root --reloadcmd "nginx -s reload" --accountemail markrobertamery@gmail.com
+ sudo openssl dhparam -dsaparam -out /etc/nginx/snippets/dhparam.pem 2048
+ sudo mv /tmp/ssl-params.conf /etc/nginx/snippets/ssl-params.conf
+ sudo mv /tmp/explodingcabbage-com/explodingcabbage.com-ssl.conf /etc/nginx/sites-available/explodingcabbage.com-ssl.conf
+ sudo ln -sf /etc/nginx/sites-available/explodingcabbage.com-ssl.conf /etc/nginx/sites-enabled/explodingcabbage.com-ssl.conf
+ sudo nginx -s reload
✔ Setting up SSL
? Do you wish to set up Systemd? Yes
+ sudo mv /tmp/explodingcabbage-com/ghost_explodingcabbage-com.service /lib/systemd/system/ghost_explodingcabbage-com.service
+ sudo systemctl daemon-reload
✔ Setting up Systemd
+ sudo systemctl is-active ghost_explodingcabbage-com
? Do you want to start Ghost? Yes
+ sudo systemctl start ghost_explodingcabbage-com
+ sudo systemctl stop ghost_explodingcabbage-com
✖ Starting Ghost
One or more errors occurred.

1) CliError

Message: Error trying to connect to the MySQL database.
Help: You can run `ghost config` to re-enter the correct credentials. Alternatively you can run `ghost setup` again.


2) GhostError

Message: Ghost was able to start, but errored during boot with: Access denied for user 'root'@'localhost'
Help: Unknown database error
Suggestion: journalctl -u ghost_explodingcabbage-com -n 50

Debug Information:
    OS: Ubuntu, v22.04.2 LTS
    Node Version: v16.19.1
    Ghost Version: 5.43.0
    Ghost-CLI Version: 1.24.0
    Environment: production
    Command: 'ghost install'

Additional log info available in: /home/mark/.ghost/logs/ghost-cli-debug-2023-04-12T13_19_47_613Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.

I suggest the ideal fix here is to change Ghost-CLI so that if the MySQL username specified is root and no password is given, it will attempt both the current auth mechanism and then, if that fails, attempt to sudo and do socket-based auth as root.

If that's difficult, we should at least add instructions to the end of the Install MySQL section saying something like this:

On newer versions of Ubuntu, the root user created when you install MySQL will by default be configured to use socket-based authentication, meaning that only the root Unix user will be able to authenticate. Ghost does not support this kind of authentication, so you must change the root MySQL user to have a password. To do this, first open a MySQL shell by running:

sudo mysql

and then execute

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<your-new-root-password>';

Steps to Reproduce

1. Create an Ubuntu 22.04 EC2 instance in AWS. (I presume, but have not tested, that any Ubuntu 22.04 anywhere will behave the same. I'm not sure about 20.04 or earlier.)
2. SSH in, then sudo su to swap to the root user.
3. Follow the rest of the instructions at https://ghost.org/docs/install/ubuntu/ exactly until you hit the error quoted above.

Log file

All errors end with "Additional log info available in: [filepath]".

Use the command cat [filepath] to read the log, then copy & paste the contents here:

Okay!

mark@ip-172-31-82-167:/var/www/sitename$ cat /home/mark/.ghost/logs/ghost-cli-debug-2023-04-12T13_19_47_613Z.log
Debug Information:
    OS: Ubuntu, v22.04.2 LTS
    Node Version: v16.19.1
    Ghost Version: 5.43.0
    Ghost-CLI Version: 1.24.0
    Environment: production
    Command: 'ghost install'
Message: Error trying to connect to the MySQL database.
Help: You can run `ghost config` to re-enter the correct credentials. Alternatively you can run `ghost setup` again.
Stack: Error: Error trying to connect to the MySQL database.
    at MySQLExtension.canConnect (/usr/lib/node_modules/ghost-cli/extensions/mysql/index.js:112:19)

Original Error Message:
Message: Access denied for user 'root'@'localhost'
Stack: Error: Access denied for user 'root'@'localhost'
    at Packet.asError (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/packets/packet.js:728:17)
    at ClientHandshake.execute (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/usr/lib/node_modules/ghost-cli/node_modules/mysql2/lib/connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)


Message: Ghost was able to start, but errored during boot with: Access denied for user 'root'@'localhost'
Help: Unknown database error
Suggestion: journalctl -u ghost_explodingcabbage-com -n 50
Stack: Error: Ghost was able to start, but errored during boot with: Access denied for user 'root'@'localhost'
    at Server.<anonymous> (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
    at Object.onceWrapper (node:events:627:28)
    at Server.emit (node:events:513:28)
    at emitCloseNT (node:net:1857:8)
    at processTicksAndRejections (node:internal/process/task_queues:82:21)

Technical details

This is automatically output by Ghost-CLI if an error occurs, please copy & paste:

Debug Information:
OS: Ubuntu, v22.04.2 LTS
Node Version: v16.19.1
Ghost Version: 5.43.0
Ghost-CLI Version: 1.24.0
Environment: production
Command: 'ghost install'

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 - There is related discussion at https://forum.ghost.org/t/new-ghost-v5-41-0-unable-to-connect-to-mysql-database/37518 which provides the obvious workaround of running a command like ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root-password'; to change the root user to use password auth. Nonetheless, I still consider the docs as they currently exist to be broken, because they don't instruct you to set a password like this and it's a necessary step.
• Checked for existing issues
• Attached log file
• Provided technical details incl. operating system

[ExplodingCabbage]

Some further commentary:

mysql2, the library you use to connect to MySQL, supports socket-based connections like this...

conn = mysql.createConnection({socketPath: '/var/run/mysqld/mysqld.sock', user: 'root'})

... but obviously you'd need to run that from within a process that's running as root, which seems awkward since you deliberately don't allow ghost install to be run as root.

Maybe the best approach here is to run the commands to create the Ghost user via the shell using your ui.sudo function, only when the host is localhost, the user is root, password auth has failed, and a Ghost user needs creating? I'm not sure if I'm overcomplicating things here and the better solution is just a docs changing telling the user to give root a password...

[carrickspeirs]

Also ran into this error when using Digital Oceans 1-click droplet. Most of what you said above went straight over my head. Is there a quick fix before someone looks at the bug?

[MarkAmeryCurative]

@carrickspeirs yes - see the instructions in the quote block that I suggest adding to the docs. Basically the fix is:

1. Run ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'somepassword';
2. Enter somepassword as the root password when installing Ghost

It's unfortunate we have to do this, though, because it weakens the security of our MySQL install a bit.

[daniellockyer]

You're right, our docs need work here. We should be able to tidy these up next week 🙂

[github-actions]

Our bot has automatically marked this issue as stale because there has not been any activity here in some time. The issue will be closed soon if there are no further updates, however we ask that you do not post comments to keep the issue open if you are not actively working on a PR. We keep the issue list minimal so we can keep focus on the most pressing issues. Closed issues can always be reopened if a new contributor is found. Thank you for understanding 🙂