Blog

Drupal to WordPress migration: posts table mapping

Following on from Drupal to WordPress migration explained, I will create a series of posts documenting the table mappings for a site migration from Drupal 6.x to WordPress 3.x.

To read the mapping, you look up the Drupal table on the left listing the fields we use for a migration. Directly to its right is the WordPress table with the corresponding field in the same row. So for example, the nid in Drupal’s node table is exported to the id field in the WordPress wp_posts table.

I have listed all the fields used in the query. If a Drupal field shows no mapping in the WordPress table, it is being used to match entries in another table for a join. Here we use the vid field in node and node_revisions for an INNER JOIN.

Table mapping for WordPress wp_posts

Drupal 6.x

WordPress 3.x

Notes

node

wp_posts

nid

id

id

post_author

created

post_date

Create date from UNIX timestamp

title

post_title

changed

post_modified

Create date from UNIX timestamp

type

post_type

status

post_status

vid

node_revisions

body

post_content

teaser

post_excerpt

vid

node

url_alias

nid

dst

post_name

If dst field is NULL, use nid

src

to_ping

Whitespace string

pinged

Whitespace string

post_content_filtered

Whitespace string

Query

REPLACE INTO wordpress.wp_posts (
id,
post_author,
post_date,
post_content,
post_title,
post_excerpt,
post_name,
post_modified,
post_type,
post_status,
to_ping,
pinged,
post_content_filtered)
SELECT DISTINCT
n.nid ‘id’,
n.uid ‘post_author’,
DATE_ADD(FROM_UNIXTIME(0), interval n.created second) ‘post_date’,
r.body ‘post_content’,
n.title ‘post_title’,
r.teaser ‘post_excerpt’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘post_name’,
DATE_ADD(FROM_UNIXTIME(0), interval n.changed second) ‘post_modified’,
n.type ‘post_type’,
IF(n.status = 1, ‘publish’, ‘private’) ‘post_status’,
‘ ‘,
‘ ‘,
‘ ‘
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the content types you want to migrate */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);

Drupal to WordPress migration SQL queries explained

In this post I will give a step-by-step explanation of my Drupal to WordPress migration SQL queries. For general information about migrating from Drupal to WordPress, please see instead my Drupal to WordPress Migration Guide.

Drupal to WordPress migration queries screenshot

Since I offer site migration as a paid service, readers might be wondering why I’m giving away some of my secret sauce. The simple answer is that the ingredients of the sauce are anything but secret. A web search brings up blog posts and tutorials detailing how to go about it. In fact, the first version of my own Drupal to WordPress migration tool was based on a blog post by another web company.

However, while the knowledge is freely available, the whole process can be a real pain, especially when you’re dealing with a Drupal installation with lots of content and customisations. In my experience, migrating an established site from Drupal to WordPress requires all of the following:

  • An intermediate to advanced level of technical skill;
  • Time to plan, run the migration and do post-migration clean-up;
  • A great deal of patience.

If you are a developer with the right skills, you still need to consider the time investment and effort needed to understand both Drupal and WordPress database schemas. Often, the man-hours required will make a client think twice about proceeding with the project. Fortunately (or unfortunately) for me, I’ve needed to put in the time because my maintenance agreement for some clients covered exactly this sort of work. I’ve therefore built up enough experience to offer competitive quotes for migration of even complex sites. In my very biased opinion, you’ll make better use of your time and budget by getting me to do the task!

Of course, there are still cases where, for whatever reason, it’s not viable to offload the work to someone else. If this sounds like your situation, you’ll probably figure things out eventually so I might as well help you along.

Prerequisites for the Drupal to WordPress migration script

To run this migration you will need:

  • A working installation of Drupal 6;
  • A clean installation of WordPress 3.5 or above;
  • Access to the Drupal and WordPress MySQL databases;
  • The ability to run SQL queries on both databases;
  • Both databases on the same MySQL server;
  • Access to the Drupal and WordPress installations.

Ideally, you should:

  • Run the migration from a development server (it’s best not to risk running this on your live server);
  • Backup your live Drupal database before beginning the migration;
  • Be comfortable with running database operations;
  • Have planned the migration beforehand (e.g. which content types and taxonomies should be migrated).

If you have an older version of Drupal, simply upgrade to Drupal 6 first, then run the migration. I mention WordPress 3.5 as a prerequisite because that’s where I’ve done most testing. You can probably get away with migrating straight into a more recent version but to avoid any problems, I suggest you start off with WordPress 3.5. It’s easy to upgrade to the latest WordPress version after converting the Drupal content.

The Drupal to WordPress migration SQL queries

Keep in mind that this article is based on my Drupal to WordPress migration tool but with some additional queries written for the specific needs of a client. It therefore includes some values which will not apply to your site. I’ve stripped out any identifying information but left generic data to provide an example. You will need to manually look up the correct values in the Drupal database for your installation.

