How to fix the mysqldump access denied process privilege error

How to fix the mysqldump process privilege error after applying a recent MySQL update.

You may receive a new ‘Access denied’ error when trying to dump your MySQL database:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

You were able to export database before so what caused this? Here’s the answer: updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change:

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

This error appears when running mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s export command.

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.

Solutions for fixing the mysqldump process privilege error

The mysqldump command requires at least the following privilege assigned to the user:

  • SELECT privilege for dumped tables
  • SHOW VIEW for dumped views
  • TRIGGER for dumped triggers
  • LOCK TABLES if you don’t use the --single-transaction option
  • PROCESS if you don’t use the --no-tablespaces option

The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:

  1. Updating the privileges for your database user.
  2. Runing mysqldump with the --no-tablespaces option.

Solution 1: Update the user privileges

Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.

To grant the PROCESS privilege, log in as an administrator user and run the following query:

GRANT PROCESS ON *.* TO [email protected];

Node that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

To grant the privilege to all databases you must use the ON *.* ... syntax.

Solution 2: Use the --no-tablespaces option

If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the --no-tablespaces option when dumping your database.

mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql

What are MySQL tablespaces?

We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.

The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.

Oracle documentation: Introduction to Tablespaces, Datafiles, and Control Files
Diagram source: Oracle Concepts documentation

You can therefore use the --no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.

About the access mysqldump denied PROCESS privilege error

mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run mysqldump without the PROCESS privilege. However, users running mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running mysqldump without PROCESS privilege ends up giving you an Access denied error.

Be careful with the PROCESS privilege

According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.

It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.

For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.

A guide to WordPress Backups and Staging

This WordPress Backups and Staging Guide is a guest post by .

If you’ve run a WordPress site long enough, you’d know how making changes to it can be tricky. Sometimes, even minor changes can cause major glitches. This makes backups and staging such lifesavers!

Every WordPress site should have backup and staging solutions in place. And not just any solution, one that actually works! I say this because in my experience, relying on host backups and staging environments have too many limitations and a tedious process. So I turned to plugins, but there too, I found so many options, it was hard to pick the right one.

In my quest to get a permanent solution, I stumbled upon BlogVault. It was easy-to-use and reliable with a great support team behind it. Ever since, it has been my go-to for WordPress backups and staging.

So today, I’m sharing everything what I think WordPress site owners should know about backups and staging and why BlogVault works best.

Let’s begin with a few basics.

Difference Between Backup and Staging

Many ask If I have a backup, why do I need staging? Well, the two solve very different problems. Backups are for reverting to a previous version when a problem arises on the site.

For example, you run an update on your website and it causes your site to malfunction. You find that plugins are not compatible with the new version of WordPress. It would take considerable time to update these plugins or find replacements. To solve the issue immediately and get your site back to normal, you can restore your backup.

Backups come in handy when things go wrong such as botched updates, human errors, presence of malware, and so on.

Coming to staging, it enables you to create a clone of your WordPress website. This is done specifically for development. You can try out new themes, plugins, designs and layouts. You can test out updates and even test restore your backup! None of the changes you make will affect your live site.

There are staging solutions that have a feature to merge your changes. This is extremely handy because you might make multiple changes and it would take time to replicate the same on your live site. So you can just push the changes to your live site. Simply put, staging enables progress!

Now that you know why you need both solutions, let’s check out the backup solutions available for WordPress sites.

WordPress Backup Solutions

There are many options to backup your WordPress site, but not all of them are easy to use or reliable. The main options available to back up your site are – manually, using a plugin, or through your web host.

Manual Backups

For this, you need to access your web hosting account. Once you’ve logged in, go to cPanel > File Manager > Public_Html. Here, you’ll see all your WordPress files. Simply compress and download them.

Hosting Control Panel Manual Backups

It seems simple enough. But there are issues with this method. For one, the process is time-consuming. Spending time taking manual backups regularly is simply not feasible when you have tons of other things to focus on. Plus, you need to take responsibility to encrypt your data and store the backup locally. A backup has all the information on your website and needs to be stored securely. If hackers get their hands on an unencrypted backup, it’s already a data breach, but you can be sure your site will be hacked as well.

But above all, the biggest hassle with this method is the restoration process. Sometimes, the backup just doesn’t work at all. Other times, you restore your site only to find it malfunctioning or missing functionalities. Which means more time wasted on troubleshooting and debugging troubles. This is why I prefer using a plugin. But among these too, there are so many options available, how do you choose one?

