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

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.

Migrating Drupal events to WordPress Events Calendar (Tribe Events)

The The Events Calendar, sometimes known as Tribe Events, is a popular plugin for managing event content in WordPress. It’s so popular that it’s often the first choice when clients ask me to migrate Drupal event content into WordPress.

Here is some sample SQL queries if you need to do something similar.

Sample SQL queries

These are example queries only. Be sure to edit them for your own configuration.

Event date and time

The event date and time is in ISO 8601 without the T character. See how WordPress prefers to format times and dates.


INSERT INTO wp_postmeta (
    post_id,
    meta_key,
    meta_value
)
SELECT
	nid,
    '_EventStartDate',
	REPLACE(field_eventdate_value, 'T', ' ')
FROM drupal_content_field_eventdate;


INSERT INTO wp_postmeta (
     post_id,
     meta_key,
     meta_value
)
SELECT
	post_id,
	'_EventEndDate',
    meta_value
FROM wp_postmeta WHERE meta_key="_EventStartDate";


INSERT INTO wp_postmeta (
     post_id,
     meta_key,
     meta_value
)
SELECT
	post_id,
	'_EventTimezone',
    'UTC+0'
FROM wp_postmeta WHERE meta_key="_EventStartDate";

INSERT INTO wp_postmeta (
     post_id,
     meta_key,
     meta_value
)
SELECT
	post_id,
	'_EventTimezoneAbbr',
    'UTC+0'
FROM wp_postmeta WHERE meta_key="_EventStartDate";


INSERT INTO wp_postmeta (
     post_id,
     meta_key,
     meta_value
)
SELECT
	post_id,
	'_EventOrigin',
    'events-calendar'
FROM wp_postmeta WHERE meta_key="_EventStartDate";

Event details


INSERT INTO wp_postmeta (
    post_id,
    meta_key,
    meta_value
)
SELECT
    nid,
    '_EventURL',
    field_eventorgs_url
FROM drupal_content_field_eventorgs;


INSERT INTO wp_postmeta (
    post_id,
    meta_key,
    meta_value
)
SELECT
    nid,
    '_OrganizerOrganizer',
    field_eventorgs_title
FROM drupal_content_field_eventorgs;

Custom fields

This query will save the filepath of associated files, such as a logo, into a custom field.


INSERT INTO wp_postmeta (
     post_id,
     meta_key,
     meta_value
)
SELECT
    nid,
    'field_eventlogo',
    REPLACE(f.filepath, 'sites/default/files/', '/wp-content/uploads/legacy/')
FROM drupal_content_field_orglogos c
INNER JOIN drupal_files f
ON c.field_orglogos_fid = f.fid;

The Events Calendar meta key cheatsheet

For all the meta keys, take a look at The Events Calendar’s WordPress Post Meta Data Cheatsheet.

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.

Handling the Drupal file directory when migrating to WordPress

Attaching media the WordPress Media Library are one of those things that many clients don’t realise can add lots of time to a Drupal to WordPress migration project. The reason is a little detailed and involves some knowledge of WordPress’ inner-workings.

When you upload a file to the WordPress Media Library, WordPress does some magic behind the scenes and creates different bits of metadata. If you upload, say, an image, it creates thumbnail versions of the image based on the settings in Settings > Media Settings > Image sizes. It also adds a bunch of serialized data in WordPress’ attachment table (wp_attachment_metadata). If you get this information wrong, displaying attachments will fail. For example, Featured Images in posts will not display because the theme has no reference for which thumbnail to display when a post is delivered.

Folders symbolising media migration from Drupal to WordPress

Because of the necessary metadata that needs to be generated, attaching media to the Media Library is therefore not feasible through a database migration using purely SQL queries. If you’d like media attached, a separate custom script is needed to pick up all your files under the Drupal file directory and attach them using different mechanisms, such as XML-RPC, WP-CLI or a custom plugin. It adds to the cost so many of my clients see this as a ‘nice-to-have’ rather than a necessity.

As an alternative, my clients and I have come up with different ways to handle the Drupal files and media items when migrating to WordPress:

  1. Leave the Drupal file directory path intact on the WordPress installation server. This is the easiest option which most clients take because it’s cheaper. No changes are needed to the post and page content because the already embedded path will still be valid. However, we may have to make changes to the WordPress theme so that it knows the path to legacy Drupal file path. Future uploaded files will be fine but legacy posts will point to the Drupal file paths. This is not nice and a bit of a hack but it’s a cheap and quick solution. Media items will not appear in the WordPress Media Library.
  2. Move the Drupal file tree to the WordPress wp-content directory. We then need to do a search-and-replace to amend the link path within the post content. There’s a small risk of broken links if we don’t get the search pattern right but generally it’s a straightforward process. Media items will not appear in the WordPress Media Library.
  3. Use a plugin or custom WordPress code to attach files from an external URL. The client’s developer normally handles this work in-house so I can’t offer much feedback on this method. It seems to work reasonably well.
  4. Manually upload all the files to the WordPress Media Library. This is of course the most tedious and time-consuming option. Nevertheless, those with smaller sites or have gone this route. If you’re trying to save on budget and have lots of time on your hands, you might want to consider it.