Another thing to keep in mind is that I’ve favoured readability over efficiency. For example, it’s possible to write more complex queries to avoid creating working tables but that would make debugging more difficult. Having a trail of data changes can help with content analysis if the end results aren’t what you expected.

CAUTION: Make a backup of both your Drupal and WordPress databases before running these queries. USE IS ENTIRELY AT YOUR OWN RISK. I’m offering this information with no warranty or support implied.

Example data in this scenario

  • wordpress: the WordPress database name.
  • drupal: the Drupal database name.
  • acc_ table prefixes: these are working tables I create to help with migrating data.

Clear out some Drupal and WordPress tables

For most migration projects to date, I’ve needed to run through several passes of the queries as I make incremental adjustments based on the client’s feedback. I define a ‘pass’ as one iteration of the entire migration process and inspecting the results in a WordPress installation. Depending on your project requirements, you may need to do this several times, making little tweaks to the MySQL queries as you go along.

These Drupal and WordPress tables may be empty or non-existent if you’re running the queries for the first time but it makes sense to clear them out at the start.

TRUNCATE TABLE wordpress.wp_comments;
TRUNCATE TABLE wordpress.wp_links;
TRUNCATE TABLE wordpress.wp_postmeta;
TRUNCATE TABLE wordpress.wp_posts;
TRUNCATE TABLE wordpress.wp_term_relationships;
TRUNCATE TABLE wordpress.wp_term_taxonomy;
TRUNCATE TABLE wordpress.wp_terms;
TRUNCATE TABLE wordpress.wp_users;

DROP TABLE IF EXISTS drupal.acc_duplicates;
DROP TABLE IF EXISTS drupal.acc_news_terms;
DROP TABLE IF EXISTS drupal.acc_tags_terms;
DROP TABLE IF EXISTS drupal.acc_wp_tags;
DROP TABLE IF EXISTS drupal.acc_users_post_count;
DROP TABLE IF EXISTS drupal.acc_users_comment_count;
DROP TABLE IF EXISTS drupal.acc_users_with_content;
DROP TABLE IF EXISTS drupal.acc_users_post_count;

For some installations, I make changes to the wp_usermeta table so that needs to be cleared too.

TRUNCATE TABLE wordpress.wp_usermeta;

Vocabularies and taxonomies

Delete unwanted vocabularies. You’ll need to look in your Drupal vocabulary table for the appropriate vids. In this case, I’m deleting vocabularies 5, 7, 8, 38 and 40.

DELETE FROM drupal.vocabulary WHERE vid IN (5, 7, 8, 38, 40);

Delete terms associated with unwanted vocabularies. Here I’m keeping the terms for vid 38. Sometimes you might want to keep some terms of unwanted vocabularies for later conversion into into WordPress tags. (Please see the next query.)

DELETE FROM drupal.term_data WHERE vid IN (5, 7, 8, 40);

You may want to merge terms. In this example, I am merging the previously saved terms for News which has vid 38 to the Tags vocabulary terms which has vid 2.

We will need to deal with duplicates. For example, in the Drupal installation, ‘science’ could appear in both News (vid 38) and Tags (vid 2). This will cause a problem when exporting to WordPress since we can’t have duplicate terms. Here I create working tables for both term groups.

CREATE TABLE drupal.acc_news_terms AS SELECT tid, vid, name FROM drupal.term_data WHERE vid=38;
CREATE TABLE drupal.acc_tags_terms AS SELECT tid, vid, name FROM drupal.term_data WHERE vid=2;

Create a working table from duplicates.

CREATE TABLE drupal.acc_duplicates AS
SELECT t.tid tag_tid,
n.tid news_tid,
t.vid tag_vid,
n.vid news_vid,
t.name
FROM drupal.acc_tags_terms AS t
INNER JOIN (drupal.acc_news_terms AS n)
ON n.name=t.name;

Append a string to News terms duplicates so they won’t clash during migration. Here I used a fixed string but this won’t work if you have more than two terms with the same name. If you expect many terms with the same name, it would be better to generate a unique number. For example, using the tid would make it unique since these are unique primary keys. Use whatever string makes sense for your project.

Note that we’re overwriting the source data in the Drupal term_data table so proceed with care. Make sure you have a backup of your pre-migration Drupal tables in case you need to run the conversion again.

UPDATE drupal.term_data
SET name=CONCAT(name, ‘_01’)
WHERE tid IN (SELECT news_tid FROM drupal.acc_duplicates);

Convert Drupal News terms to Drupal Tags. We’ll migrate the whole lot into WordPress tags later.

UPDATE drupal.term_data SET vid=2 WHERE vid=38;

Create a table of WordPress tags. Exclude any terms from Drupal vocabularies that you might later migrate into WordPress categories. See the MySQL queries below where I create WordPress categories and sub-categories.