Well, after trying out a couple of them, I stuck with BlogVault. It’s great for backups and even offers inbuilt staging at no additional cost.

Backups with a WordPress Plugin – BlogVault

To start off, setting up BlogVault was easy to use – with a simple install and activate process. Once you sign up, the backup automatically runs. It gives you the following options which I think is not only cool but essential for WordPress:

  • The option to schedule your backups. Plus, you can take a backup on demand whenever you want, any number of times. This is good to have when you want to make major changes to your site or roll out big updates. Taking a backup before any major change is absolutely necessary.
  • It’s all automated so you don’t have to worry about the technicalities.
  • The restore process is just a few clicks. It takes a few minutes and it’s guaranteed to work. So you needn’t worry about your site breaking or losing data.
  • The process is handled offsite on their own servers so it doesn’t affect the performance or speed of your site. Plus, it doesn’t take a full backup every single time. It smartly copies one full backup and then subsequently, only the changes made each time are backed up.
  • Lastly, your backup is encrypted and multiple copies of it are stored securely. That takes away the pain of storage.
  • Creating a backup using the BlogVault WordPress Backup Plugin

    Apart from this, there are handy features like 365-day archives, real-time backups, website management, site migrations, multiple site management, uptime monitoring, and staging.

    Next, you can also back up your website using your web host itself. I’ve checked out two of the most popular ones.

    Backups with your Host – WP Engine

    WP Engine takes backups for all environments by default. This includes production staging, development, and legacy staging. It’s automated, encrypted, and stored offsite on Amazon S3. You can also take manual backups. It keeps up to 40 backup points. Older data up to 60 points may be accessible but you need to contact their support team for that.

    To access a backup, log into your user portal. Go to sites > environment name > backup points. You’ll find your backups here. You can also create a backup on demand.

    To restore, simply click on the backup point you want and click the Restore button.

    Creating backups in WP Engine
    (Image source: WP Engine)

    The process seems simple enough. But WP Engine also has some drawbacks. The restore process can take several minutes to several hours. And it’s important to note that they do not copy all files of your site. The following files aren’t included:

    Files excluded with WP Engine backups

    Additionally, it’s a destructive backup which means, during the restoration process, all content is overwritten. So, if you have files on your website that are not present in your backup, they will be wiped out. Your site will be restored to the contents of the backup alone.

    Backups with your Host – Kinsta

    Kinsta provides daily automatic backups for your WordPress site. It also has system-generated backups for all the sites listed in your account. The backup is a snapshot of your website’s files, database, redirects, and Nginx configuration. When you restore your backup up, all of these elements will be rolled back to when the backup was taken.

    You can also create manual backups if you want one immediately and don’t want to wait for the scheduled one to run. You need to visit the ‘Backups’ tab, click on the ‘Manual’ tab. Here, you’ll find the ‘Backup Now’ button for on-demand backups.

    Creating backups on Kinsta
    (Image source: Kinsta)

    The duration for which your backups are stored depends on your hosting plan. It can vary between 14 days and 30 days. Kinsta also allows you to increase the frequency of your backups to hourly and 6-hour backups at a premium.

    When it comes to restoring your site, you can simply click on the ‘Restore’ button next to the backup of your choice. While the restore takes place, you cannot access your site. The process can take a few minutes to a few hours.

    What I liked about Kinsta is that it takes a backup of your site before restoration. So you can undo the restore and get back your site to as it was.

    That brings up to the end of backups. With a backup solution in place, we can move on to staging. Here, I’ll touch upon – the manual method, show you how you can do this with the BlogVault plugin, and also talk about staging with your hosting provider.

    WordPress Staging Solutions

    Staging a site can be done manually through your hosting account. But it’s riddled with problems. The process to create one is quite technical, and for a regular WordPress user, it’s not a feasible option.

    Next, if you want to merge the changes you made, you need to download the updated files from your staging site and upload them to you live site. This is risky business as you could wipe out data and even crash your site.

    Fortunately, there are other options! You can create a staging site with your host or by using a plugin. Coming back to BlogVault, it also offers a staging feature that makes the job much easier.

    Staging with BlogVault

    If you’ve already installed BlogVault, staging is a free feature compatible with any web host. On the BlogVault dashboard, you’ll see an option to ‘Add staging site’. It takes a few minutes, and you’ll be notified when it’s done.

    Creating a WordPress  staging site with the BlogVault Plugin
    (Image source – BlogVault)

    The plugin handles migrating your site to a dev environment, so you don’t have to bother about the file transfer and the database export and import. The staging site is password protected and blocked off from search engines so migration does not affect your SEO.

    With everything taken care of, you simply need to start using your staging site. Once you’re happy with the changes you’ve made, you can simply merge the changes to your live site. There’s no need to upload files or replicate changes.

    BlogVault WordPress merge tool

    You can also do a selective merge. BlogVault gives you a comparison of your live site and your staging site. You can then select which changes you want to merge to your live site. In the example below, I made changes to my plugins, themes, uploads, and a few other files. But I wanted only the changes made to plugins. By clicking on the ‘+’ sign, I could see the list of plugins I added or modified. I selected the ones I wanted and merged in just two clicks.

    BlogVault WordPress file comparison tool

    The best part is that you can create as many staging sites as you want. So that’s BlogVault. It’s straightforward and easy to use. It won’t break your site when you merge the changes so your site is in safe hands.

    Next, let’s take a look at staging options with web hosting providers.

    Staging with WP Engine

    WP Engine gives you options to create three kinds of environments – production, staging, and development.

    To carry out the staging process, you first need to add your site to the user portal. Next, log into the user portal and access Sites > Name of your site > Add staging. Your staging site will be created using the backup point of the existing environment.

    WP Engine WordPress staging

    In case you don’t see the staging option, you need to convert a single environment site into a multi-environment site.

    Once you’ve made the changes you want, you can copy the changes from staging to your production environment.

    WP Engine also allows you to copy changes from a staging environment of one site to another website. This means you can roll out updates or changes to all your sites without repeating the staging process for each site. However, this can get a bit complicated and isn’t recommended for those new to WordPress.

    Staging with Kinsta

    Creating a staging site with Kinsta is quite easy. Access your Kinsta dashboard and select your site. Here, on the top right, you have the option to create a staging environment for your website. It takes around 10-15 minutes to create a staging site.

    Kinsta WordPress staging

    Once you refresh the page, you will see your staging details. You can make the changes you like to your staging site and then push them to your production environment. To do this, under the ‘Staging Environment’ tab, there’s a ‘Push Staging to Live’ button. Simply click on it and all the changes will be made visible on your live site.

    There’s no selective merge option here. So if you’re pushing changes to live, be aware that all changes will be merged.

    Final Thoughts

    That’s a wrap on backups and staging for your WordPress site. To summarize, hosts may offer free versions of backup and staging, but you’re limited in terms of features and functionality. As your site grows bigger, you would need to upgrade to premium plans which are quite expensive. However, when you opt for a plugin like BlogVault, it’s reasonably priced, gives you access to premium features, and above all, it works seamlessly.

    Whichever be your choice, make sure you can rely on your backup to work and be restored easily. Finally, always carry out changes in a staging environment before updating your live site. This will keep you out of trouble and ensure your site is always up and running.

