New Ghost v5.41.0 Unable To Connect To MySQL Database

sgrobert · April 2, 2023, 11:46am

Hi there,

I am trying to do a brand new install from scratch via the Bare Linux method, as even though I have tried using DigitalOcean’s Marketplace’s Ghost v5.41.0 Installer, the same error of being unable to connect to the database still persists.

Thus, I thought by utilizing the server method, I might have more control over what I can change to help resolve the solution. However, the same issue still persisted even though I destroyed and restarted the whole process about 20 times.

Hope someone may have some better headway on this matter to give some advice moving forward. Thank you.

Server Configs:

ubuntu v22.04 (LTS) x64
nginx v1.18.0-6ubuntu14.3
mysql-server v8.0.32-0ubuntu0.22.04.2
nodejs v18.15.0-deb-1nodesource1
npm v9.6.3

Ghost Configs:

ghost-cli v1.24.0
ghost v5.41.0

Error Messages:
Starting Ghost
One or more errors occurred.

GhostError

Message: Ghost was able to start, but errored during boot with: connect ECONNREFUSED ::1:3306
Help: Unknown database error
Suggestion: journalctl -u ghost_mywebsite -n 50

Debug Information:
OS: Ubuntu, v22.04.2 LTS
Node Version: v18.15.0
Ghost Version: 5.41.0
Ghost-CLI Version: 1.24.0
Environment: production
Command: ‘ghost install’

MySQL DB User Listing:

In addition, I have also tried to add an additional user my-ghost to handle the db-interactions but it just seems like Ghost cannot access mysql.

mjw · April 2, 2023, 1:06pm

These steps should work for you on Ubuntu 22.04, so double-check the MySQL section.

sudo apt update && sudo apt upgrade --yes
sudo apt install mysql-server --yes
sudo systemctl start mysql.service
sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root-password';
QUIT;

sudo mysql_secure_installation

sudo mysql -u root -p

CREATE USER 'user_site'@'localhost' IDENTIFIED BY 'site-password';
CREATE DATABASE site_prod;
GRANT ALL PRIVILEGES ON site_prod.* TO 'user_site'@'localhost';
FLUSH PRIVILEGES;
QUIT;

curl -sL https://deb.nodesource.com/setup_16.x | sudo -E bash
sudo apt install nodejs --yes
sudo npm install ghost-cli@latest -g
sudo mkdir -p /var/www/ghost-site
sudo chown ghost-user:ghost-user /var/www/ghost-site
sudo chmod 775 /var/www/ghost-site
cd /var/www/ghost-site
ghost install

sgrobert · April 2, 2023, 1:46pm

Hi @mjw,

I had tried all of the above before and had tried it again. The issue still currently persists.

✔ Checking system Node.js version - found v18.15.0
✔ Checking logged in user
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking system compatibility
✔ Checking for a MySQL installation
+ sudo systemctl is-active ghost_mywebsite-com
+ sudo systemctl reset-failed ghost_mywebsite-com
✔ Validating config
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking binary dependencies
✔ Checking free space
✔ Checking systemd unit file
✔ Checking systemd node version - found v18.15.0

The only new things which I do not get is sudo mysql_secure_installation, as the password I had used kept the program going in an endless loop. Do it mean I have to change the password, as my current password is pretty strong, but I am unsure that ghost's mysql actually validates the password. Does it?

Is there other ways I can help to jumpstart the mysql setup as it seems like the main issue I am consistently facing here.

Thank you.

mjw · April 2, 2023, 2:15pm

AFAIK, the reset-failed means Ghost was already in a failed state. So, it would seem that Ghost cannot access the database.

Secure installation isn’t a prerequisite, but is good practice. I’d double-check your database credentials using the command line.

sgrobert · April 3, 2023, 12:00am

Hi @mjw,

From all the results, I think Ghost have no issues but this mysql issue kept coming up oddly. I have been setting up different Ghost instances since version 3, and this is quite the first time, no matter how many times I re-do the whole setup, Ghost is unable to connect to mysql, though I do know in the past.

I just want to check is there any procedures that are potentially different as I did follow this official Ghost instructions (How to install & setup Ghost on Ubuntu 16.04, 18.04, 20.04 or 22.04) to the tee, but still end up with the same results.