Here, all Drupal vocabularies except 37, 36 and 35 will be converted WordPress tags.

CREATE TABLE drupal.acc_wp_tags AS
SELECT
tid,
vid,
name
FROM drupal.term_data
WHERE vid NOT IN (37, 36, 35);

Now create the tags in the WordPress database. A clean WordPress database will have term_id=1 for ‘Uncategorized’. Use REPLACE as this may conflict with a Drupal tid.

We are assuming that this point the Drupal term_data table has been cleaned of any duplicate names. Any duplicate terms will be lost when running this MySQL query.

REPLACE INTO wordpress.wp_terms (term_id, name, slug, term_group)
SELECT
d.tid,
d.name,
REPLACE(LOWER(d.name), ‘ ‘, ‘_’),
d.vid
FROM drupal.term_data d WHERE d.tid IN (
SELECT t.tid FROM drupal.acc_wp_tags t
);

In WordPress, tags and categories are all stored in the wp_term_taxonomy table. The taxonomy field specifies whether it’s a tag or category by setting the field string to either ‘post_tag’ or ‘category’.

Here I convert these Drupal terms into WordPress tags.

REPLACE INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent)
SELECT DISTINCT
d.tid,
d.tid ‘term_id’,
‘post_tag’, /* This string makes them WordPress tags */
d.description ‘description’,
0 /* In this case, I don’t give tags a parent */
FROM drupal.term_data d
WHERE d.tid IN (SELECT t.tid FROM drupal.acc_wp_tags t);

Create the categories and sub-categories in the WordPress database. This may be unnecessary depending on your setup.

Add terms associated with a Drupal vocabulary into WordPress. Note that in this case, these are the same vids that I excluded from the tag table above.

REPLACE INTO wordpress.wp_terms (term_id, name, slug, term_group)
SELECT DISTINCT
d.tid,
d.name,
REPLACE(LOWER(d.name), ‘ ‘, ‘_’),
d.vid
FROM drupal.term_data d
WHERE d.vid IN (37, 36, 35);

Convert these Drupal terms into WordPress sub-categories by setting the parent field in the wp_term_taxonomy table.

REPLACE INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent)
SELECT DISTINCT
d.tid,
d.tid ‘term_id’,
‘category’,
d.description ‘description’,
d.vid
FROM drupal.term_data d
WHERE d.vid IN (37, 36, 35);

Now add the vocabularies to the WordPress terms table. There’s no need to set term_id as vocabularies are not directly associated with posts.

INSERT INTO wordpress.wp_terms (name, slug, term_group)
SELECT DISTINCT
v.name,
REPLACE(LOWER(v.name), ‘ ‘, ‘_’),
v.vid
FROM drupal.vocabulary v
WHERE vid IN (37, 36, 35);

Insert Drupal vocabularies as WordPress categories.

INSERT INTO wordpress.wp_term_taxonomy (
term_id,
taxonomy,
description,
parent,
count)
SELECT DISTINCT
v.vid,
‘category’, /* This string makes them WordPress categories */
v.description,
v.vid,
0
FROM drupal.vocabulary v
WHERE vid IN (37, 36, 35);

Update the WordPress term groups and parents.

Before continuing with this step, we need to manually inspect the table to get the term_id for the parents inserted above. In this case, vids 37, 36 and 35 were inserted as into the wp_term_taxonomy table as term_ids 7517, 7518 and 7519. I will use them as the parents for their respective terms. In other words, terms that formerly belonged to the Drupal vocabulary ID 37 would now belong to the WordPress parent category 7519.

UPDATE wordpress.wp_terms SET term_group=7519 WHERE term_group=37;
UPDATE wordpress.wp_terms SET term_group=7518 WHERE term_group=36;
UPDATE wordpress.wp_terms SET term_group=7517 WHERE term_group=35;

UPDATE wordpress.wp_term_taxonomy SET parent=7519 WHERE parent=37;
UPDATE wordpress.wp_term_taxonomy SET parent=7518 WHERE parent=36;
UPDATE wordpress.wp_term_taxonomy SET parent=7517 WHERE parent=35;

UPDATE wordpress.wp_term_taxonomy SET term_id=7519 WHERE term_taxonomy_id=7519;
UPDATE wordpress.wp_term_taxonomy SET term_id=7518 WHERE term_taxonomy_id=7518;
UPDATE wordpress.wp_term_taxonomy SET term_id=7517 WHERE term_taxonomy_id=7517;

Re-insert the Uncategorized term replaced earlier in the conversion process. We may have replaced or deleted the Uncategorized category during a previous MySQL query. Re-insert it if you want an Uncategorized category.

