One day without warning and for no clear reason, my website suddenly started sending “error connecting to database” messages in web-browser for http://kevwells.com. 

No content was displayed. The WordPress WP-Admin could not be accessed either. Big Problem!

Non-Wordpress html pages were displaying and functioning ok, so it was obviously a Mysql-Wordpress communication issue.

Also pure PHP pages also displayed without problem – eg phpmyadmin, nextcloud, checkmk. So PHP was running ok.

Possible Causes of the Problem

Most support suggestions list 3 main possibilities:

Your database login credentials are wrong or have been changed.
Your database server is unresponsive.
Your database has been corrupted.

I checked, checked, and rechecked all this in line with all the suggestions – and found everything to be order. No errors, no mistakes, nothing missing.

Searching online for possible alternative explanations for this problem yielded little of use. Most suggestions simply regurgitated the standard textbook stuff – check login credentials, check database is running, check apache is running. Check the database isn’t corrupted.

I encountered other people who also faced this sudden same problem – so I was not alone with this issue – but they were as stuck as I was. 

However, there was a fourth possibility which no-one appeared to have mentioned and did not seem to have occurred to anyone: 

WordPress plugins causing the problem.

After extensive investigation I ascertained that this was indeed the cause in this case.

The solution was to move plugins to a new directory: plugins.disabled.

Then move plugins one by one back to the original plugin directory, login to the wp-admin and activate one by one, test the website content functionality and move plugins back one by one, repeating the procedure for each in turn.

Below are the steps I used in analysing this problem:

Check MySQL is running and listening on the correct port

First check the mysqld or mysql service is up and running, and that apache is running:

ps -ef | grep apache

ps -ef | grep mysql

also check they are listening for connections, and listening on the correct ports:

netstat -tulpn

Check version of mysql with:

root@gemini:/var/www/kevwells.com# mysql –version
mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@gemini:/var/www/kevwells.com#

Check your WordPress wp-config.php is correct and does not contain any errors

Next check the wp-config.php definitions for the mysql database. These are essential definitions that must be present – substitute your own definitions for your system and make sure they are correct:

Check your wp-config.php file in your WordPress document root directory.

Your wp-config.php should contain following:

// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘database_name_here’ );
/** MySQL database username */
define( ‘DB_USER’, ‘username_here’ );
/** MySQL database password */
define( ‘DB_PASSWORD’, ‘password_here’ );
/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

How to Debug wp-config.php

The following code, inserted in your wp-config.php file, will log all errors, notices, and warnings to a file called debug.log in the wp-content directory.  It will also hide the errors so they do not interrupt page generation.

This code will need be to inserted before the comment /* That’s all, stop editing! Happy blogging. */ in the wp-config.php file.

// Enable WP_DEBUG mode
define( ‘WP_DEBUG’, true );

// Enable Debug logging to the /wp-content/debug.log file
define( ‘WP_DEBUG_LOG’, true );

// Disable display of errors and warnings
define( ‘WP_DEBUG_DISPLAY’, false );
@ini_set( ‘display_errors’, 0 );

// Use dev versions of core JS and CSS files (only needed if you are modifying these core files)
define( ‘SCRIPT_DEBUG’, true );

WP_DEBUG #WP_DEBUG
WP_DEBUG is a PHP constant (a permanent global variable) that can be used to trigger the “debug” mode throughout WordPress. It is assumed to be false by default and is usually set to true in the wp-config.php file on development copies of WordPress.

// This enables debugging.
define( ‘WP_DEBUG’, true );
// This disables debugging.
define( ‘WP_DEBUG’, false );

Obviously you do not want both true and false defined at the same time, so comment out the one you do not currently want to be active.

Note: The true and false values in the example are not surrounded by apostrophes (‘) because they are boolean (true/false) values. If you set constants to ‘false’, they will be interpreted as true because the quotes make it a string rather than a boolean.

It is not recommended to use WP_DEBUG or the other debug tools on live site; they are meant for local testing, debugging, and install stage only.

