How to set up a MySQL database server on Ubuntu for Drupal to WordPress migrations

Content Management System (CMS) migration projects involve moving data between databases with different schemas. Table names, field types and constraints often don’t match up, leading to a number of frustrating errors. This is especially the case with highly complex Drupal to WordPress migrations that use custom scripts to extract, transform and load the data. These projects can seem like you’re constantly hitting roadblocks throughout. I’ve found from experience that running the migration in an appropriate development environment can reduce a great deal of effort. In this guide, I will describe how to set up a MySQL database server on Ubuntu for Drupal to WordPress migrations.

Table of contents

Why MySQL and Ubuntu?

The first thing to address is why MySQL and Ubuntu? How about MariaDB? How about Arch Linux, Mac OS or Windows? Yes! Any platform that runs Drupal and WordPress will work for your migration environment so you can use whatever you prefer. Personally, I have a fondness for OpenBSD but it’s not a practical platform for a CMS migration. OpenBSD’s niche user-base means you’ll spend much longer installing necessary tools and troubleshooting errors.

There are all sorts of tutorials covering MySQL on Ubuntu. This means you’re more likely to quickly find a solution from a web search when you hit a problem. Furthermore, if you need a software utility or program to help you get the job done, it will probably be available through apt, dpkg, snap or tasksel. Use whatever you like but for now, MySQL on Ubuntu is my recommended platform for Drupal to WordPress migrations. I expect this will be the case for some time to come. These projects are complex and time-consuming enough without making the job more difficult.

Installing MySQL on Ubuntu for CMS migrations

There are many detailed tutorials for installing MySQL on Ubuntu. DigitalOcean’s How To Install MySQL on Ubuntu 20.04 is a good one and writing another won’t add much value. My guide will therefore only give a brief overview of the MySQL server installation steps. Instead, I will focus on the configuration areas specifically related avoiding problems on a CMS migration project.

You may wonder why the migration environment should be much different from a live server. Migration projects require you to do things that aren’t supported by the CMS platform. You’re therefore likely to encounter weird errors that aren’t normally found when running standard Drupal or WordPress.

Set up your Ubuntu LAMP migration platform

The main source of unusual errors is almost certainly because you’re migrating on a setup suited to a live website. Live server configurations are more restrictive than you need for a migration project. You can therefore save yourself a huge headache by rolling your own local migration environment. It might take a little longer to get started but you’ll save time by avoiding lots of unnecessary troubleshooting.

I must highlight that this will be a local migration environment and should not be accessible from the public internet. The normal security considerations with running a live content management system don’t apply when you’re working locally. By all means follow basic security measures mentioned the various tutorials for setting up Ubuntu and MySQL. Nevertheless, a highly secure setup is counterproductive for these projects and you can avoid trouble by being a little more permissive.

Go ahead and install Ubuntu Desktop. Since this will be a development environment, you’ll want the Desktop environment rather than the more lightweight server version. Of course, you’ll still need to install a web and database server. Follow these instructions for installing LAMP stack but skip the step of installing MariaDB. As mentioned above, we’ll be using MySQL.

WARNING: Installing MariaDB over MySQL or vice versa on Ubuntu 20.04 may lead to all sorts of problems starting up the database server with errors like the following:

Failed to start mysqld.service: Unit mysqld.service not found.

The last time I did this, none of solutions mentioned online for purging the installation worked. I spent most of a day trying to fix the problem. In the end, I realised it was quicker to start again and rebuild the machine from scratch. This is a big reason why I decided to stick with MySQL as standard for my projects.

Install MySQL

You can read a more detailed tutorial on installing MySQL on Ubuntu but here’s an overview.

  1. Update the package index on your server: sudo apt update
  2. Install MySQL server: sudo apt install mysql-server
  3. Secure MySQL: sudo /usr/bin/mysql_secure_installation

The mysql_secure_installation script doesn’t cause problems for migrations so it’s worth running.

Create an admin user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'userpassword';
GRANT ALL PRIVILEGES ON *.* to 'user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit;

Now create a user and database for your migration project:

CREATE USER 'projectuser'@'localhost' IDENTIFIED BY 'password';

CREATE DATABASE project_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
GRANT ALL ON project_db.* TO 'projectuser'@'localhost';

You can give this user more restrictive permissions but GRANT ALL avoids errors when running scripts and SQL queries on the database. Only the migration team should have access and you won’t need it after the migration so why not make your life easier?

Setting the MySQL server SQL mode

It’s possible that you won’t be familiar with SQL modes unless you’ve done some database administration work. For our purposes, SQL modes do two things:

  • change the types of queries you can run on your MySQL server;
  • change the validation checks when altering the data.

I’ve found that specific SQL modes need to be set for Drupal to WordPress projects. You may find that all sorts of strange errors appear if the correct modes aren’t set. The following sections show you two ways to set your MySQL server’s SQL mode.