INSERT INTO wordpress.wp_terms (name, slug, term_group)
VALUES (‘Uncategorized’, ‘uncategorized’, 0);
INSERT INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent,
count)
SELECT DISTINCT
t.term_id,
t.term_id,
‘category’,
t.name,
0,
0
FROM wordpress.wp_terms t
WHERE t.slug=’uncategorized’;

Converting Drupal nodes to WordPress posts

Now create WordPress posts from Drupal nodes. This may take a while if you have many Drupal nodes. Wait until the query completes before continuing. It could take several minutes.

REPLACE INTO wordpress.wp_posts (
id,
post_author,
post_date,
post_content,
post_title,
post_excerpt,
post_name,
post_modified,
post_type,
post_status,
to_ping,
pinged,
post_content_filtered)
SELECT DISTINCT
n.nid ‘id’,
n.uid ‘post_author’,
DATE_ADD(FROM_UNIXTIME(0), interval n.created second) ‘post_date’,
r.body ‘post_content’,
n.title ‘post_title’,
r.teaser ‘post_excerpt’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘post_name’,
DATE_ADD(FROM_UNIXTIME(0), interval n.changed second) ‘post_modified’,
n.type ‘post_type’,
IF(n.status = 1, ‘publish’, ‘private’) ‘post_status’,
‘ ‘,
‘ ‘,
‘ ‘
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the content types you want to migrate */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);

Set the Drupal content types that should be migrated as WordPress ‘posts’. In this case, I want ‘page’, ‘story’, ‘blog’, ‘video’, ‘forum’ and ‘comment’ in Drupal to be converted to posts in WordPress.

UPDATE wordpress.wp_posts SET post_type = ‘post’
WHERE post_type IN (
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);

Now convert the remaining content types into WordPress pages.

UPDATE wordpress.wp_posts SET post_type = ‘page’ WHERE post_type NOT IN (‘post’);

Housekeeping queries for terms

Here I associate the content with WordPress terms using the wp_term_relationships table.

INSERT INTO wordpress.wp_term_relationships (
object_id,
term_taxonomy_id)
SELECT DISTINCT nid, tid FROM drupal.term_node;

We need to update tag counts.

UPDATE wordpress.wp_term_taxonomy tt
SET count = ( SELECT COUNT(tr.object_id)
FROM wordpress.wp_term_relationships tr
WHERE tr.term_taxonomy_id = tt.term_taxonomy_id);

Now set the default WordPress category. You’ll need to manually look in the database for the term_id of the category you want to set as the default WordPress category.

UPDATE wordpress.wp_options SET option_value=’7520′ WHERE option_name=’default_category’;
UPDATE wordpress.wp_term_taxonomy SET taxonomy=’category’ WHERE term_id=7520;

Migrate comments

REPLACE INTO wordpress.wp_comments (
comment_ID,
comment_post_ID,
comment_date,
comment_content,
comment_parent,
comment_author,
comment_author_email,
comment_author_url,
comment_approved)
SELECT DISTINCT
cid,
nid,
FROM_UNIXTIME(timestamp),
comment,
pid,
name,
mail,
SUBSTRING(homepage,1,200),
((status + 1) % 2) FROM drupal.comments;

Update comment counts.

UPDATE wordpress.wp_posts
SET comment_count = ( SELECT COUNT(comment_post_id)
FROM wordpress.wp_comments
WHERE wordpress.wp_posts.id = wordpress.wp_comments.comment_post_id);

Migrate Drupal Authors into WordPress

In this case I am migrating only users who have created a post. This was a requirement for my project but may be unnecessary for you.

First delete all existing WordPress authors except for admin.

DELETE FROM wordpress.wp_users WHERE ID > 1;
DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;

Now set Drupal’s admin password to a known value. This avoids hassles with trying to reset the password on the new WordPress installation. Resetting a user password in WordPress is more convoluted and cannot be done using a simple MySQL query as in Drupal.

UPDATE drupal.users set pass=md5(‘password’) where uid = 1;

Create a working table of users and the number of posts they’ve authored. I am only considering authors who have created posts of the content types I want to migrate.

CREATE TABLE drupal.acc_users_post_count AS
SELECT
u.uid,
u.name,
u.mail,
count(n.uid) node_count
FROM drupal.node n
INNER JOIN drupal.users u on n.uid = u.uid
WHERE n.type IN (
/* List the post types I migrated earlier */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’)
GROUP BY u.uid
ORDER BY node_count;

Now add these authors into the WordPress wp_users table.

INSERT IGNORE INTO wordpress.wp_users (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_registered,
user_activation_key,
user_status,
display_name)
SELECT DISTINCT
u.uid,
REPLACE(LOWER(u.name), ‘ ‘, ‘_’),
u.pass,
u.name,
u.mail,
FROM_UNIXTIME(created),
”,
0,
u.name
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);

First set all these authors to WordPress “author” by default. In the next MySQL query, we can selectively promote individual authors to other WordPress roles.