Ubercart to WooCommerce migration notes

I’ve now had several Ubercart to WooCommerce migration projects so it’s time to start documenting the process. As with all my documentation, I’ll start off this post as rough notes and improve it over time. If this topic interests you, be sure to check back every so often to see the updates. Please keep in mind that I’m writing this from the standpoint of Drupal to WordPress migrations. In other words, the main objective for the projects were to migrate a Drupal site to WordPress but there was also an Ubercart to WooCommerce component.

Key differences between Ubercart and WooCommerce

There’s one key difference between Ubercart and WooCommerce from an architectural point-of-view. Ubercart stores products as nodes and orders are stored in a separate table uc_orders. Orders have an order_id and there’s no direct relationship in uc_orders to a product’s node ID. Instead, the uc_order_products table stores the relationship between orders and products purchased with the order.

WooCommerce stores both products and order transactions as posts in the wp_posts table. The post ID is used as the order’s transaction ID. Products purchased with the order are stored in wp_woocommerce_order_items, with additional product metadata being strored in wp_woocommerce_order_itemmeta.

Obviously there are many more differences but this information architecture is the key thing to keep in mind when migrating the data.

Ubercart to WooCommerce table mapping

Database tables

Here are the main database tables that you’ll need to migrate Ubercart content to WooCommerce.