Option 1: Setting the global sql_mode in the database

  1. Login in to database as an admin user.
  2. View the current sql-modes using SELECT @@GLOBAL.sql_mode; and make a copy if necessary.
  3. Copy the current modes (add or delete modes as needed) and paste in next step.
  4. Add ALLOW_INVALID_DATES and removes both NO_ZERO_DATE, NO_ZERO_IN_DATE by setting the sql-modes with
    SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; (WARNING: check the modes correspond with your setup.)
  5. Restart server:
    sudo systemctl start mysql

Option 2: setting the sql_mode in the MySQL configuration file

Locating the MySQL configuration file

The my.cnf configuration file isn’t always found in the same place. It’s specific to the Linux distribution and server configuration but can normally found in one of the following locations:

/etc/my.cnf
/etc/mysql/my.cnf
echo/my.cnf
[datadir]/my.cnf
~/.my.cnf

If you can’t find your MySQL configuration file, you can try running locate my.cnf or mysqladmin --help. The latter will show something like the following in the output:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Also keep in mind that it’s possible to use !include directives to include other option files and !includedir to search specific directories for option files. Under Ubuntu, there may be a file /etc/mysql/my.cnf with !includedir directives to search /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/

Editing the MySQL configuration file

If the MySQL server finds more than one configuration file, it will load each one in turn. The values override each other and it can be difficult to know which takes priority. Furthermore, the –defaults-file parameter can also override all configurations. Keep things simple and have only one file and place it the directory that makes sense to you.

Before editing the my.cnf, first log in to MySQL with an administrator user and run the SELECT @@GLOBAL.sql_mode query to see the values used in your setup.

Setting the MySQL database server sql mode on Ubuntu for Drupal to WordPress migrations
Running the SELECT @@GLOBAL.sql_mode query on MySQL Workbench

Next, open the configuration file, look for the section [mysqld] and edit the line starting with:
sql_mode = ...

Add the line if it’s not there. Adjust the exact modes to match your project’s needs so take a look at the list of SQL modes to see which may apply. I’ve found the following works well:
sql_mode = "STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Finally, restart the MySQL server. On Ubuntu this will probably be with

sudo systemctl start mysql

If you are logged in to the MySQL server, you may also need to disconnect your client and reconnect for the changes to take effect for your session.

Potential errors

Here are some potential errors that you may come across during a CMS migration project. I usually find them when running a Drupal to WordPress migration on a freshly built development environment.

mysqldump access denied when trying to dump tablespaces

Migrations involve dumping and importing databases and this process is straightforward on a mature development environment. However you may receive an ‘Access denied’ error out of the blue when dumping 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

If you see this, perhaps you were working on an environment that was a little too ‘mature’ (in other words, obsolete!) and you have recently upgraded your installation. The updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change that produces this error.

Read my separate article, How to fix the mysqldump access denied process privilege error, for more information and instructions on how you can try solving this.

ERROR 1067 (42000) Invalid default value

Drupal nodes store the date as a Unix timestamp in an int (e.g. 1623427200) field whereas WordPress stores dates as datetime (e.g. 2021-06-11 16:00:00). There may be a conversion error in your migration script or the source date could simply be zero for some reason. Normally your MySQL server mode will be set to NO_ZERO_DATE, NO_ZERO_IN_DATE so trying to insert a zero date will give you the error:

ERROR 1067 (42000) Invalid default value

You can fix this by replacing NO_ZERO_DATE, NO_ZERO_IN_DATE with set to ALLOW_INVALID_DATES in your global SQL mode.

Expression #1 of SELECT list is not in GROUP BY clause

You run an SQL query and get the rather cryptic error:

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'database.table.pid' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What’s wrong? MySQL has a only_full_group_by mode which, when enabled, strictly applies ANSI SQL rules when using GROUP BY. Fix this by reworking your script or removing the ONLY_FULL_GROUP_BY SQL mode.

Error Code: 2013. Lost connection to MySQL server

This often happens when it an SQL query takes too long to return data. The connection between your MySQL client and database server times out so the connection gets dropped. For ideas on how to try solving this, read my separate article, How to fix Error Code 2013 Lost connection to MySQL server.

Conclusion

Setting up a MySQL database server for Drupal or WordPress is a familiar task for web developers and site administrators. However, CMS migrations have quirks that can cause obscure and baffling errors. In this guide I’ve shown you some little tricks that may save time and annoyance. While there’s no way to provide an exhaustive list of solutions to all the MySQL problems you’ll encounter, I hope to have pointed you in the right direction.

If you have a site migration project and would like to hire me, please ask for a quote for my consulting service.

CMS migration consulting

All content · Custom content types · SEO · Plugins

Migrating content from a site 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

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];

Note 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.

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.

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.