INSERT IGNORE INTO wordpress.wp_usermeta (
user_id,
meta_key,
meta_value)
SELECT DISTINCT
u.uid,
‘wp_capabilities’,
‘a:1:{s:6:”author”;s:1:”1″;}’
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);

INSERT IGNORE INTO wordpress.wp_usermeta (
user_id,
meta_key,
meta_value)
SELECT DISTINCT
u.uid,
‘wp_user_level’,
‘2’
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);

During the course of the migration, some posts may end up not having an assigned author. Here I reassign authorship for these posts to the WordPress admin user.

UPDATE wordpress.wp_posts
SET post_author = 1
WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users);

Comment authors

In Drupal, comments are treated as nodes and comment authors are stored along with other node authors in the Drupal users table. WordPress treats comments and comment authors differently. Comment authors in WordPress are not stored in the wp_users table. Instead, they’re stored along with the comment content itself in the wp_comments table.

We may need to run additional query to import users who have commented but haven’t created any of the selected content types. To do this, I create some working tables required for some later MySQL queries:

  • acc_users_with_comments: empty copy of wp_users
  • acc_users_add_commenters: empty copy of wp_users
  • acc_wp_users: copy of wp_users from wordpress database containing users

Running the following MySQL queries will throw errors if you haven’t created the required tables above.

First create a working table of Drupal users who have created a Drupal comment.

CREATE TABLE drupal.acc_users_comment_count AS
SELECT
u.uid,
u.name,
count(c.uid) comment_count
FROM drupal.comments c
INNER JOIN drupal.users u on c.uid = u.uid
GROUP BY u.uid;

Now add the author information for these users into another working table.

INSERT IGNORE INTO drupal.acc_users_with_comments (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_registered,
user_activation_key,
user_status,
display_name)
SELECT DISTINCT
u.uid,
REPLACE(LOWER(u.name), ‘ ‘, ‘_’),
u.pass,
u.name,
u.mail,
FROM_UNIXTIME(created),
”,
0,
u.name
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_comment_count);

Using the above table, next build a working table of Drupal users who have commented but have not already been added to the WordPress wp_users table.

INSERT IGNORE INTO drupal.acc_users_add_commenters (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_registered,
user_activation_key,
user_status,
display_name)
SELECT DISTINCT
u.ID,
u.user_login,
u.user_pass,
u.user_nicename,
u.user_email,
u.user_registered,
”,
0,
u.display_name
FROM drupal.acc_users_with_comments u
WHERE u.ID NOT IN (SELECT ID FROM drupal.wp_users);

Combine the tables into another working table acc_wp_users.

INSERT IGNORE
INTO drupal.acc_wp_users
SELECT *
FROM drupal.acc_users_add_commenters;

The acc_wp_users working table helps when inspecting the user list. For example, you might want to clear out inactive users or spam posters. Once finished, remember to replace your WordPress wp_users with the cleaned acc_wp_users table. You may prefer to amend the above query to insert directly into the WordPress wp_users table.

I realise this is a rather round-about way of migrating comment authors from Drupal into WordPress but find that having working tables helps with debugging.

Housekeeping for WordPress options

Update file path for the WordPress installation.

UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘”/files/’, ‘”/wp-content/uploads/’);

Set your WordPress site name using the Drupal ‘site_name’ variable.

UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_name’) WHERE option_name = ‘blogname’;

Set your WordPress site description using the Drupal ‘site_slogan’ variable.

UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_slogan’) WHERE option_name = ‘blogdescription’;

Set the WordPress site email address.

UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_mail’) WHERE option_name = ‘admin_email’;

Set the WordPress permalink structure. Here we’re using /%postname%/ but you may set it according your own needs.

UPDATE wordpress.wp_options SET option_value = ‘/%postname%/’ WHERE option_name = ‘permalink_structure’;

Create URL redirects table

This table will not be used for the migration but may be useful if you need to manually create redirects from Drupal aliases. You will need the entries here for search engine optimisation (SEO) of your new WordPress site.

DROP TABLE IF EXISTS drupal.acc_redirects;
CREATE TABLE drupal.acc_redirects AS
SELECT
CONCAT(‘drupal/’,
IF(a.dst IS NULL,
CONCAT(‘node/’, n.nid),
a.dst
)
) ‘old_url’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘new_url’,
‘301’ redirect_code
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the post types I migrated earlier */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);

Finalising the conversion

Now that we’ve finished converting the content over from Drupal to WordPress, we have the rather (not very) fun job of checking the content, setting up any WordPress plugins and widgets, then finally going live. Depending on the complexity of your Drupal installation, this process can be extremely time-consuming and perhaps form a separate project in its own right.

You can use my Drupal to WordPress migration notes to help with going live. Of course, the search for equivalent WordPress plugins and conversion from the old Drupal modules will have to be done according to your specific set-up.

