Using MySQL With WordPress

If you manage a WordPress website, you may have found yourself wanting to know more about how its database functions. It’s useful to understand how your site works behind the scenes, and there may even be times when you need to access your site’s database directly. 

That’s where MySQL comes in. Structured Query Language (SQL) is the management system for WordPress’ databases. We’ll go into greater detail later, but suffice it to say that the database is the brains of your website and MySQL is the nervous system, sending commands back and forth to retrieve information and execute operations. 

In this article, we’ll provide a detailed explanation of how MySQL works in regards to WordPress. Additionally, we’ll review several database errors you might encounter and explain how to remedy them. Let’s dive right in!

MySQL Explained

You may have heard a database described before as a filing cabinet, a place to hold all of your site’s important information in categorized slots. In this analogy, the individual folders within the cabinet are the database tables that hold information.

As for MySQL, it’s how requests are made to place or remove data within these folders. You can also use MySQL to set rules for what is allowed to be placed in each part of the filing cabinet.  

MySQL can’t operate alone, however. It is part of a stack of software applications used to create websites. The other components include Linux, Apache, MySQL, and PHP (LAMP). Together, they form the ‘dream team’ of the open-source programming world. 

Using MySQL for WordPress

When it comes to WordPress, the PHP scripting language is used to send and retrieve information from your MySQL database. These two elements handle everything from logging in site users, to storing theme and plugin information for dynamic content display. 

MySQL uses table structures to store data. Most web hosts come with a MySQL user interface software called phpMyAdmin. This free and open-source piece of software makes it easy to run database commands. It enables you to edit, delete, or create tables, rows, and fields:

During a standard WordPress installation, 12 tables are created in your database. You can see them listed on the left in the above image. These tables include key information required for your site to function.

For example, the wp_options table stores the options you can access and control from the Settings menu in your WordPress dashboard. It’s also the home to your various widget-related settings.

As you can see in the phpMyAdmin panel, information about all the data contained in any table will be visible when you click on its name. This includes any values or parameters associated with specific fields.

MySQL Plugins/Extensions

While there aren’t many plugins that directly enhance MySQL functionality within WordPress, there are a few tools worth checking out if you plan on manually changing or altering your database often.

For example, WP phpMyAdmin is a useful plugin if you want to be able to access your site’s phpMyAdmin panel from within your WordPress dashboard, rather than through your web host’s control panel:

This is a simple but popular solution that offers convenient access to your database. That can also make it a bit easier to resolve MySQL-related errors, as we’ll see below.

Common MySQL Errors in WordPress (And How to Solve Them)

Database errors can happen to anyone, but can be frustrating to deal with. To make your life simpler, it helps to understand the most common problems that can arise, as well as each one’s likely solutions. Let’s look at five examples of typical MySQL issues.

1. Error Establishing a Database Connection

If you encounter a message like “Error establishing a database connection” when trying to access your site, it likely means that your URL is pointing to the wrong database or your site is encountering a connection error.

A database connection error means that you won’t be able to access your website. You’ll likely also be blocked from logging in to your administration panel. In this scenario, your wp-config.php file is most likely the culprit.

The first way you can resolve this issue is by accessing your site’s hosting account and restoring a recent backup. This will reset your wp-config.php file, giving you access to your administration panel. If it’s not possible to access a backup of your site, however, there is another solution.

You can also edit your wp-config.php file using a Secure File Transfer Protocol (SFTP) application like FileZilla. Once you connect to your website, your wp-config.php file can be found in the root directory of your site’s files.

If you open the file, you’ll see all your database information near the top. You can then check to see if there is a mistake in your database name, user name, or any other information regarding your site.

You may need to contact your host if you are unsure of what this information should include. You’ll be mostly concerned with verifying the correct “DB_USER”, “DB_PASSWORD”, and “DB_HOST”. Once you complete the necessary corrections, be sure to save your changes and upload the file back to your site’s server. 

2. Error #1005

The #1005 error is a server-side issue, rather than a problem with your site. It occurs when a necessary table could not be created. Depending on the details of the message string, you may get more information about the cause of the error.

For instance, your error message might look like this: 

Can't create table '%s' (errno: %d)

The most common “errno” with this particular issue is “(errno: 150)”. This means there is a foreign key constraint issue. In other words, it’s likely that the table you are trying to create conflicts with a set constraint. The error is preventing inconsistencies from occurring in data between multiple tables. 

In order to solve a foreign key constraint error, you’ll need to do a little investigating and check your database tables for inconsistencies. This means you’ll want to: 

• Make sure the tables involved are all referencing the same database engine.
• Check to verify that the fields you are indexing all have the same type or length. 

This error typically involves simple inconsistencies, and simply requires a little time and patience to rectify.

3. Error #1213 

This next error will typically appear alongside an “ER_LOCK_DEADLOCK” symbol. You should also receive a message that looks like this: “Deadlock found when trying to get lock; try restarting transaction”. Fortunately, this is an error that comes with instructions you can use to solve it quickly. 

When you execute a transaction that encounters a deadlock, your transaction will stop and roll back. This is due to an SQL command that stops the transaction and undoes any changes it made. You can think of this as an automatic ‘undo’ function that enables your application to stop and take corrective action. 

In this case, you’ll just need to run the transaction again. The rollback will have released the locks that triggered the deadlock, and the transaction should now complete successfully. 

4. Error #1064 

If you receive a #1064 error, you’ll also see a symbol display that reads “ER_PARSE_ERROR”. This means you have a mistake in your syntax. This might be due to a typo, or the use of an outdated command.

You should also receive a message with some helpful information, such as: 

%s near '%s' at line %d

This will direct you to an approximate line in your query, which you can reference to locate and fix the syntax error. There are also some tools available online to help you find errors in particularly long queries. 

5. Error # 2003 

Finally, a #2003 error results when you can’t connect to the MySQL server. The error symbol will indicate a host connection issue: 

CR_CONN_HOST_ERROR

In other words, this error message lets you know that your network connection has been refused. Your first step in correcting the issue will be to determine whether a MySQL server is running. Next, you’ll want to make sure the network connection and ports you indicated are the same that you configured on the server. 

You can do this through your phpMyAdmin panel, by going to Status > Monitor and viewing the activity on your server for issues. If your server is not running, you’ll need to go through the steps for restarting it as well.

If your mysqld process is running, you’ll need to have some deeper knowledge of the server connection to the network, so you can start working through possible disruptions to your connection. 

Level Up Your Knowledge With WP Engine

While not everyone will be spending time with their site’s MySQL server regularly, it’s useful to understand where to go for help if you encounter an error. That’s why it helps to know where to find the best advice and developer resources online.

To help you keep your site running smoothly at all times, we offer a variety of hosting solutions and tools. This includes the WP Engine Error Log, which helps you provide your visitors with the best digital experience possible!