Today I learned… in code

A lot happens between "Hello World" and "Supreme Master Programmer". Here, we share little discoveries made along the way. The more frustrating something was, the more likely it is to end up here on this site!

Category: Digital Ocean

Error establishing database connection is my most hated WordPress problem – it’s so cryptic and so many things can cause it. I run about a dozen WordPress blogs: some are on shared hosting, some are on virtual private servers (with Digital Ocean), and nearly all of them have had this problem at some time or another. This article documents what I do when it happens to me.

If it’s any comfort, I’ve never not solved this problem (eventually). It’s definitely fixable, but there are a lot of things that can cause it, so if nothing here helps you just keep digging around in the Googles – and good luck.

Before you do anything, turn on error messages and see what the problem actually is

Get into your website’s files, either through FTP or your host’s control panel, and turn on debug mode in wp-config.php. This file is in your WordPress installation directory.

Change this line to true:

define('WP_DEBUG', true);

Now go back to http://sitename.com/wp-admin and get those juicy error messages.

This step alone can save you a lot of frustration as you debug the actual cause of your WordPress blog’s Error Establishing Database Connection problem.

Possible fix #1: make sure DB_USER and DB_PASSWORD match what your host has

The vast majority of the times I run into Error Establishing Database Connection on a shared hosting site, it’s because something (I don’t know what) caused the DB_PASSWORD in wp-config.php to become out of sync with the password my host has for that user. This particular flavor of the error connecting to db problem seems to only affect my sites that are on shared hosting (most recently, it happened to a site I host on lunarpages.com).

Basically, what wp-config.php has for DB_USER and DB_PASSWORD has to match what your host has saved for that database and particular user.

By turning on WP_DEBUG in step 2, I was privy to the following error messages when attempting to access http://mysite.com/wp-admin:

If you know what your DB_USER and DB_PASSWORD are supposed to be, maybe you’ll spot a discrepancy here. Chances are, you don’t know what’s supposed to go here, so you can’t tell just by looking if it’s right. That’s okay, I don’t either, but it’s easy to get everything matching.

First, if you have CPanel on your host, you can log into CPanel and go into MySQL Databases to see a list of users associated with your database(s).

Next, find your database and look in the Privileged Users column. One name from the Privileged Users column has to match the username given in wp-config.php.

If the user your wp-config.php file is expecting is already in this column, you’re good to go to the next step where you reset its password.

If you don’t have the same user your wp-config.php expects, either add that user here or change wp-config.php to reference a user you do have.

Still on the MySQL Databases page, scroll down into “Current Users” and find the user your db is using. Click Set Password.

I just change the user’s password to something randomly generated, it doesn’t matter. Copy that password and paste it right into wp-config.php

Try the site again: if it works now, you’ve resolved your “access denied” error and may now have full access to your WordPress site again.

Possible fix #2: it could be a bad plugin

This one is easy to test the fix for but I’ve only seen it be the problem once, and it happened right after I messed with plugins so the cause was obvious. However, with more plugins and WP things going to “auto update” these days, I could see how this might crop up independent of blog-owner interaction.

Rename the plugins folder.

I log into my site’s file manager via my hosting service’s website or FTP, navigate to /wp-content and rename the folder called plugins. (Don’t delete it, just put an X at the end or something.)

plugins > pluginsX

Try the site again – if it loads, your problem is one of your plugins. You can narrow it down by renaming plugins back to its normal name and then turning plugins off in groups to narrow it down to a specific one.

If your site doesn’t load, put plugins back to normal and go to the next step.

Possible fix #3: maybe your MySQL service croaked – try restarting it

This particular flavor of “Error Establishing Database Connection” seems to affect my Digital Ocean (VPS) hosted blogs (not my shared hosting blogs). There are many reasons why MySQL can crash, but when your WP site is down and you’re losing money by the hour, getting it back online is probably your #1 priority.

Since my Digital Ocean hosting runs on Linux, I log in to the virtual console and check if mysql is running with this command:

mysqladmin -u root -p status

This command brought MySQL back up:

service mysql start

Now, as to why it crashed in the first place, that could be any number of things, and chances are, MySQL will go right back down again as soon as the same conditions return.

The various fixes I’ve applied in effort to stop chronic MySQL crashes on Digital Ocean merit their own article someday, but for the sake of helping anyone who might find this, here’s a brief overview of stuff I’ve done on my VPS WordPress to try to stop frequent MySQL crashes.

I tried to figure out what was using up memory by logging into my droplet’s virtual console and looking at all the active processes sorted by what resources they are consuming. The command to see that chart is top.

You should now see your process list sorted with the most memory intensive processes at the top. What you find here will help you Google for solutions.

For me, mysqld is always at the top, soaking up all the memory, so I focused on that when I was trying to fix chronic “Error Establishing Database Connection” problems on my Digital Ocean WordPress blog. After mysqld was always a whole ton of apache2 instances.

Restarting the droplet has helped, too. One time I cleared my blog’s MaxCDN cache and that immediately took the site down and replaced it with Error Establishing Database Connection. When that happened, restarting the droplet brought it back up.

To be honest, MySQL crashes on Digital Ocean are kind of an ongoing issue for my most popular WordPress blog, but I’ve managed to lengthen the time between crashes/restarts with the above steps.