Accepting limitations

Before finishing this article, one point I’d like to convey is the importance of accepting the limitations of any migration process. You, or your client, may be insistent on turning your new WordPress site into an exact copy of your former Drupal installation. While this may be technically possible, there comes a point of diminishing returns where the work you’d need to put in just isn’t worth the value of the data.

It may also be more productive in some instances to make manual adjustments via the WordPress control panel than to try anything clever using the backend database. Sometimes the time needed to write, test and debug MySQL queries far exceeds the boring but more reliable editing using the web-front end.

Expect to kill your SEO if you’re not careful with the migration. A site that relies heavily on revenue from search engine rankings will need extra steps to preserve SEO. This is a huge topic so I will not cover it here but you should carefully plan the conversion steps before starting with the migration. Pay particular attention to preserving Drupal path aliases, taxonomy listing pages and internal links.

Good luck!

So that’s it. A Drupal to WordPress migration can be a great deal of effort. I’ve had one project that initially looked like only couple of hours work balloon to over 50 billable hours in total. Previously installed modules caused problems requiring custom MySQL queries and PHP scripting to resolve. On the other hand, I’ve had a number of sites that completed in 15 minutes after running my Drupal to WordPress migration tool.

Overall, the majority of my clients prefer WordPress over their previous Drupal site. I personally find WordPress quicker to update and manage. Any short-term hassles with migrating have been outweighed by the long-term advantages of easier maintenance.

Getting the code and submitting improvements

You can find the code on my GitHub repository. The queries in this article can be found in the file drupaltowordpress-custom.sql.

I’d love to receive corrections, bug fixes and suggestions for improvements. Please contact me or submit an issue on GitHub.

CAUTION: Make a backup of both your Drupal and WordPress databases before running these queries. USE IS ENTIRELY AT YOUR OWN RISK. I’m offering this information with no warranty or support implied.

Drupal to WordPress Migration Tool

The Drupal to WordPress Migration Tool exports the bulk of the content from Drupal 6 to WordPress 3.5. It runs a series of MySQL queries to read information from a Drupal database and convert the content to WordPress. Please see this post for some background on the project.

A web front end is available for very basic Drupal installations. Sample SQL queries are included for more complex setups. It is an open source project released under The MIT License and hosted on GitHub.

The Drupal to WordPress Migration Tool shows how I like to structure code but since it’s an on-going project, it should be considered alpha code. (It uses HTML, JavaScript, jQuery and PHP but should be considered alpha code.)

Q: What do you need to get started with migrating a site from Drupal to WordPress?

The minimum requirement is some sort of access to your Drupal database. For security, we’d prefer not have access to your live servers so a compressed MySQL database dump file of your Drupal site is ideal. If you have no-one to create the database dump file, we can create one on your behalf by logging in to your MySQL control panel or database server.

In the cases of large databases, we’d need to work directly on your server since transferring dump files of many gigabytes is impractical. A good practice would then be to clone the database and give us a separate login to the server. That way we won’t have to touch your live server and data.

An admin login to the Drupal control panel on a clone of your site would be useful for fine-tuning and debugging. This is normally not necessary for the first test export.

We generally take care of setting up the entire development environment on our servers. This includes creating an empty WordPress site for displaying results and for some complex migrations, a clone of your Drupal site for analysis. If you have limited budget, you can save on fees by providing us with the development environment set up and ready to use.

Q: How much will it cost to migrate my site from Drupal to WordPress?

Please see my dedicated migration site for the latest pricing.

Since there are so many variations between Drupal installations, it’s not practical to give reliable cost and time estimates from the outset.

We really only start to understand the scope of the work after running the first few exports. Furthermore, the modules installed during the life of your Drupal site may have altered the structure of the database. This affects how cleanly we can run the migration and how many adjustments will be necessary.

Nevertheless, you remain the driver of how much work we put in. Some site owners request lots of adjustments while others are happy to have just a few aspects of their old site migrated. The biggest factor affecting the budget is therefore the amount of communication needed with regard to the adjustments.

For the bottom-end migration, all we need to do is run through our migration script. You take care of everything else, including setting up the development server. We charge more if you have very rigid requirements which involve writing lots of code to automate tasks.

Read more about our migration process and how it may affect your budget.

Q: What is your process for Drupal to WordPress migrations?

The whole migration process is very much an iterative process of fine tuning until you’re satisfied with the end result. We call each iteration a ‘pass’ because we pass through the database with our migration script repeatedly, making slight adjustments as we gather more information.

Since there are so many variations between Drupal installations, we really only start to understand the scope of the work after running the first few exports. Our process starts off as follows:

  1. You let us know your requirements and give access to your site’s database;
  2. We run a test export and show you the result using a standard WordPress installation;
  3. You let us know how much more fine-tuning is needed.