WP_DEBUG_LOG #WP_DEBUG_LOG

WP_DEBUG_LOG is a companion to WP_DEBUG that causes all errors to also be saved to a debug.log log file This is useful if you want to review all notices later or need to view notices generated off-screen (e.g. during an AJAX request or wp-cron run).

Note that this allows you to write to log file using PHP’s built in error_log() function, which can be useful for instance when debugging Ajax events.

When set to true, the log is saved to debug.log in the content directory (usually wp-content/debug.log) within your site’s filesystem. Alternatively, you can set it to a valid file path to have the file saved elsewhere.

define( ‘WP_DEBUG_LOG’, true );
-or-
define( ‘WP_DEBUG_LOG’, ‘/tmp/wp-errors.log’ );
Note: for WP_DEBUG_LOG to do anything, WP_DEBUG must be enabled (true). Remember you can turn off WP_DEBUG_DISPLAY independently.

Does the problem occur for /wp-admin/ as well?

Does it occur with webpages on the server which are not provided by the wordpress database? eg plain html, php-driven services, and the like, if there are any on the server?

eg checkmk, or nextcloud, or simple html pages.

Try Repairing the WordPress Database

If the error is only on the front end. try to repair the database by adding the following line of code in wp-config.php

define(‘WP_ALLOW_REPAIR’, true);

Then go to ‘http://www.yoursite.com/wp-admin/maint/repair.php’ and click ‘repair and optimize database’ button.

/usr/bin/mysqlcheck –all-databases –auto-repair

check:

mysqlshow -u database_username -p

root@gemini:~#
root@gemini:~# mysqlshow –verbose
+——————–+——–+
| Databases | Tables |
+— 

(DATA REMOVED FOR SECURITY REASONS) 

| 12 |

+——————–+——–+
9 rows in set.
root@gemini:~#

Check Your index.php is Present and Error-Free

Check the index.php is present and is the correct instance. This is important!

Whenever someone visits a web page on a WordPress site, the server attempts to run index.php or index.html from the defined WordPress document root directory.

Your website document root directory index.php contains the code that generates a WordPress site.

However there is also an index.php in the wp-content directory and in other locations. If you examine the index.php located under wp-content and other directories you will find that it contains no active code. It is simply a placeholder.

Without index.php, anyone could access your site’s /wp-content folder and see all of the media, files, and directories it contains.

The index.php file at this location and others functions like a privacy screen: it blocks visitors from directly accessing your directories.

Alternativey you can use the following directive in your .htaccess file to prevent directory listings even if that index.php file is not present:

Options –Indexes

However, be aware that you do NOT want this file in your main document root folder, else no one can access any website content!

Check Apache Can Connect To MySQL Server

Next, check your web-host can connect to your mysql server.

In PHP you can easily do this using the mysqli_connect() function. All communication between PHP and the MySQL database server takes place through this connection. Here’re the basic syntaxes for connecting to MySQL using MySQLi extensions:

Syntax: MySQLi, Procedural way
$link = mysqli_connect(“hostname”, “username”, “password”, “database”);

Syntax: MySQLi, Object Oriented way
$mysqli = new mysqli(“hostname”, “username”, “password”, “database”);

Create a new file called testconnection.php and paste the following code in it:

<?php
$link = mysqli_connect(‘localhost’, ‘username’, ‘password’);
if (!$link) {
die(‘Could not connect: ‘ . mysqli_error());

}
echo ‘Connected successfully’;
mysqli_close($link);
?>

(substitute localhost, username and password with the correct parameters for your site)

Then I called up the file in the webbrowser:

http://3.222.27.169/testconnection.php

Result, it displayed:

Connected successfully

So that means the connection parameters – dbhost name, username and password are ok.

Check the Error Logs for MySQL and Apache

If the configuration seems correct and the service is running, but your website still doesn’t load as it should, try checking the logs for any hints to as what might be the cause.

Error logs for Apache are located at /var/log/apache/error.log

