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

Short description

You receive these error messages because of the following reasons:

• Corrupted database tables
• Blocked remote database connection
• Database service issues
• Insufficient space on your volume
• Incorrect login credentials in your WordPress configuration file

Resolution

Corrupted database tables

To check for corrupted database tables, complete the following steps:

1. Go to example.com/wp-admin, and look for the following error: One or more database tables are unavailable. The database may need to be repaired.
   Note: Replace example.com with your website name.

2. Use a text editor, such as the vi editor, to access the wp-config.php file:

   $ sudo vi wp-config.php

3. Immediately before the line That's all, stop editing! Happy blogging, add the following line:

   define('WP_ALLOW_REPAIR' ,true);

4. Go to example.com/wp-admin/maint/repair.php, and then run Repair Database.
   Note: Replace example.com with your website name.

5. Go back to the wp-config-php file, and remove the line of code that you added in step 3.

Blocked remote database connection

If the database server doesn't allow remote connections from the instance that's hosting the website, then you can't connect to the database. It's a best practice to store the website database in a Lightsail managed database for high availability and security.

To check your database connection, complete the following steps:

1. Check the wp-config.php configuration file for the DB_HOST value:

   define('DB_HOST', '192.168.22.9');

   Note: If the value isn't localhost or 127.0.0.1, then the database resides in a remote server. Copy the DB_HOST value to use in the following step.
2. Use port 3306 to telnet from the server to the remote server:

   telnet remote server IP 3306

   Note: Replace remote server IP with the DB_HOST value that you received in step 1.
3. If you can't connect, then the remote server's database configuration doesn't allow remote connections. A firewall on the remote server might also block your connection. Contact the external database owner or support team to set up connections from your Lightsail instance.

Database service issues

To check if your database service is down, determine whether your Lightsail WordPress instance uses MySQL or MariaDB as a server type. Also, check whether the instance uses native Linux systems or a server that's a self-contained installation. To identify the server type and approach, run the following commands:

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

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

Note: If you see Approach A in the output, then your server uses native Linux systems. If you see Approach B, the server is a self-contained installation.

MySQL database server

To check for service issues with a MySQL database, complete the following steps:

1. To check the database server status, run the following command:

   sudo /opt/bitnami/ctlscript.sh status mysql

2. If the database is stopped, then run the following command:

   sudo /opt/bitnami/ctlscript.sh start mysql

3. If you still can't start the database service or you see errors during the start process, then check the database logs to identify the root cause. If your server uses native Linux systems, then check the following file location: /opt/bitnami/mysql/logs/mysqld.log. If your server is a self-contained installation, then check the following file location: /opt/bitnami/mysql/data/mysqld.log. Low disk space or low available memory affect database performance and connectivity. To check these resources, run the df and free commands.

MariaDB database server

To check for service issues with a MariaDB database, complete the following steps:

1. To check the database server status, run the following command:

   sudo /opt/bitnami/ctlscript.sh status mariadb

2. If the database is stopped, then run the following command:

   sudo /opt/bitnami/ctlscript.sh start mariadb

3. If you still can't start the database service or you see errors during the start process, then check the database logs to identify the root cause. If your server uses native Linux systems, then check the following file location: /opt/bitnami/mariadb/logs/mysqld.log. If your server is a self-contained installation, then check the following file location: /opt/bitnami/mariadb/data/mysqld.log. Low disk space or low available memory affect database performance and connectivity. To check these resources, run 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.

To check your server volume, complete the following steps:

1. To see the amount of free disk space, run the following command:

   $ sudo df -h

   Example output:

     
   Filesystem      Size    Used     Avail      Use%    Mounted ondevtmpfs        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 you don't have enough available space, then increase your instance size or delete unnecessary files from the server.

3. Restart the database service.

Incorrect login credentials in your WordPress configuration file

Verify your credentials

Complete the following steps:

1. Go to the wp-config.php file. Copy the details in DB_NAME, DB_HOST, DB_USER, and DB_PASSWORD to a text file to use in the following step.
2. To access the database from a terminal, run the following command:

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

   Note: Replace DB_NAME, DB_HOST, and DB_USER with the values that you copied in step 1.
3. Enter the password with the DB_PASSWORD value, and then press ENTER.
   Note: If you receive an Access Denied error, then the credentials are incorrect. To resolve this, reset the database user password.
4. If your DB host is remote, then update the connection string in the wp-config.php file. If the database is in the same server, then validate that DB_NAME is bitnami-wordpress and DB_USER is bn_wordpress.

Reset the database user password

Complete the following steps:

1. To access /home/bitnami/bitnami_credentials, run the following command:

   sudo cat /home/bitnami/bitnami_credentials

   Note: Copy the root database password to a text file to use in the following step.

2. To log in to your MySQL or MariaDB shell, run the following command:

   sudo mysql -u root -pEnter password: ********

   Note: Replace mysql with your server and ******** with the root database password that you copied in step 1. If you can't log in with the password, then follow the instructions on the Bitnami website to reset the password for MySQL or MariaDB.

3. To verify that the bitnami_wordpress database exists, run the following query:

   show databases;

4. To verify that the bn-wordpress database user exists, run the following query:

   SELECT user FROM mysql.user;

5. If you use a MySQL database, then run the following command to reset the bn-wordpress user's database password:

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

   If you use a MariaDB database, then run the following command to reset the bn-wordpress user's database password:

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

   Note: Replace PASSWORD with the password from the wp-config.php file in step 3 of Verify your credentials.

If none of the preceding resolutions resolve the issue, then launch a new Lightsail instance from a snapshot.