Wordpress

How to Resolve “Error establishing a database connection”

WordPress - Error establishing a database connection

Error establishing a database connection

“Error establishing a database connection” is a very common error in WordPress when the web server is trying to connect the database and fails to get necessary data back. As a result, the web server shows the error in browser to alert user.

Error establishing a database connection

There’s no more details.

For some late versions of WordPress, you might see an error like this:

WordPress - Error establishing a database connection
WordPress – Error establishing a database connection
It’s more explanatory and helpful.

A Quick Check

Normally, the error is caused by incorrect credentials that we provided in wp-config.php for database connections. We’d better to inspect the information very closely.

define('DB_NAME', 'example-wp-db');
define('DB_USER', 'steven');
define('DB_PASSWORD', '12345678');
define('DB_HOST', 'mysql.example.com');
$table_prefix = 'wp_example_';

According to the above code snippet in wp-config.php, we should check WordPress variables by this order:

  1. DB_HOST
  2. The web server needs to know where the connection should go. In other words, where to find the database server then establish a database connection.

  3. DB_USER
  4. MySQL service needs to know who is trying to be authenticated. Moreover, DB_USER should be authorized with predefined privileges after authentication.

  5. DB_PASSWORD
  6. MySQL service needs to know the password that pairs with DB_USER in order to process single-factor authentication.

  7. DB_NAME
  8. MySQL service needs to know which database (DB_NAME) that DB_USER is trying to select for further data manipulation.

  9. $table_prefix
  10. With the table prefix, the web server of WordPress are able to specify the correct table set that it wants to use.

If you saw no problem in the above and still got “Error establishing a database connection”, then I think you need a thorough check on every step in the way from the web server to the database. The real causes of this error may vary from website to website.

A Thorough Check

In the way to establish a database connection, there’re many factors that may block your web server to get the data. Here I list 7 steps of a connection that should be done in its route to MySQL server.

WordPress - 7 Steps to Make DB Connections
WordPress – 7 Steps to Make DB Connections
  1. Resolve Host (DB_HOST) Name
  2. Reach Host (DB_HOST) Server
  3. Pass MySQL Port
  4. Access MySQL Service
  5. Authenticate User (DB_USER + DB_PASSWORD)
  6. Select Database (DB_NAME)
  7. Use Tables ($table_prefix)

Our goal is to make web server establish a database connection successfully and retrieve data from MySQL server and respond to visitors without errors.

1. Resolve Host (DB_HOST) Name

What is domain name resolution? It translates human-readable domain name into computer-readable and real address, either IPv4 or IPv6 on the internet for successful networking.

If the value of DB_HOST is localhost, you can skip the step. It means that the web server should try to find the database server within the same machine.

In some cases, the DB_HOST is a full qualified domain name, e.g. mysql.example.com. It should be resolved by domain name server (DNS) at run-time before connections leave the web server.

By using a simple nslookup command in the web server, we can learn the real address of the domain name.

[root@web ~]# nslookup mysql.example.com 8.8.8.8
Server:         8.8.8.8
Address:        8.8.8.8#53

Non-authoritative answer:
Name:   mysql.example.com
Address: 10.1.12.123

In the above, we used an internet DNS server, 8.8.8.8 to resolve mysql.example.com into 10.1.12.123. For LAN environment, you should use your local DNS to resolve it.

If the domain name is unresolvable, it’s usually a DNS configuration or records propagation problem.

2. Reach Host (DB_HOST) Server

DB_HOST may not be reachable due to network problems or server down. We can use ping command to test the reachability of MySQL server. Once again, if DB_HOST=localhost, you can skip this step. [root@web ~]# ping mysql.example.com
PING mysql.example.com (10.1.12.123) 56(84) bytes of data.
64 bytes from mysql.example.com (10.1.12.123): icmp_seq=1 ttl=48 time=246 ms
64 bytes from mysql.example.com (10.1.12.123): icmp_seq=2 ttl=48 time=244 ms
...

Unreachable server could be a serious problem to WordPress administrators. It means that we lost traffic during the downtime. Practically, we should look for servers with 99.99% or above of availability (up-time) to host our website. In my opinion, three-nines (99.9%) guarantee is no longer good enough in state of the art.

3. Pass MySQL Port

The default service port of MySQL is 3306. Is there any chance that the port has been closed by accidents? You can test the port by telnet and see whether it is open or close. I have talked about it in another post: How To Check Open Port By Telnet.

In short, if the telnet command failed immediately, then the port is closed. Otherwise, it’s open.

An easily neglected problem is that someone has changed MySQL port on the server from 3306 to another and we know nothing about it.

4. Access MySQL Service

After passing through port 3306, we should have accessed MySQL service. If there no processes were serving the connections, which is, MySQL service is down, we still got “Error establishing a database connection”.