Error logs for MySQL are located at /var/log/mysql/error.log

Should you not be able to find anything within the most recent logs, check the archived ones as well. To do this, use ‘zgrep’ with otherwise the same command as regular ‘grep’

sudo zgrep -i error /var/log/mysql/error.log.1.gz

Since the database under CentOS is named MariaDB instead of MySQL, the logs are also saved under a different name. You can search the logs with the following command.

sudo grep -i error /var/log/mariadb/mariadb.log

Debian systems also report MySQL events to /var/log/syslog, to filter out everything else, use ‘grep’ with two keywords separated by .* to express ‘and’ like in the command below.

sudo grep -i -E ‘mysql.*error’ /var/log/syslog

If you are having difficulties finding anything helpful, try different keywords such as ‘start’ to see when the service was last restarted, or ‘failed’ to find any less critical problems that might not be reported as errors.

When we try

http://3.222.27.169/wp-admin

 we see in /var/log/mysql/error.log:

2021-12-29 19:39:44 264 [Warning] Access denied for user ”@’localhost’ (using password: NO)
2021-12-29 19:40:22 270 [Warning] Access denied for user ”@’localhost’ (using password: NO)

so, it looks like there is some login issue for the mysql database user:

Check the MySQL Database

MariaDB []> SELECT User, Host FROM mysql.user;
+—————+———–+
| User | Host |
+—————+———–+
| * | % |
| * | localhost |
| * | localhost |
| * | localhost |
| * | localhost |
| * | localhost |
+—————+———–+
6 rows in set (0.000 sec)

MariaDB [kevwells]>

NOTE! Usernames have been removed from the above for security reasons.

Use the desc mysql.user; statement to display information about the table’s columns. Once you know the column name, you can run a query against a selected data.

For example, to get a list of all MySQL users accounts including information about the password and whether it is active or expired, you would use the following query:

SELECT User, Host, Password, password_expired FROM mysql.user;

MariaDB []> SELECT User, Host, Password, password_expired FROM mysql.user;
+—————+———–+——————————————-+——————+
| User | Host | Password | password_expired |
+—————+———–+——————————————-+——————+
| * | localhost | * | N |
| * | localhost | * | |
| * | localhost | * | N |
| * | localhost | * | N |
| * | % | * | N |
| * | localhost | * | N |
+—————+———–+——————————————-+——————+
6 rows in set (0.001 sec)

MariaDB []>

#NOTE! Usernames and passwords have been removed from the above for security reasons.

Show Users that Have Access to a Particular Database

The information about the database-level privileges is stored in the mysql.db table.
You can query the table to find out which users have access to a given database and the level of the privileges.

For example, to get a list of all users that have some level access to the database named db_name you would use the following query:

SELECT * FROM mysql.db WHERE Db = ‘db_name’\G;

MariaDB []> SELECT * FROM mysql.db WHERE Db = ‘*****’\G;
*************************** 1. row ***************************
Host: localhost
Db: ****
User: ***
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
Delete_history_priv: Y
1 row in set (0.001 sec)

ERROR: No query specified

MariaDB []>

NOTE! Usernames, dbname and passwords have been removed from the above for security reasons.

To fetch information only about the user accounts that have access to a given database, without displaying the privileges use:

SELECT db, host, user FROM mysql.db WHERE db = ‘db_name’;

MariaDB []> SELECT db, host, user FROM mysql.db WHERE db = ‘***’
-> ;
+———-+———–+—————+
| db | host | user |
+———-+———–+—————+
| * | localhost | * |
+———-+———–+—————+
1 row in set (0.001 sec)

MariaDB []>

NOTE: Sensitive information has been removed from the output for security reasons!

The following query will show you information about all databases and associated users:

SELECT db, host, user FROM mysql.db;

Try restoring database from a backup made using wp-db-backup plugin:

Put the backed-up SQL back into MySQL/MariaDB:
user@linux:~/files/blog> mysql -h mysqlhostserver -u mysqlusername -p databasename < blog.bak.sql

