Common Issues – MySQL Connectivity Issues

This week on Common Issues and their Solutions, we will be tackling the common, yet annoying, MySQL connection issues that many of clients see daily. This issue is typically much more simple than it may seem so let’s see if we can hammer it out together.

Client submits a ticket saying his WordPress installation is not working correctly and just showing a very plain error “WordPress cannot connect to Database”. The error is nondescript but we can use a set route to find the solution quickly.

Step 1: Check the MySQL service. You would be surprised how many times the MySQL service has just died or is overloaded and just needs to be restarted. This is a quick and painless restart typically so it is not a pain to do.

Check MySQL Status with: /etc/init.d/mysql status -OR- Through Service Status in WHM

Restart MySQL with: /scripts/restartsrv_mysql -OR- Through Restart MySQL in WHM

If the status is either okay or restarting the service did not fix the problem, move on to step 2.

Step 2: Check the user account and ensure they are connecting to the database correctly.

To check this with the least amount of steps and most accuracy, first check the configuration file for the given software the client is having an issue with. Since our example is WordPress, we will go on that. WordPress stores all of its database connection information in a flat file named wp-config.php in the root directory of the WordPress installation. You will find the following variables:

define('DB_NAME', 'dennis_wrdp1');
define('DB_USER', 'dennis_wrdp1');
define('DB_PASSWORD', 's3kr3t!1');

These three variables are pretty obviously named as far as letting you know what they do. Once you have these variables, test out the connection by using the following command:

mysql -u USERNAME -p

MySQL will then prompt you for the password. Provide that and if it shows the following error:

ERROR 1045 (28000): Access denied for user 'dennis_wrdp1'@'localhost' (using password: YES)

Then we have located the issue. To fix it, we will need to repair the password that MySQL is expecting. The easiest way to do this is just use the following commands:

mysql // enter mysql as root
USE mysql; // select the mysql database to manipulate
UPDATE user SET password=PASSWORD('INSERTNEWPASSHERE') where User='USERNAMEHERE'; // updates the password
FLUSH privileges; // push all changes live
exit;

-OR- in WHM you can use the “Change a User or Database Password” tool and select the user in the drop down and insert the correct password into the “New Password” box.

Please note: These steps should only be taken given that the user is using a MySQL user account OTHER than their main account (ie. If in the example I had been using “dennis” to connect to my database). In that case, you will need to get the user’s cPanel password and update the WordPress configuration to reflect it rather then this route as the user has just updated his cPanel password but not the configuration for their WordPress installation.

This should solve 99% of MySQL connection problems and the hardest part will just be locating the DB configuration file. At times, the error message says it can’t connect and the error shows the mysql_connect line but hunting down the actual variables it uses is a bit of an easter egg hunt as the user may have multiple useless files and variables that could be scattered wildly for no apparent reason. Being able to follow the trail of included files, and their included files to find the missing information can be complex but valuable. My one big tip is: If there is not a clear ‘variables.php’ or ‘vars.php’ or ‘db.php’ but rather just ‘header.php’, ‘body.php’, and ‘footer.php’, check the header.php for an include to a more obvious file.

Note: If you cannot find the configuration file in about 5 minutes, then the user is hiding it far too well for a novice and they can likely assist you in finding it. Ask them for more information and they will likely be able to help. Also, most commercial PHP applications such as WordPress and Drupal have a set file such as Configuration.php or wp_config.php that contain all of this information. Check these files if you know the PHP application is a typical commercial application.

That rounds up this week’ Common Issues and their solutions. If you have any questions on this email, please feel free to contact me and I’ll do my best to help you out.

One Reply to “Common Issues – MySQL Connectivity Issues”

Leave a Reply

Your email address will not be published. Required fields are marked *