Importing a WordPress database: How to fix the Unknown collation: ‘utf8mb4_unicode_ci’ error

If you do a lot of exporting and importing to different database servers, you’ll be familiar with the frustration of encountering MySQL import errors. Every so often when importing a WordPress dump file into a client’s database, I will encounter an Unknown collation error like the following:

Unknown collation: 'utf8mb4_unicode_ci'

Sometimes it will come up as:

Unknown collation: 'utf8mb4_unicode_520_ci'

This is caused by a difference in encoding types between the source and destination databases. It usually happens when you export from a newer MySQL database (MySQL 5.5.3 and above) which uses utf8mb4, then attempt to import into an older version using utf8. If you are importing from a dump file generated from a MySQL 5.6 database, you may get the utf8mb4_unicode_520_ci message. The 520 refers to MySQL’s use of Unicode Collation Algorithm 5.2.0. Unknown collation errors may also happen if you are trying to import a MariaDB database into MySQL. I tend to get unknown collation errors with my Rackspace Cloud accounts after Rackspace started offering MariaDB as a database option.

Ideally one would upgrade the older destination database but this isn’t always a realistic option. There are a number of discussion threads on the WordPress forum about what to do. Fortunately, many web hosting accounts have a phpMyAdmin interface which provides an easy work-around for the problem.

Format-specific options during a phpMyAdmin database export

  1. Log in to your database server using phpMyAdmin
  2. Make sure you select your database and go to the “Export” tab
  3. Select the “Custom” radio button
  4. Go the section “Format-specific options” and in the setting for “Database system or older MySQL server to maximize output compatibility with:” select MYSQL40.
  5. Scroll to the bottom and click GO.

phpMyAdmin format specific options to fix the Unknown collation: 'utf8mb4_unicode_ci' error

Other possible solutions include:

Since many of my WordPress database migrations are under my migration service, I don’t always have control over the client’s platform. The phpMyAdmin export format method is often the simplest solution.

Side-effects of a character encoding downgrade

You might be wondering about the purpose of encoding types and if there will be any side-effects of downgrading. Character encoding allows support for a set of characters, such as the Western alphabet, Asian scripts and non-alphanumeric symbols. Older utf8 databases support a smaller set of characters whereas utf8mb4 includes emojis, musical notation and Chinese Han characters. If you’ve ever exported a website from one CMS to another and found random characters scattered throughout the copy, it’s because of an incompatible character encoding.

Solving the unknown collation error as described here could mean you’ll end up with unsupported characters after your site migration. However, as with many of my Drupal to WordPress migration clients, in all likelihood you’ll be migrating from an older utf8 Drupal database to a newer utf8mb4-supported WordPress database. In this case, your old content will not have characters that will cause a problem after an encoding downgrade.

Post-migration steps: what to do after a Drupal to WordPress migration project

From time-to-time I get clients who ask me to only export the content to a WordPress database, after which they’ll complete the remaining setup themselves. If this applies to your project, you can use these migration notes to help get your new WordPress site running properly.

Import the database dump file

Please see these notes for importing the WordPress dump file.

Administrator credentials and email address

I will have changed your content management system (CMS) administrator password and email address to help with debugging. It’s important that you change these to your own as soon as possible.

Drupal and WordPress user passwords are encrypted so I won’t be able to view them. However, for your peace of mind, I recommend that you ask all your users to reset their passwords after your new WordPress site is live.

Server credentials

Please remember to change any database, (S)FTP, SSH server and control panel credentials you may have given me.

Migrating to a live server

I perform most Drupal to WordPress migrations on a development server. For help on how to move WordPress to your live server, please see: WordPress Codex: Moving WordPress.

Common errors after moving to a live server

Please see below for some common errors you may experience after migrating your new WordPress site to a new server.

Incorrect domain in URLs

WordPress stores domains in the database. If you performed the migration on a local or development server, there’s a good chance that the links will be incorrect after migrating to your live server. Use the Interconnect IT utility to run a search and replace on your database. This will also correct changed database prefixes.

More information can be found on the interconnect/it Search Replace DB page.

“You do not have sufficient permissions to access this page”

If you receive this error after logging in to your new WordPress installation, it’s possible that the database prefix on your new WordPress site is not set correctly. This may happen if you move your WordPress installation to a host that uses a different database prefix.

Try running one of the queries below. Replace wp_new_usermeta, oldprefix_ and newprefix_ as appropriate.

Option 1:

UPDATE wp_new_usermeta SET meta_key = REPLACE(meta_key,’oldprefix_’,’newprefix_’);

UPDATE wp_new_options SET option_name = REPLACE(option_name,’oldprefix_’,’newprefix_’);

Option 2:

update wp_new_usermeta set meta_key = ‘newprefix_usermeta’ where meta_key = ‘wp_capabilities’;