Enter password: (enter your mysql password)
user@linux:~/files/blog>

root@gemini:/var/www/kevwells.com# mysql -h localhost -u *** -p *** < /var/www/kevwells.com/*****.sql

Enter password:
root@gemini:/var/www/kevwells.com#

NOTE! Usernames, dbname and passwords have been removed from the above for security reasons

To delete a user use:

DROP USER ‘exampleuser1’@localhost;

then check the user is no longer present:

MariaDB [(none)]> SELECT User FROM mysql.user;

The syntax for changing a password using the SET PASSWORD statement in MariaDB is:

SET PASSWORD [ FOR user_name ] =
{
PASSWORD(‘plaintext_password1’)
| OLD_PASSWORD(‘plaintext_password2’)
| ‘encrypted_password’
};

For example, if you had an entry with User and Host column values of ‘bob’ and ‘%.loc.gov’, you would write the statement like this:

SET PASSWORD FOR ‘bob’@’%.loc.gov’ = PASSWORD(‘newpass’);

If you want to delete a password for a user, you would do:

SET PASSWORD FOR ‘bob’@localhost = PASSWORD(“”);

I was then able to access phpmyadmin, so one step forward on the way to solving the problem.

In phpmyadmin update the site url if necessary with:

UPDATE wp_options SET option_value=’YOUR_SITE_URL’ WHERE option_name=’siteurl’

UPDATE wp_options SET option_value=’http://3.222.27.169′ WHERE option_name=’siteurl’

Show query box
1 row affected. (Query took 0.0024 seconds.)
UPDATE wp_options SET option_value=’http://3.222.27.169′ WHERE option_name=’siteurl’

1 row affected.. yes, but it also says

apache2, mysql and sshd are all running.

check version of mysql with

root@gemini:/var/www/kevwells.com# mysql –version
mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@gemini:/var/www/kevwells.com#

Set the MySQL user password
Type the following commands if you have MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

ALTER USER ‘user-name’@’localhost’ IDENTIFIED BY ‘NEW_USER_PASSWORD’;
FLUSH PRIVILEGES;

You MUST do a FLUSH PRIVILEGES;

MariaDB []> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

that is now working ok!

but the website still can’t connect…

Note the command and its arguments are of the form:

mysql -h locahost -u username -p database

The arguments are

-h followed by the server host name (localhost)
-u followed by the account user name (use your MySQL username)
-p which tells mysql to prompt for a password
database the name of the database (use your database name).
Once mysql is running, you can type SQL statements and see the output in the terminal window.

NOTE -p is NOT PASSWORD!!!

so lets try:

mysql -h locahost -u username -p database

It works, we can connect to the database…

but the website still does not connect!

To reset a user’s MySQL/MariaDB password, log in to the command-line client as root with the command:

mysql -u root -p

Next, reset the user’s password with the command:

update mysql.user set password = MD5(‘(new_password)’) where user = “jdoe”;

Replace new_password with the new password, and jdoe with the username.

Next, show the user’s grants with the command:

show grants for ‘jdoe’@’localhost’;

Replace jdoe with the username. If applicable, change localhost to the host name.

You should get a list of the privileges that user has for the relevant database. It should look something like this:

MariaDB [(none)]> show grants for ‘***’@’localhost’;
+——————————————————————————————————————————-+
| Grants for ***@localhost |
+——————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO `****`@`localhost` IDENTIFIED BY PASSWORD ‘********** ‘ |
| GRANT ALL PRIVILEGES ON `<database>`.* TO `****`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `<database`.* TO `***`@`localhost` |
+——————————————————————————————————————————-+
3 rows in set (0.000 sec)

MariaDB [(none)]>

NOTE: Sensitive data removed from above for security reasons!

to create a new user:

Log into MYSQL as root

mysql -u root
Grant privileges. For a new user, execute:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@’localhost’;
FLUSH PRIVILEGES;

I created a new php user for admin  as the original user could no longer login all of a sudden!