Graphic courtesy of and copyright morguefile.com user Ladyheart

Migrating Bare Bones Yojimbo to WordPress

In a previous post, I wrote about exporting data from Bare Bones Software’s Yojimbo and using Tomboy as an alternative. My migration script scraped the content from Yojimbo Sidekick and wrote XML files in Tomboy Note format. Though there were some drawbacks, such as tags being unavailable in Yojimbo Sidekick, I thought Tomboy’s search feature would be adequate. A couple of weeks trialling Tomboy proved that it wasn’t going to be a Yojimbo killer. Tomboy can’t compete in terms of overall usability and though it’ll work on my Linux and OS X machines, note synchronisation takes some setup that didn’t warrant further time investment.

Once again I turned to WordPress as an easy solution. There’s a risk of seeing WordPress as my hammer for everything that looks like a nail but it has taxonomies, a reliable-enough search functionality and being web-based, works across all my devices. I’m very familiar with the platform and have already built up my own set of tools to export and migrate content. Why not use WordPress? Getting data out of Yojimbo was another issue. The quick and easy Yojimbo Sidekick route already proved inadequate so it was time to dig in and reverse engineer Yojimbo’s storage mechanism.

Analysing and exporting the Yojimbo database

‘Reverse engineering’ turned out to be too lofty a term for the task. It was obvious after quick look that Yojimbo uses an SQLite database to store information. Firing up DB4S to analyse the tables and bit of analysis revealed the tables, columns and relationships that are important for exporting our notes. The columns are a little oddly named but it didn’t take long to figure out the necessary fields for migrating to WordPress.

Table: ZITEM
Column Description
ZBLOB This looks like an ID
ZNAME The Yojimbo note title
Z_PK The ID to the Z_15TAGS relationship table
Table: ZTAG
Column Description
Z_PK The Tag ID
ZNAME The tag name
Table: ZBLOBLSTRINGREP
Column Description
ZBLOB ID
ZSTRING String for unencrypted item
Table: Z_15TAGS
Column Description
Z_15ITEMS1 Relationship ID
Z_25ITAGS Tag ID

Yojimbo SQLite tables

It became a simple matter of tweaking my Drupal to WordPress migration queries to extract from the Yojimbo database and create WordPress posts. Unlike with the Tomboy Notes route, it was possible to recreate the tags, which is what makes Yojimbo so useful. One drawback is that I haven’t figured out how to extract encrypted notes but I didn’t use Yojimbo to store important encrypted information so that wasn’t a priority.

WordPress as a Yojimbo alternative

Using WordPress as a Yojimbo alternative might not work for everyone but after several months use, I’ve found it to be an excellent cross-platform replacement. The installation and database runs on a NAS drive connected to my local network so is accessible to all my devices. Standard WordPress taxonomies, search and plugins makes content management simple once you’ve imported the Yojimbo content. In fact, by migrating away from Yojimo, I’ve ended up creating my own full-blown personal knowledge management system.

If you need to export your Yojimbo notes to a cross-platform alternative, give WordPress a try. You can grab my migration script from GitLab but please keep in mind that it was a quick hack to achieve a specific one-time objective. You may need to hack it to suit your own setup.

Migrating Delicious Bookmarks to WordPress

Delicious is one of those services that few people know about but loved (and probably hated in equal measure) by those who use it. Their offering is easy to grasp: Delicious is a social bookmarking service that lets you “Save, organize, and remember the links you find interesting or useful around the web.” I’m sure you’ve been in a situation where you’ve tried to remember the source for a particular factoid or needed to cite an article but lost track of the link. The obvious place to save links is in the browser bookmarks but this quickly becomes unwieldy if, for example, you’re doing research on lots of different topics. Unless you install bookmark syncing software, browser-based bookmarks are also cumbersome if you run different browsers and computers. I used Delicious to store thousands of links over the years, all of which where tagged and easy to call up when needed. They say in their about page, “Delicious remembers so you don’t have to. It’s easy to build up a collection of links, essentially creating your own personal search engine.” It was great at that and absolutely invaluable if, like me, you have a poor memory. Programmers, bloggers, researchers, anyone who needs to save a link for future recall needs something like Delicious. Founded in 2003, it’s also a veteran in an industry where anything that lasts more than a couple of years is considered successful.

Old book with bookmark for Bookmark to WordPress migration

Sadly, this glowing opening isn’t to recommend the service. You’d think its longevity would be a sign of a rock-solid, mature platform but no: Delicious’ loyal user-base has been rewarded with outages, lost data and broken features. That people persisted despite years of problems shows just how useful a service it provides. When the site went down again earlier this year (2016), I decided it was time to make a move. Unfortunately I couldn’t find an alternative trustworthy enough for several years-worth of carefully curated bookmarks. Free web services are notorious for disappearing into a black hole with all your data. As the saying goes, you get what you pay for.

