How can I troubleshoot database connection errors on WordPress-based applications hosted in Lightsail?

Short description

The error "Error establishing a database connection" might occur for the following reasons:

• There are corrupted database tables.
• The remote database connection is disabled.
• The database service is down.
• There is insufficient space on your volume.
• There are incorrect login credentials in your WordPress Configuration file.

Resolution

Corrupted database tables

Open the wp-admin page of your website (for example, example.com/wp-admin) in the browser and look for the error "One or more database tables are unavailable. The database may need to be repaired.". If you see this error, then you're getting the "Error establishing database connection" error due to corrupted database tables. To repair corrupted tables, do the following:

1.    Access the wp-config.php file using a text editor, such as the vi editor.

$ sudo vi wp-config.php

2.    Add the following line in your wp-config.php file. Make sure to add the line immediately before the line "That’s all, stop editing! Happy blogging".

define('WP_ALLOW_REPAIR' ,true);

3.    After adding the preceding setting to the file, access the following URL and then run Repair Database:

/wp-admin/maint/repair.php (for example, example.com/wp-admin/maint/repair.php)

4.    After running the database repair, remove the line of code you added to your wp-config.php file. If you don't remove this line, then anyone can run the repair on your database.

Disabled remote database connection

Sometimes databases reside on a remote database server. If the database server doesn't allow remote connections from the instance hosting the website, then you can't connect to the database. To troubleshoot this, do the following:

1.    Check the configuration file wp-config.php for the DB_HOST value. If the host isn't localhost or 127.0.0.1, then the database resides in a remote server, as shown in the following example:

define('DB_HOST', '192.168.22.9');

2.    Try to telnet from the server to the remote server on port 3306. If you can't connect, then remote connections aren't allowed in the database configuration on the remote server. Or, there's a firewall on the remote server that's blocking the connection. Contact the external database owner or support for assistance allowing connections from your Lightsail instance.

It's a best practice to store the website database in a Lightsail managed database for high availability and security.

Database service is down

Note: The following file paths and commands might change depending on whether your Lightsail WordPress instance uses MySQL or MariaDB. Also, the file paths and commands vary depending on whether the instance uses native Linux system packages (Approach A), or if it's a self-contained installation (Approach B). To identify the database server type and which approach to follow, run the following commands:

test ! -f "/opt/bitnami/common/bin/openssl" && echo "Approach A" || echo "Approach B"

test -d /opt/bitnami/mariadb && echo "MariaDB" || echo "MySQL"

1.    If you verified that there are no table corruption and no remote database connection issues, and WordPress still can't connect to the database, then your database server might be down. This might happen due to database configuration issues, heavy traffic on a server, low disk space, low available memory, and so on. Check the database service status using the following command:

MySQL database server

sudo /opt/bitnami/ctlscript.sh status mysql

MariaDB database server

sudo /opt/bitnami/ctlscript.sh status mariadb

2.    If the preceding command shows that database is in the stopped state, then try starting the database service using the following command:

MySQL database server

sudo /opt/bitnami/ctlscript.sh start mysql

MariaDB database server

sudo /opt/bitnami/ctlscript.sh start mariadb

3.    If you're still not able to start the database service and you're seeing errors during the start process, then check the database service logs to identify the root cause and troubleshoot the issue. The main database service log file is located at one of the following locations in your Lightsail WordPress Instance:

MySQL database server following Approach A: /opt/bitnami/mysql/logs/mysqld.log

MySQL database server following Approach B: /opt/bitnami/mysql/data/mysqld.log

MariaDB database server following Approach A: /opt/bitnami/mariadb/logs/mysqld.log

MariaDB database server following Approach B: /opt/bitnami/mariadb/data/mysqld.log

Database performance and connectivity can be affected by low disk space and/or low available memory. Check these resources using the df and free commands.

Insufficient space on your volume

If the free disk space on your volume is 100% or just below 100%, then the database service might go down.

1.    Run the following command:

$ sudo df -h

The preceding command lists the amount of free disk space, as shown in the following example:

Filesystem      Size    Used     Avail      Use%    Mounted on
devtmpfs        1.9G     0       1.9G        0%     /dev
tmpfs           1.9G     0       1.9G        0%     /dev/shm
tmpfs           1.9G    400K     1.9G        1%     /run
tmpfs           1.9G     0       1.9G        0%     /sys/fs/cgroup
/dev/nvme0n1p1  8.0G    8.0G      0G        100%    /
tmpfs           389M     0       389M        0%     /run/user/1000

2.    If the command output shows that you don't have enough available space, you can resize your instance to bigger size. Or, you can delete unnecessary files from the server to create free space.

3.    After increasing the free disk space, restart the database service.

Incorrect login credentials in your WordPress settings

WordPress needs a specific database connection string, which includes a user name, password, and host to access the database. If any of those items have changed, then WordPress can't access the database.

1.    To verify you're using the correct connection string, get the connection string details DB_NAME, DB_HOST, DB_USER and DB_PASSWORD in the wp-config.php file.

2.    Access the database from a terminal using the connection string. Make sure to replace DB_NAME, DB_HOST AND DB_USER with the values you got in step 1.

sudo mysql 'DB_NAME' -h 'DB_HOST' -u 'DB_USER' -p
Enter password: ********

Note: The password isn't displayed as you enter it so that it won't be visible to other users.

3.    Press the ENTER key after entering the password.

If you're getting an Access Denied error when using the preceding command, then it usually means that the credentials are incorrect.

If you're using a remote DB host, then add the correct connection string in the wp-config.php file. If the database is in the same server, then make sure that DB_NAME is bitnami_wordpress and DB_USER is bn_wordpress.

To reset the database user password, do the following:

1.    Use the following command to access /home/bitnami/bitnami_credentials. Make a note of the root database password.

sudo cat /home/bitnami/bitnami_credentials

2.    Log in to MySQL/MariaDB shell using the following command, then enter the password you got from the preceding command. If you're not able to log in to shell using the database root password, then reset the password (MySQL or MariaDB)

sudo mysql -u root -p
Enter password: ********

3.    Inside the MySQL or MariaDB shell, run the following query to make sure that database bitnami_wordpress exists:

show databases;

4.    Run the following query to make sure that the database user bn_wordpress exists:

SELECT user FROM mysql.user;

5.    Reset the password of the database user "bn_wordpress" using the following query.

Note: Replace PASSWORD with the password you got from the wp-config.php file.

MySQL database

alter user 'bn_wordpress'@'localhost' identified by 'PASSWORD‘;
alter user 'bn_wordpress'@'127.0.0.1' identified by 'PASSWORD‘;

MariaDB database

alter user 'bn_wordpress'@'%' identified by 'PASSWORD‘;

Note: If none of the preceding resolutions work, you can restore your instance using a backup snapshot.

---