So for example, we’d run through the first pass and ask you to take a look at the content. You let us know if you discover any issues, like missing posts or meta information (e.g. tags, authors and dates). We then adjust the migration script and run another pass. This repetition continues until you decide it’s good enough.

Why would there be missing information? The number and type of modules you’ve installed during the life of your Drupal site could have effected the structure of the database. This then affects how cleanly we can run each pass of the migration. For example, one client installed a module that ended up silently duplicating certain tags. We had to write custom code to remove duplicates and merge the associated posts. All of this took a great deal of time.

There’s rarely a ‘perfect’ migration due to the differences between the how Drupal and WordPress store the data for your site. Often, ‘good enough’ is a compromise between your budget and how close the WordPress version is compared to the original site. It’s not always possible to get an exact copy in WordPress, especially for more complex Drupal installations.

In fact, it’s usually too expensive to be overly specific about what gets migrated over because we’d write code and debug custom rules. We generally recommend bulk migration of only the aspects that would be tedious to do manually, then getting a human editor to ‘eyeball’ the content to make very specific changes using the WordPress user interface. It’s a boring job for the content editor but it tends to be cheaper and more accurate way of polishing off the migration.

Emergency Plan for iPhone to improve preparedness in the Philippines

FOR IMMEDIATE RELEASE

London, UK: Another Cup of Coffee Limited, a London-based web development company, aims to improve emergency preparedness in the Philippines with the launch of Emergency Plan for iPhone. The app is a simple tool designed to store an emergency plan for an earthquake, flood, typhoon or similar event. Valuable for families who want to ensure their households are prepared to handle a crisis, it can also be used by employers to issue staff with company emergency procedures.

Existing apps tend to offer alert services geared for crisis management professionals or provide standard guidelines easily found online. In contrast, Emergency Plan for iPhone allows ordinary users to save a plan that’s appropriate for their own family or work environment. Its straightforward interface delivers only the important information without unwanted distractions.

Despite the Philippines being highlighted as among the most disaster-prone countries, many Filipinos are still caught off guard by extreme weather events. Founder Anthony Lopez-Vito, who is a British-born Filipino, said, “When Typhoon Yolanda struck, my friends and colleagues with family in the area didn’t know how to find their loved ones. I thought it would be great to have an app people can use for planning before another crisis.” After an unsuccessful search for something suitable, Mr Lopez-Vito decided to build an app to meet the need. He continued: “These days, mobile phones–especially iPhones–are quite common but most apps are for preppers. Since your phone is a constant companion, it makes sense to use an app to keep the important elements of your plan with you at all times.”

Emergency Plan is an iPhone app version of a paper-based emergency plan card. Although electronics may not be useable in a prolonged calamity, it is intended for the immediate unfolding of a crisis. Emergency Plan for iPhone is free for a limited time to gather feedback and make improvements.

Download Emergency Plan for iPhone on the App Store

###


Links and media downloads:

About Another Cup of Coffee Limited:

Another Cup of Coffee Limited is a web development company based in London, England. Founded in 2006, it creates and manages websites for small businesses, media agencies and not-for-profit groups around the world. Although incorporated in the United Kingdom, its talent is made up of independent professionals, many of whom are based in the Philippines.

Contact:

Mr Lan H. Lam
Another Cup of Coffee Limited
Phone: +44 (0) 20 3 290 8898
Email: [email protected]

Editing your WordPress site

Here’s a primer to editing your WordPress site. More detailed information can be found on the WordPress help pages but this should get most clients started quickly.

1. First log in

To login to your WordPress site, go to http://yourdomain.com/wp-admin. (Replace yourdomain.com with the domain for your site.) You will be presented with the login screen.

help-wp-login

Enter the login details you were sent after your site was launched. If you’re logging in for the first time, please remember to change your password under the Users > Your Profile menu item.

 

2. The Dashboard

After logging in, you should see the WordPress Dashboard. If you don’t see the screen below, hover over your site name on the dark menu bar at the top of the screen. You will see an option to switch between Dashboard view and Site view.

help-wp-dashboard

  1. Site name menu allows you to switch between Dashboard view and Site view.
  2. Hovering over your username will allow you to log off.
  3. Click the Posts menu item to view, edit and create new posts.
  4. Click the Pages menu item to view, edit and create new pages.

This WordPress article gives a good overview of the differences between pages and posts.

 

3. Editing content

You can add new content or edit existing ones. Click on the Posts or Pages menu item on the left and either select existing content you want to edit or click Add New.

The editing controls will be quite familiar if you’ve used a word processor like Microsoft Word or Apple Pages.

