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.

Common Issues – Hostname A Record

The first week’s topic is the dreaded Hostname A Record Email clients get and panic about because of cPanel’s lovely alarmist email. This issue is really simple typically but does have a 3 part resolve and all 3 must be perfect for the resolve to function.

Our example client is a VPS client (vps.clientserver.com) and he was receiving the invalid IP Hostname Email. Technician A saw this and did fix one of the major 3 issues; an A record for the hostname. Ensuring that the server has the proper A Record set up can be done one of two ways:

1) GUI Route – Go into WHM and use the “Add A Record for Hostname” tool. Quick and Easy
2) Console Route – nano /var/named/HOSTNAME.db -OR- DOMAIN.db (if the domain for the hostname exists on the server) and ensure that the A record exists and is pointing to the main IP correctly.

The second step of this resolve is checking the /etc/hosts file. Below was the /etc/hosts file on this server before I fixed it:

0.0.0.0             vps.clientserver.com vps vps.otherserver.com
# Auto-generated hostname. Please do not remove this comment.
123.456.789.123            vps
127.0.0.1               localhost

As you can see, the 0.0.0.0 entry is entirely incorrect and needs to be removed and the actual IP host is wrong too, only containing the short hostname and not the long. Below is the corrected /etc/hosts file:

# Auto-generated hostname. Please do not remove this comment.
123.456.789.123        vps.clientserver.com vps
127.0.0.1        localhost.localdomain localhost

This is how pretty much all /etc/hosts files should look (obviously customized for the server) and should not vary too much from this format. The localhost.localdomain is just a preference thing for me and not absolutely necessary.

The final step to this 3 step repair is to check the /etc/resolv.conf file which tells the server what nameservers to use to resolve domains from the server (not domains on the server note). Below was the /etc/resolv.conf before repair:

search localhost

The search localhost is not really needed and can even cause issues that I have seen in the past. Below is the fixed /etc/resolv.conf:

nameserver 4.2.2.1
nameserver 4.2.2.2

This contains 2 working nameservers and will help the server resolve domains without issue.

Once all of these fixes are made, all you need to do is rndc flush, rndc reload, and restart named for the changes to be committed. After that, the client should no longer see cPanel’s alarmist message about the A record being bad.

And that’s all for this week. Stay tuned for another possible episode next week in Common Issues and their Solutions!