Ubercart

Table: Drupal Ubercart tables
Table Description
node Products are stored in the Drupal node table.
uc_orders Stores the individual Ubercart transactions.
uc_order_products Stores the products purchased during the transaction.
uc_order_line_items Line items for an order. This includes tax and shipping fees applied to an order.
uc_order_comments Customer or administrator notes associated with each order.
uc_order_log Comments about the order status by the shop administrator.
uc_zone Country zone codes for customer billing and delivery.
uc_countries Countries and zone codes in ISO 3166-1 Alpha-2 and Alpha-3 code format for customer billing and delivery.

WooCommerce

Table: WordPress WooCommerce tables
Table Description
wp_posts Stores products and transactions.
wp_postmeta Transaction meta data.
wp_woocommerce_order_items Stores the line items for a transaction in the wp_posts table.
wp_woocommerce_order_itemmeta Stores the meta data for line items, such as quantity, price and tax information.
wp_comments Order notes.

Order transactions and products are saved as WordPress posts. One or many order item meta entries can be linked to an order.

WooCommerce shop orders

A WooCommerce transaction is saved as a shop order in the wp_posts table.

  • post_status = wc_completed
  • post_type = shop_order

WooCommerce subscriptions

WooCommerce subscriptions are saved in wp_posts. A subscription is made up of two entries:

  1. The transaction order for a subscription.
  2. The subscription itself.

Subscription transaction order

  • post_status = wc_completed
  • post_parent = 0
  • post_type = shop_order

The subscription

  • post_status = wc_active
  • post_parent = [post ID to the transaction shop order]
  • post_type = shop_subscription

Products

Purchased products are saved as line items in the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables.

A WooCommerce product can be of any post type. The product post is linked to a shop order transaction by setting the product’s post ID in the wp_woocommerce_order_itemmeta table:

  • meta_key = _product_id
  • meta_value = [post ID to the product]

Notes

My apologies if you’ve come here looking for more complete documentation. I’ve been planning to write this post for more than two years but have been putting it off due to my work schedule. I figure the best way to finally get it done is to just make a start and update it as time allows.

The most-viewed articles here started off as notes for my own use and evolved over time. This one is no different. Hopefully it will still be of use to some people in its draft state.

In the meantime, you might want to take a look at these other articles and plugins.

How to fix a ‘Object of class WP_Error could not be converted to string’ error in WordPress

If you see a blank page while trying to log in to your WordPress site, check your web server’s error logs. You may get the following error:

stderr: PHP Catchable fatal error:  Object of class WP_Error
could not be converted to string in
/var/[PATH TO YOUR DOCUMENT ROOT]/wp-includes/default-constants.php on line 139

Note that the line number may be different depending on your version of WordPress but the code generating the error is as follows:

function wp_plugin_directory_constants() {
    if ( !defined('WP_CONTENT_URL') )
        define( 'WP_CONTENT_URL', get_option('siteurl') . '/wp-content');

Do not be tempted to debug by editing the code as it’s not the source of the error. Your problem will very likely be that the siteurl option value in your WordPress database does not contain a valid entry. In this case, the error message is telling you that get_option() receives a WP_Error object rather than a string that it’s expecting.

To fix this:

  1. First check the siteurl option to verify that the value is indeed incorrect. Run the following SQL:
    SELECT * FROM `wp_options` WHERE option_name = 'siteurl';

    You will likely find a serialized array containing a WP_Error object.

  2. Correct the option value by setting it with your domain’s URL:
    UPDATE wp_options SET option_value = '[YOUR URL]' WHERE option_name = 'siteurl';

I’m not sure what overwrites the siteurl option value. Most likely there is a misbehaving plugin installed or malware has infected your installation. Be sure to run a scan on your server.

How to fix https version of a site redirecting to the wrong domain

Some hosting providers restrict customers to a single SSL/TLS certificate per socket. (In simple terms, a socket is the combination of IP address and port number.) Since Apache listens to port 80 for non-SSL connections and port 443 for SSL connections on the same IP address, customers usually need a separate IP address for each certificate.

At the same time, you can configure Apache for multiple domains to share a single IP address using virtual hosts. Each virtual host gets its own port and Apache listens to this port, redirecting connections to the appropriate domain.

The combination of the above behaviours can sometimes cause complications when you install a single SSL Certificate on a shared IP address. Secure connections to port 443 of an IP address will be directed to the virtual host and domain assigned to that port. Thus, if you try to make a secure connection to a domain on a shared IP address, Apache will create a socket to the actual domain listening to port 443. Depending on your configuration, this domain may be a default virtual host or one that is explicitly set to listen to port 443.

The possible solutions depend on the types of configurations supported by your hosting provider. These include:

  1. Moving each domain with SSL certificates to its own IP address.
  2. Use Server Name Indication (SNI) to define separate SSL virtual hosts.
  3. Creating a default virtual host in your SSL file that does nothing but redirect to non-SSL connection.
  4. Installing a self-signed certificate on each domain name on that IP address.
  5. Making a different SSL host the primary certificate for the IP address.

Resources

How to fix Error Code 2013 Lost connection to MySQL server

If you spend time running lots of MySQL queries, you might come across the Error Code: 2013. Lost connection to MySQL server during query. This article offers some suggestions on how to avoid or fix the problem.

Why this happens

This error appears when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.

Most of my work involves content migrations. These projects usually involve running complex MySQL queries that take a long time to complete. I’ve found the WordPress wp_postmeta table especially troublesome because a site with tens of thousands of posts can easily have several hundred thousand postmeta entries. Joins of large datasets from these types of tables can be especially intensive.

Avoid the problem by refining your queries

In many cases, you can avoid the problem entirely by refining your SQL queries. For example, instead of joining all the contents of two very large tables, try filtering out the records you don’t need. Where possible, try reducing the number of joins in a single query. This should have the added benefit of making your query easier to read. For my purposes, I’ve found that denormalizing content into working tables can improve the read performance. This avoids time-outs.

Re-writing the queries isn’t always option so you can try the following server-side and client-side workarounds.

Server-side solution

If you’re an administrator for your MySQL server, try changing some values. The MySQL documentation suggests increasing the net_read_timeout or connect_timeout values on the server.

Client-side solution

You can increase your MySQL client’s timeout values if you don’t have administrator access to the MySQL server.

MySQL Workbench

You can edit the SQL Editor preferences in MySQL Workbench:

  1. In the application menu, select Edit > Preferences > SQL Editor.
  2. Look for the MySQL Session section and increase the DBMS connection read time out value.
  3. Save the settings, quite MySQL Workbench and reopen the connection.

Navicat

How to edit Navicat preferences:

  1. Control-click on a connection item and select Connection Properties > Edit Connection.
  2. Select the Advanced tab and increase the Socket Timeout value.

Command line

On the command line, use the connect_timeout variable.

Python script

If you’re running a query from a Python script, use the connection argument:
con.query('SET GLOBAL connect_timeout=6000')

Drupal to WordPress migration consulting

Any Drupal version · All content · Custom content types · SEO · Plugins

Migrating a site from Drupal to WordPress and need a specialist? Please contact me for a quotation. Whether you’re a media agency who needs a database expert or a site owner looking for advice, I’ll save you time and ensure accurate content exports.

Get a quote

Post-migration troubleshooting: Gateway timeout when enabling plugins

Here’s one that caught me out on a recent Drupal to WordPress migration. As is common with my projects, there were three parties involved: the client, an external development team and myself. The WordPress site was first built on the development team’s server, after which it was migrated to my local environment. When everything was ready for beta testing, we moved the site over to the client’s staging server on a newly activated account over at Kinsta. Eventually, we’d move it over to a live server, also hosted on Kinsta.

Diagram of Drupal to WordPress migration workflow for this project

After some initial tests on staging, I found that deactivating and reactivating plugins would cause the site to hang and show a ‘504 Gateway Time-out’ error. This happened when re-enabling some but not all plugins.

I initially suspected some misconfiguration at the hosting end because there were some hitches with the newly create account. At the outset, the account’s server had an issue which Kinsta support needed to fix. For convenience, we’d also made use of Kinsta’s free site migration service. This is where they’ll migrate an existing WordPress site into their environment. Though it would have been easy enough for me to do, we thought to give it a try. In hindsight, this was a bit of a mistake. The site migration service itself was fine but it did end up causing some confusion. First, a miscommunication in the migration request caused them to create a temporary domain we didn’t want. They helpfully solved this by giving us a second temporary domain. However, they’d also upgraded everything to PHP 7 in the process. All of these issues were possible suspects for the time-out error but turned out to be red-herrings.

It took some time to pinpoint the cause behind the Gateway Time-out error. I do have to say that Kinsta support were very responsive throughout the troubleshooting process. They eventually put a senior engineer on the case who found the problem. It turned out the problem wasn’t to do with Kinsta at all. There was a leftover setting from the original development team’s server. It was a valid format so didn’t cause an issue on either my local server or my staging server. However, it apparently can cause issues with plugins and did on the Kinsta environment.

What was the setting? The WordPress upload path directory was set to the development team’s server path e.g. /home/dev/public_html/sitename. Throughout the migration, I’d been doing a database search-and-replace looking for their development domain. Somehow, as the site moved from different servers, that server path string remained in the database, only to cause a problem when the site landed in the destination server on Kinsta.

I’m not sure if there would have been any way to have caught this problem earlier. It’s one of those obscure errors that are easy to overlook and take time to resolve. There’s also no practical way to do a database search-and-replace for every imaginable string. I’ll have to rack this one up to experience.

How to change the WordPress table prefix prior to a migration

When working on a Drupal to WordPress migration project, I like to migrate into a set of intermediary WordPress tables that live in the Drupal database. These are working tables where I can run various scripts to process and clean up the content before exporting to a working WordPress installation. It’s not necessary to do this but I find it convenient to run scripts on the same database rather than deal with two separate database connections.

Note that some people suggest renaming the table prefixes to improve security. My use of the table prefixes is simply to create temporary containers for the migration. While non-standard prefixes might help prevent ‘script kiddie’ attacks, I find it isn’t worth the disadvantages that come with this sort of security through obscurity (or more precisely, security through minority) approach. Here are two articles give a deeper explanation of topic:

SQL queries to change the WordPress table prefixes

You can start with a freshly installed WordPress database. Dumping this and importing to your Drupal migration database will give you all the tables with the correct WordPress schema. I use the acc_ prefix but you can use whatever you want.

Rename the tables with these queries:

RENAME table `wp_commentmeta` TO `acc_commentmeta`;
RENAME table `wp_comments` TO `acc_comments`;
RENAME table `wp_links` TO `acc_links`;
RENAME table `wp_options` TO `acc_options`;
RENAME table `wp_postmeta` TO `acc_postmeta`;
RENAME table `wp_posts` TO `acc_posts`;
RENAME table `wp_terms` TO `acc_terms`;
RENAME table `wp_termmeta` TO `acc_termmeta`;
RENAME table `wp_term_relationships` TO `acc_term_relationships`;
RENAME table `wp_term_taxonomy` TO `acc_term_taxonomy`;
RENAME table `wp_usermeta` TO `acc_usermeta`;
RENAME table `wp_users` TO `acc_users`;

Testing the migration results

If you need to point a WordPress installation to these tables for testing, you’ll need to do two things:

  1. Update the $table_prefix setting in the wp-options.php file
  2. Update the options and usermeta tables

Updating the $table_prefix setting in the wp-options.php file is straightforward. Open the file and edit the line:

$table_prefix  = 'acc_';

In WordPress, prefixes are saved as entries in the options and usermeta table. Check for entries containing the prefix:

SELECT * FROM `acc_options` WHERE `option_name` LIKE '%wp_%';
SELECT * FROM `acc_usermeta` WHERE `meta_key` LIKE '%wp_%';

When you have all the entries, update them with the new prefix. The query will probably look something like this:

UPDATE `acc_options` SET `option_name` = 'acc_user_roles' WHERE `option_name` = 'wp_user_roles';
UPDATE `acc_usermeta` SET `meta_key` = 'acc_capabilities' WHERE `meta_key` = 'wp_capabilities';
UPDATE `acc_usermeta` SET `meta_key` = 'acc_user_level' WHERE `meta_key` = 'wp_user_level';
UPDATE `acc_usermeta` SET `meta_key` = 'acc_user-settings-time' WHERE `meta_key` = 'wp_user-settings-time';
UPDATE `acc_usermeta` SET `meta_key` = 'acc_user-settings' WHERE `meta_key` = 'wp_user-settings';
UPDATE `acc_usermeta` SET `meta_key` = 'acc_dashboard_quick_press_last_post_id' WHERE `meta_key` = 'wp_dashboard_quick_press_last_post_id';

A word of warning: it’s easy to forget to change the prefixes back to match the final WordPress installation. If you do, the WordPress user accounts will have problems, such as the Dashboard controls not being visible after logging in. Because of this, I tend to have a separate testing installation that gets an import of the working tables.

Post-migration troubleshooting: WordPress redirects to old site after updating database

If you’ve ever migrated a WordPress site, either to another URL or for a Drupal to WordPress migration project, you’ll know that WordPress stores the domain name in its database. This means you’ll have to jump through some hoops when moving WordPress to another environment. A critical step is to update the database to reflect the new domain. My favourite tool for this used to be the database search and replace script from interconnect/it. The script is PHP-based so runs on all environments that host WordPress. I now prefer WP-CLI’s wp search-replace command when on my own development environment, or for client hosting that supports it. Nevertheless, interconnect/it’s tool is still my fall-back option for clients of my Drupal to WordPress migration service since many use hosts that don’t offer command-line access.

In nearly all cases, updating the siteurl and home fields in the wp_options database table achieves the bare minimum to get the site working after migration. Running a search-and-replace across the WordPress database (in particular, the wp_posts table) will resolve broken links containing absolute URLs.

wp-admin still redirects to the old site after updating wp_options?

Once-in-a-while, I’ll encounter a migration project where wp-admin still redirects to the old site even after running through the obvious steps of:

  1. updating the database;
  2. clearing the browser cache;
  3. clearing the server cache.

It happens very rarely so I have yet to discover the cause. I suspect it’s something to do with sites that had a caching plugin installed, such as W3 Total Cache.

If you ever find yourself in this situation, the best workaround is to add the following two constants in wp-config.php:

define('WP_HOME', 'http://' . $_SERVER['SERVER_NAME']);
define('WP_SITEURL', WP_HOME . '/');

This isn’t a nice long-term solution but it should at least enable you to log in for some basic site administration. Once you’re able to log in to the WordPress Dashboard, disable any caching plugins after first clearing their cache.

How to write a Drupal to WordPress migration mapping document

Performing a Drupal to WordPress migration can be very complex, especially if you have many content types. You’ll make the process easier if you create a migration mapping document beforehand. A common migration mapping is to convert Drupal pages to the WordPress page type and Drupal stories to the WordPress post type. However, many Drupal sites also have custom content types so the mapping won’t always be obvious. Will they be converted to WordPress pages or posts? Do they have custom fields? How about views and panels? Perhaps you may need to develop a new WordPress content type to support them.

Your Drupal to WordPress migration mapping document will give everyone involved a clear idea about how the Drupal content will be migrated to its equivalent in WordPress. If you’re running the migration yourself on a simple blog or company site, there might not be much need to spend the extra effort but sometimes running through the process uncovers aspects of the site that you may have overlooked. I’d say this is vital for migrations where you’ve hired someone else or if you have a content-heavy or news-based site. Not only will the document make it easier for someone to quote for the job, you’ll also have a specification for reference when checking the results post-migration.

Creating the content mapping

Creating the mapping needn’t be complex. The easiest way is draw up a table with at least two columns. On the left column, list down all your Drupal content types. Next write down equivalent WordPress content type on the right column for each row of Drupal content types. You might find it helpful to have a third column for writing notes, such as whether or not you need do develop a new custom WordPress content type. If your Drupal content type has custom fields, simply add rows below each type listing the fields.

Sample content mapping table
Drupal WordPress Notes
Drupal WordPress Notes
Drupal WordPress Notes
  Field 1   Field 1 Notes
  Field 2   Field 2 Notes
  Field 3   Field 3 Notes
Drupal WordPress Notes
Drupal WordPress Notes
Drupal WordPress Notes

Developers responsible for the migration can also add additional sections specifying back-end table and field names where the relevant content can be found.

Creating the functionality mapping

Follow a similar procedure to create the functionality mapping. Instead of content type names, list down Drupal modules and equivalent WordPress plugins.

Preparing for your migration project

The migration mapping document will have helped prepare you for your project and provide you with a specification for the migration. You might also find the following articles useful:

A little plug to keep the lights running. If you think all of this work is too much trouble, please consider hiring me for your Drupal to WordPress migration project.

Drupal to WordPress migration service

Any Drupal version · All content · Custom content types · SEO · Plugins

If you’re not sure how to make the appropriate changes or would simply like someone else to do the work, please request a quotation for my Drupal to WordPress migration service.

Get a quote