can now login once again to phpmyadmin

Check the MySQL hostname

If the script is hosted on the same server as the MySQL database (e.g. your Linux Cloud Server), the hostname is localhost by default.

To double-check the MySQL hostname, log into the command-line client as root with the command:

mysql -u root -p

Next, show the hostname with the command:

SHOW VARIABLES WHERE Variable_name = ‘hostname’;

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name = ‘hostname’;

+—————+——–+
| Variable_name | Value |
+—————+——–+
| hostname | gemini |
+—————+——–+
1 row in set (0.001 sec)

MariaDB [(none)]>

On Debian and Ubuntu servers the configuration file for MySQL is usually saved at /etc/mysql/. It’s also possible to have user-specific settings stored at /home/<user>/.my.cnf, which would override the global configurations. Check if any user level overrides have been set.

It is commonly advised to have separate usernames for different web applications, so check at least those relevant to your page loading issues. You can open the global configuration file with first of the following two commands below, and the user-specific with the latter by replacing the <user> with a database username.

sudo nano /etc/mysql/my.cnf

sudo nano /home/<user>/.my.cnf

By scrolling down past [client] and [mysqld_safe] settings you’ll find something like the example here.

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

The lines here to pay close attention to are ‘socket’, ‘datadir’ and ‘bind-address’. The parameters in the example above are in their default values, and in most cases, your configuration would look the same. Make sure the settings point to the correct directories so that MySQL can actually find the required files.

The easiest way to check the ‘datadir’ is to use this command below

sudo ls -l /var/lib/mysql/

The output will list all files in that directory, it should contain at least the following plus any databases you have created.

drwx—— 2 mysql root 4096 Aug 5 12:23 mysql
drwx—— 2 mysql mysql 4096 Aug 5 12:29 performance_schema

If the data directory or socket has been moved and MySQL doesn’t know where they are, fix the configuration file to point to the correct directories. You can search for the folders with the following command.

sudo find / -name performance_schema && sudo find / -name mysql.sock

The third parameter you’ll need to check is the bind-address, this is only really relevant if your database needs to be accessed remotely. In Debian and Ubuntu installations the bind is by default set to the loopback address, which prevents database calls from outside the localhost. CentOS doesn’t have the same parameter unless manually set. For any setup where your web service is on a different server to the database, this bind-address should be set to the server’s own private IP.

Step 2 — Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.

First, log in to MySQL as root or another user with sufficient privileges to create new databases:

mysql -u root -p

This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In this example, the new database is called new_database:

CREATE DATABASE new_database;

You’ll see this output confirming the database creation.

Output
Query OK, 1 row affected (0.00 sec)

Then exit the MySQL shell by pressing CTRL+D. From the normal command line, you can import the dump file with the following command:

mysql -u username -p new_database < data-dump.sql

username is the username you can log in to the database with
newdatabase is the name of the freshly created database
data-dump.sql is the data dump file to be imported, located in the current directory

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.

The Actual Cause of the Problem In This Case – WordPress Plugins

I finally ascertained that the cause of the problem was WordPress plugins. Presumably one or more WordPress plugins have been automatically updated and have suddenly resulted in this bug being released.

The Solution 

The solution was to move all plugins to a new directory: plugins.disabled.

Then move each plugin one by one back to the original plugin directory, for each  plugin, login to the wp-admin and activate one by one, test the website content functionality.

Continue in this way, moving plugins back one by one, repeating the procedure for each in turn.

Make sure when you test the pages content to call up different pages each time – else you could be receiving a “false positive” from cached website content from caches in the web-browser or internet provider!

In addition, I made sure to disable the automatic WordPress Plugin update option. As so often happens with these kind of “smart functionality” bright ideas for auto-updating dreamt up by programmers, it ends up in practice causing more problems than the one it was originally intended to solve. 

So, the website is now connecting to the database successfully and is once again able to display the content without errors. Let’s hope we can keep it that way.

Until the next software update.