If you were using phpMyAdmin to logon MySQL service, you might see the error “Cannot log in to the MySQL server” on the screen:

phpMyAdmin - Cannot log in to the MySQL server
phpMyAdmin – Cannot log in to the MySQL server

Then how to make sure MySQL service is online? In practice, we check it on Linux like this:

  • For Enterprise Linux 6
  • [root@el6 ~]# service mysqld status
  • For Enterprise Linux 7
  • [root@el7 ~]# systemctl status mysqld
if you have the privilege to perform the check.

5. Authenticate User (DB_USER + DB_PASSWORD)

If MySQL service is online, let’s move on. Now we start to focus on the authentication problem. The most common mistake is that, we might have changed password by some other tools but we forgot to reflect it in wp-config.php.

The solution to authentication problem is to modify the password in MySQL server (or cpanel) to be consistent with the current one in wp-config.php, or vice versa.

Also, you may use phpMyAdmin to make sure your credentials are all correct. If anything is wrong, you should see “Cannot log in to the MySQL server” message in browser.

The last resort, create a new DB_USER dedicated for connecting MySQL service, then reflects the new user credentials in wp-config.php. Just don’t forget to grant proper privileges for the new DB_USER.

If DB_USER is authenticated, it means that the database connection is established, theoretically. But the thing is not over.

6. Select Database (DB_NAME)

What value you provided for DB_NAME is the database name (i.e. schema name) that you want to use. If you’re pretty sure that the DB_NAME does exist in MySQL database, you should check what privileges held by DB_USER. Lack of privileges on DB_NAME will result “Error establishing a database connection” as well. Please grant proper privileges to DB_USER if necessary.

For some late versions of WordPress, probably 4.9 or later, you might see an error more specific than “Error establishing a database connection”:

WordPress - Can't select database
WordPress – Can’t select database
Impressive! WordPress starts to report such error more granularly.

7. Use Tables ($table_prefix)

Variable $table_prefix gives DB_USER a hint to find all required tables. Any mispelled prefix also results “Error establishing a database connection”.

A more complicated problem related to $table_prefix happens in multisite mode, which mode allows us to create multiple WordPress blogs within same domain name. I’ll talk about it below.

WordPress Multisite Network

A multisite network is a collection of sites that all share the same WordPress installation including plugins and themes.

Wordpress Network Multisite
WordPress Network Multisite

Some days ago, I met “Error establishing a database connection” problem when I enabled multisite feature in WordPress. Eventually, it’s a table prefix related problem.

Phenomenon

After configuring and enabling the network feature according to WordPress codex documentation, I tried to connect to multisite dashboard for the first time. But it failed with the error message: Error establishing a database connection in the browser.

Since I was able to establish a WordPress single site successfully before configuring multisite, so I don’t think the connection information that I provided in wp-config.php would be a problem.

Recheck Multisite Variables

I’m pretty sure that I have followed all instructions of WordPress Codex for creating a network. However, I checked the newly added multisite settings in wp-config.php again and perhaps I can find some clues to solve this problem. define('WP_ALLOW_MULTISITE', true);
define('SUBDOMAIN_INSTALL', true);
define('DOMAIN_CURRENT_SITE', 'example.com');
define('PATH_CURRENT_SITE', '/');
define('SITE_ID_CURRENT_SITE', 1);
define('BLOG_ID_CURRENT_SITE', 1);

As we can see, SITE_ID_CURRENT_SITE always starts from number 1 as WordPress Codex recommended. Do we miss something that should contain the incremental number, I mean SITE_ID_CURRENT_SITE? In fact, I don’t see any table names that contain the site ID. That could be the problem that caused “Error establishing a database connection” in browsers.

Solution to Table Prefix

For solving error establishing a database connection, I copied all the original tables to those names with specific format as below:

$table_prefix + _1 + <object_name>
Which means, I inserted the site ID: _1 into the middle of table name for every table. For examples, I changed the table wp_example_posts into wp_example_1_posts.

How to Copy MySQL Tables?

Now our question is: how to copy or duplicate MySQL tables? You can check the post below for more instructions.
3 Steps to Duplicate a MySQL Table Using CREATE TABLE LIKE

I take the first table in the above chart for an example, we can do this to copy a table:

mysql> CREATE TABLE wp_example_1_blog_versions LIKE wp_example_blog_versions;
mysql> INSERT wp_example_1_blog_versions SELECT * FROM wp_example_blog_versions;

Conclusion

By copying tables and changing tables’ name, I solved Error establishing a database connection.

Please note that, the URL for multisite network dashboard would be: http://example.com/wp-admin/network/

Further reading: how to remove uncategorized from posts in WordPress.

Leave a Reply

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