After a long search, I began to realise there wasn’t anyone out there who could be trusted. This isn’t just Delicious and bookmark curation problem though. These past few years has shown that The Cloud hasn’t lived up to its promise. Data breaches, surveillance, data collection, outages, and catastrophic meltdowns; we can’t really trust a third-party service, especially if they don’t have a clear business model. I grudgingly conceded that cobbling together my own solution was the only option.

The rest of this post describes my solution, why I chose WordPress and how you can convert your WordPress site to host a personal bookmarking service. This will be valuable even for those who’ve never used Delicious but would like a way to curate links.

Exporting the bookmarks

Before doing anything else, the bookmarks need to be in form that’s easy to manipulate. Delicious had a feature to export all your links, tags and notes into a HTML file Netscape bookmark format. At the time of writing this has been ‘temporarily disabled due to server load’ (really, Delicious?). Fortunately I was in the habit of making frequent backups due to Delicious’ unreliability so my export file was only a few weeks old. I threw together a quick Python script called DeliciousPy to scrape the file and save the results into a local MySQL database. Feel free to grab the source over on GitLab.

It was a one-use tool so a little rough and undocumented. (Sorry, it wasn’t written with the intention of public release!) Anyone with basic Python knowledge should be able to easily figure out how it works. Essentially, I parse the Delicious export file using Thibauld Nion’s Netscape bookmark parser that is part of his Water On Mars! project. Everything is then inserted into three MySQL database tables:

  • bookmarks: the table storing bookmark title, url, note, creation time and privacy flag.
  • tags: all the tags associated with the Delicious bookmarks.
  • tag_relationships: the relationships between the bookmarks and tags.

After doing a pip install to install the requirements, you run DeliciousPy with the command:
$ ./deliciouspy.py -p /path/to/delicious/export_file.html

Running the script exports:

  • Bookmark title
  • URL
  • Tags
  • Note
  • Created date

Getting the Delicious bookmarks into a database opens up options for importing the data into a different platform. You can roll your own custom tool or use a content management system. I chose WordPress.

WordPress as a personal social bookmarking service

My wish list for a Delicious replacement was short:

  • Save links with accompanying notes.
  • Tagging and search to easily call up previously saved links.
  • Simple, bare-bones functionality free of unnecessary features.
  • Set links to private for personal research or public for sharing.
  • Avoid relying on a third-party service.

It didn’t take me long to settle on WordPress. I already have expertise with importing data into WordPress from my Drupal to WordPress migration service. It’s grown into a mature content management platform with a huge user and developer base, apparently powering 25% of all websites. It has excellent documentation, can run on a standard hosting package, has a simple user-interface and a flexible taxonomy system. The code is open source so if you self-host the site, you won’t run into the third-party service problems of getting locked out of your data. WordPress has all the components for a home-brew bookmarking service.

My Bookmarks plugin

I’d originally hoped to avoid building my own plugin but none of those in the WordPress Plugin Directory suited my needs. Most were too feature-packed and I wanted something lightweight: just have some way of saving and tagging links for later retrieval. Putting together simple bookmark storage isn’t difficult: ‘bookmarks’ can be nothing more than a post with a single custom field to store the URL. To keep them separate from standard blog posts, I created a custom bookmark content type and packaged it into a plugin called My Bookmarks. (I’m not very imaginative when naming things.)

Delicious has a social bookmarking aspect that lets you publish your links, follow other users and discover pages those in your network have saved. Social bookmarking wasn’t compelling for me so I didn’t attempt to include this functionality. In my opinion, publishing the links on a blog is social enough and any anyone who wants to ‘follow’ can subscribe to the RSS feed built into WordPress.

Bookmark sharing id controlled through the normal WordPress Visiblity setting. Set to Public so the bookmark gets posted on your blog; set to Private so it’s visible only after logging in to WordPress.

One great thing about Delicious was a handy bookmarklet that allowed you bookmark your browser’s current open page. Rather annoyingly, this feature broke for me at some point after it was acquired by one of its many owners. I implemented this functionality in the form of a WordPress-style Press This bookmarklet. Code from the now seemingly abandoned Linkmarklet plugin by Jonathan Christopher does most of this work.

Experience so far

The My Bookmarks plugin hasn’t been published to the WordPress Plugin Directory as of now. I’m still trialling it to make sure everything works as expected before releasing but you’re welcome to grab the source and tinker with it for your own needs. Note: As of December 2016, the My Bookmarks plugin is temporarily hosted over on GitLab.

Currently the plugin is installed on my internal WordPress-based Knowledge Management System on my local network. It serves up thousands of bookmarks from around 10 years worth of links. I replicated everything that made Delicous so useful for me without all the drawbacks of a third-party service. So far so good. The bookmarks that I’ve spent years curating are now fully under my control—and under my responsibility. Backups are still important but bookmarks are now included with my usual automated WordPress database dumps. All-in-all, I think it was a good move to finally migrate from Delicious.

Book photo by Katia Grimmer-Laversanne from freeimages.com