Usually, right after executing,

# Install MySQL
sudo apt-get install mysql-server

I would do a,

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

However, as I recently checked the users within mysql (Based on this mysql - ERROR 1698 (28000): Access denied for user 'root'@'localhost' - Stack Overflow) via:

sudo mysql

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

As you can see that for 'root'@'localhost', the plugin is now auth_socket instead of mysql_native_password, thus the command ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password'; will also not work, which was also suggested in one of your previous solutions for me.

Given that, I may think that is the main root cause of the issue where the ‘root’@‘localhost’ password is actually not altered.

Alternatively, I also tried another method where I added a new ghost-user as the alternative 'ghost-user'@'localhost' (Based on suggestions from this mysql - ERROR 1698 (28000): Access denied for user 'root'@'localhost' - Stack Overflow),

sudo mysql

USE mysql;
CREATE USER 'ghost-user'@'localhost' IDENTIFIED BY 'new-password';
GRANT ALL PRIVILEGES ON *.* TO 'ghost-user'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='ghost-user';
FLUSH PRIVILEGES;
QUIT;

Sadly, though there was some difference in the output, the main results was still similar, where “Ghost was unable to connect to mysql”.

At the moment, I do believe I have tried a number of different methods with similar results and hoped that you or your team might have a potential alternative solution that I might not have tried relating to this part of password authentication.

Hope to hear your view on this. Thank you.

mjw · April 3, 2023, 7:35am

As mentioned earlier, set the root password, and then login using the password method. Then create a user and database solely for Ghost use.

You may want to restart MySQL after.

The steps I posted are verified for Ghost V5, and Ubuntu 22.04. It’s what I used.

Incidentally, please note that I am a community member like yourself and a volunteer moderator, and not part of the Ghost project team.

sgrobert · April 3, 2023, 1:45pm

Hi @mjw ,

First of all, my apologies for assuming that you have been part of the Ghost team, and Thank you for volunteering your personal time to assist and help with my enquires! Really appreciate you for that!

I have actually did another round of server-restart, re-setting up the whole process from scratch, but this time, I had followed the exact side-tracked suggestion you have provided earlier:


sudo apt update && sudo apt upgrade --yes
sudo apt install mysql-server --yes
sudo systemctl start mysql.service
sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root-password';
QUIT;

sudo mysql_secure_installation

sudo mysql -u root -p

CREATE USER 'user_site'@'localhost' IDENTIFIED BY 'site-password';
CREATE DATABASE site_prod;
GRANT ALL PRIVILEGES ON site_prod.* TO 'user_site'@'localhost';
FLUSH PRIVILEGES;
QUIT;

curl -sL https://deb.nodesource.com/setup_16.x | sudo -E bash
sudo apt install nodejs --yes
sudo npm install ghost-cli@latest -g
sudo mkdir -p /var/www/ghost-site
sudo chown ghost-user:ghost-user /var/www/ghost-site
sudo chmod 775 /var/www/ghost-site
cd /var/www/ghost-site
ghost install

Thankfully now it worked! The key reminder here is as you had mentioned: “As mentioned earlier, set the root password, and then login using the password method. Then create a user and database solely for Ghost use.” ;)

So just want to say a Big Thank You for the great help! ;)

Final Working Server Configs For Others To Reference:

ubuntu v22.04 (LTS) x64
nginx v1.18.0-6ubuntu14.3
mysql-server v8.0.32-0ubuntu0.22.04.2
nodejs v16.19.1-deb-1nodesource1
npm v9.6.3
ghost-cli v1.24.0
ghost v5.41.0

Topic		Replies	Views
Connect ECONNREFUSED ::1:3306 Installation	4	4316	October 31, 2023
Error installing ghost: connect ECONNREFUSED 127.0.0.1:3306 Using Ghost	2	897	December 23, 2023
Latest Ghost Update Throws "Unknown database error" Self-hosting	7	1288	November 25, 2023
Getting mysql connection error Installation	4	2625	September 18, 2018
Installation Errors Using Official Doc Installation	9	843	October 8, 2023

New Ghost v5.41.0 Unable To Connect To MySQL Database

Related topics