update wp_new_usermeta set meta_key = ‘newprefix_user_level’ where meta_key = ‘wp_user_level’;

update wp_new_usermeta set meta_key = ‘newprefix_autosave_draft_ids’ where meta_key = ‘wp_autosave_draft_ids’;

update wp_new_options set option_name = ‘newprefix_user_roles’ where option_name = ‘wp_user_roles’;

Please note that these queries may not work for you. Success depends on your specific setup.

For more information, please see the following pages:

Further help

I’ll be very happy to provide support you if have difficulties after migration. For a quotation, please contact me. I also offer customised hosting and maintenance packages. Please ask for details.

Handling Drupal terms during a Drupal to WordPress migration

When migrating Drupal terms into WordPress, it’s important to understand exactly what terms are and how the two systems handle categorising information.

A primer on Drupal taxonomies

One of Drupal’s most powerful features is its ability to organise content with taxonomies. Unfortunately, the taxonomy system is also notorious as one of the trickiest things about Drupal for beginners to understand. You can find a more detailed explanation here but essentially, a taxonomy is the practice and science of classifying things. In content management terms, you would mostly use taxonomies to organise and categorise articles or posts.

Taxonomies in Drupal uses the concept of vocabularies and terms. Terms are just a list of words that describe a particular type of content. They’re grouped together into vocabularies, which can be thought of as ‘containers’ for a set of terms. Vocabularies may be assigned to any content type. Drupal allows you to arrange the terms within a vocabulary using a parent-and-child hierarchical structure or they can be a flat list, with each term being on the same level as the others.

You can have many vocabularies in Drupal, each containing any number of terms. Vocabulary names must be unique and you cannot have duplicate term names within a vocabulary. It’s possible, however, to have the same term name appear in different vocabularies. Fig. 1 shows an example Drupal taxonomy with three vocabularies, Music, Movies and Books. The Movies and Books vocabularies both have the term Sci-Fi.

An example of Drupal vocabularies and terms
Fig 1: Drupal vocabularies and terms

For more information about the Drupal taxonomy system, please see Organizing content with taxonomies.

WordPress categories and tags

WordPress’ system for organising content is simpler. You have the option of categories–which can be hierarchical–and tags which are flat, or non-hierarchical. In general, categories in WordPress are used as a way of broadly organising posts and tags are used for more detailed descriptions.

Unlike Drupal, where you can have many containers in the form of vocabularies, a standard WordPress installation offers one container for categories and one for tags. Also as standard, categories and tags can only be assigned to the WordPress post content type. A WordPress developer can extend this by creating custom content types with their own categories and tags.

Fig. 2 shows show you’d organise the Music, Movies and Books categorisation in WordPress.

WordPress categories and tags
Fig. 2: WordPress categories and tags

Migrating Drupal terms as WordPress categories and tags

When running a Drupal to WordPress migration, we need to map Drupal’s more complex multi-vocabulary taxonomy system into the simpler WordPress model of categories and tags. How we do this depends on how you want to organise your new WordPress site. For example, we can:

  • convert Drupal vocabulary names into WordPress categories and Drupal term names into WordPress tags;
  • convert Drupal terms into WordPress categories and sub-categories;
  • vocabularies and their associated terms.

It’s all up to you and we figure this out during the requirements gathering stage of the project. For many sites, converting Drupal vocabulary names into WordPress categories and Drupal term names into WordPress tags, as shown in Fig. 3, seems to be the most sensible option. The important thing to know is that the migration may require us to ‘collapse’ or combine your taxonomies.

Merging Drupal and WordPress taxonomies
Fig 3: Merging Drupal taxonomies into WordPress

Since WordPress doesn’t support duplicate category or tag names, another thing to consider is how to handle any duplicate Drupal terms. Normally, the easiest solution is to append a unique number so that you can filter them out post-migration. We can do some clever merging and re-assigning of terms to posts but frankly, it’s probably not worth incurring the extra fees. Unless you have a great number of duplicates, you can probably do the job yourself quite easily via the WordPress Dashboard controls.

Organising your categories and tags in WordPress

Now that we know what’s involved in converting Drupal’s taxonomy over to WordPress, the next obvious question would be, “What’s the best way to structure categories and tags in WordPress?” While I cannot prescribe exactly how you should organise your site, I can point you to this excellent article so you can decide for yourself: Categories vs Tags – SEO Best Practices for Sorting your Content. Generally you should only have a few categories, maybe five or ten in total. Any more and they can become unwieldy and difficult to manage. These categories will reflect the main themes of your site. Tags can then further describe the details of each post and link specific topics together. You can have any number of tags.

The chances are that you probably want to avoid any drastic changes to the site structure when migrating from Drupal to WordPress. A simple mapping of vocabularies to categories and terms to tags is usually the closest equivalent in WordPress.