Even more help with WordPress db error (articles, threads, etc)

WPBeginner has the Internet’s de facto go-to article on the subject, and they also report that somehow the database credentials on their shared host site got reset. They also have some solutions I’ve never had work for me but are worth looking into if nothing in this article worked for you.

This guide was written after I completed the process of hooking up MaxCDN to my Digital Ocean hosted WordPress blog.

This wasn’t a straightforward process, partially due to my own errors and partially because I was using WP Super Cache which I’m pretty sure just doesn’t cooperate with MaxCDN for reasons I may never understand. However, I now have it working so here’s my guide to everything I did.

Why I wanted a CDN: To speed up my top money-making property, which is also my slowest because it’s got long articles and lots of images.

I chose MaxCDN because it seemed to have a lot of good reviews and at about $10/month was reasonably priced.

Setting up MaxCDN with W3 Total Cache and a custom CNAME on Digital Ocean

I signed up and was disappointed to see that the URL MaxCDN gave me by default included the business name I used when I signed up. I wanted cdn.mysitename.com, but by default MaxCDN gave me cdn1.mybusinessname.netdna-cdn.com. MaxCDN calls this the “branded domain” and you can use it as-is, but I would guess most people want to customize it.

My business name and my website name are not the same and I didn’t want to expose the former in the latter. (They don’t tell you they’ll use your business name this way when you sign up, nor do they let you change it once you opened your account. Ugh.)

Fortunately, you can set up a custom domain and use that instead, and my steps below include that process.

Step 1:Enter your custom domain into the pull zone settings. Go to Pull Zones > Settings and fill in the Custom Domains field with cdn.yoursitehere.com (or cdn1.yoursitehere.com, or whatever you prefer. The important thing is that all three parts of the url are present.)

Step 2: Get into Digital Ocean’s record management. Inside Digital Ocean’s droplet management page, go to Networking and then go to Domains. Click the magnifying glass next to the domain you’re adding this CDN to to get into its records. More help adding/editing domain records in Digital Ocean.

Look in Networking, Domains

Step 3: Add a CNAME record to your droplet’s domain. If you want cdn1.yourdomain.com, fill the form out like this:

Enter Name: cdn1

Enter hostname: cdn1.yourdomain.netdna-cdn.com (the “branded” domain that Digital Ocean gave you by default)

Be sure to click the Create CNAME Record button to actually add it to the list of records.

Assuming nothing else is conflicting (you didn’t leave your DNS hooked up to CloudFlare like I did, you don’t have a competing www.yourdomain.com record like I did, etc), this should happen in 20 minutes or less.

At first, I was using WP Super Cache and every time I turned on the CDN feature my site’s styling and images disappeared. I fought with this for a while, got good and frustrated, then tried W3 Total Cache like MaxCDN suggests and WOW – it was like night and day. I followed MaxCDN’s tutorial and basically, it just worked. Be sure to whitelist your site’s IP (go into Terminal or command prompt and ping www.yoursite.com to get your site’s IP if you don’t know it, or look in Digital Ocean’s droplet list).

If everything’s working, you should be able to load your site (yoursite.com) at its usual url (not the cdn url) and look in the network tab of your browser to see responses coming in from the CDN url (cdn.yoursite.com).

Fast way to check: right click any image on your site and view it in another tab. Its url should be your cdn’s url.

If you don’t have any images, you can see this in Chrome by right clicking to Inspect and then click over to the Network tab before loading your site. Hover over some resources (like .css resource) and you should see cdn1.yourdomain.com.

You should also start to see improvements to your page’s load time immediately.

If you don’t see the changes right away: While the cdn1.mysite.com changeover was observable right away on the computer I was working on, it wasn’t on my laptop. The cause seems to be the computer’s own DNS settings. By switching my laptop’s DNS settings to Google’s, I was able to see the most up-to-date (ie: not cached) version of the website.

(Alternatively, waiting a while – up to a day – should resolve it with no changes to your computer.)

Pingdom results, before and after MaxCDN

Here’s a couple of “before” load times on my website, taken less than a minute apart and both from New York City. The thing I often notice on Pingdom’s Website Speed Test is how much variance there can be in load time across multiple tests when all other factors remain the same (location testing from, time of day, etc).

Site speed: before

Here, there’s a huge difference in load time just on these two “before” tests.

The kind of extreme difference in load time seen above is why I like to run several Pingdom tests when collecting my “before” data before making a change that I think should effect load time. Here’s another test, this one from San Jose, California.

Site speed: after

Whoo! Look at that, down to <2 seconds since turning on MaxCDN:

Here’s an even faster one, best one I saw in my several tests:

And one from San Jose:

So far I like MaxCDN. Their online support is very responsive. I sent a detailed plea for help on a Sunday afternoon in the U.S. and had a (reasonably) customized response within an hour. Their documentation is plentiful and thorough. They include many examples and their screenshots are up-to-date with their current interface.

The real test will be in seeing if getting the site speed down to ~2 seconds has any noticeable effect on the site’s Google rank and total conversions (sales).

CDN Traffic Boost?

Totally unscientific study here, but my site’s traffic pulled out of a slump as soon as my CDN hookup went live. (March 17 is just beginning as I post this update.)