help-wp-edit-page

  1. The Visual and Text tabs allows you to switch between visual editing with toolbars and HTML coding. If you don’t know how to code web pages, make sure you’ve selected the Visual tab to bring up the editing toolbar.
  2. The editing toolbar works just like a word processor. Highlight your text and select the format option on the toolbar.
  3. Type your text in the text editing are.
  4. Click Publish or Update.

 

4. Logging out

For security, it’s best to log out after completing your edits. If you decide not to log out, you’ll see the WordPress menu bar at the top of your site whenever you visit. This will only be visible to you and other content editors who are logged in. Normal visitors will not see the WordPress menu bar.

WordPress is quite simple to use so this short guide should cover the majority of day-to-day content management tasks for most of our clients under a maintenance package. For help on anything specific to your site, please contact me.

Drupal to WordPress migration notes

These Drupal to WordPress migration notes are intended for clients who are handling some aspects of the migration themselves. Users of the Drupal to WordPress Migration Tool or MySQL queries might also find information here to resolve some problems.

Admin account password and email address

Your content management system (CMS) administrator password and email address may have changed during the migration to help with debugging. Please update them as soon as possible.

Drupal and WordPress user passwords are encrypted and I do not have access to them in plaintext. However, for your peace of mind, I recommend that you also ask all your users to reset their passwords.

Server credentials

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

Working tables

During the migration process, I may create some working tables to hold temporary data. My naming convention for the working tables are as follows:

  • acc_ prefix: these are working tables created to help with migrating data.
  • backup_ prefix: these are backups of existing tables prior to migration, for example from a previous installation of your CMS. These will not be altered during the migration process.
  • _copy postfix: duplicates of WordPress tables copied for debugging will have a _copy postfix. These are different from the backup_ tables in that they may have been manually altered.

These working tables are not used by WordPress so you may safely delete them. However, it’s advisable to keep them for a short while as they may be useful in case we need to debug any issues that crop up.

Migrating to a live server

Most Drupal to WordPress migrations are performed on a test or development server. For help on how to move WordPress to your live server, please see: WordPress Codex: Moving WordPress.

Troubleshooting common errors after a site migration

Please see below for some common problems you may experience after migrating your Drupal content to a new WordPress site.

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.

“Sorry, you are not allowed to access this page”

If you try to log in via wp-admin and receive this error, you may have overwritten the administrator user’s wp_capabilities or wp_user_level.

Set the correct meta_value using the following SQL:

UPDATE wp_usermeta 
    SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
    WHERE user_id = 1 AND meta_key = 'wp_capabilities';

UPDATE wp_usermeta 
    SET meta_value = '10'
    WHERE user_id  = 1 AND meta_key = 'wp_user_level';

Remember to use the appropriate the user_id.

“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:

“Unable to establish database connection”

The database credentials are correct but you see an “Unable to establish database connection” error. Check that wp_options table is not empty.

Allowed memory size exhausted

  1. Increase php.ini memory size
  2. Increase WP settings memory limit with:
    define(‘WP_MEMORY_LIMIT’, ‘128M’);

Post migrated but navigating to post shows blank page

The post is visible in the dashboard but viewing it displays a blank (but themed) page. Manually saving it on the dashboard makes it appear.

It’s possible that the problem posts need to be assigned to a category.

Post migrated but navigating to post shows ‘Not found’ error

The post is visible in the dashboard but viewing it displays a ‘not found’ error. Manually saving it on the dashboard makes it appear.

  1. Check that the url contains valid characters for a WordPress slug.
  2. Check that .htaccess is enabled in your Apache configuration

Link to author 404

Posts exist but link to the author’s post listing page is broken.

Check user_nicename in wp_users WordPress table. Make sure the nicename doesn’t contain invalid characters such as spaces, periods.

Dashboard controls not visible after logging in

If you are able to log in as an administrator user but do not see the Dashboard controls, check your table prefixes. If the table prefixes changed during the migration, you may have forgotten to update the options and usermeta tables.

Check where the old prefixes have been set:

SELECT * FROM `wpnew_options` WHERE `option_name` LIKE '%wp_%';
SELECT * FROM `wpnew_usermeta` WHERE `meta_key` LIKE '%wp_%';

Updated the prefixes. For example:

UPDATE `wpnew_options` SET `option_name` = 'wpnew_user_roles' WHERE `option_name` = 'wp_user_roles';
UPDATE `wpnew_usermeta` SET `meta_key` = 'wpnew_capabilities' WHERE `meta_key` = 'wp_capabilities';
UPDATE `wpnew_usermeta` SET `meta_key` = 'wpnew_user_level' WHERE `meta_key` = 'wp_user_level';

UPDATE `wpnew_usermeta` SET `meta_key` = 'wpnew_user-settings-time' WHERE `meta_key` = 'wp_user-settings-time';
UPDATE `wpnew_usermeta` SET `meta_key` = 'wpnew_user-settings' WHERE `meta_key` = 'wp_user-settings';

Further help

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