Drupal 7 to Wordpress 5 Database Migration

Video

Links

• https://wpengine.com/wp-content/uploads/2017/02/WP-WP-MigratingfromDrupalToWordPress-05-PUB.pdf
• https://anothercoffee.net/drupal-to-wordpress-migration-sql-queries-explained

Database Script

The original SQL file that I walked through in the video was deleted and thus I had to retype it from what was shown in the video. Therefore, if you find a mistake in the below query, I ask that you contact me with the mistake that you found so that I may correct it. Two eyes are better than one.

use dthealmostengineerwp;

set autocommit = 0;
commit;

-- clear all tables

truncate table awp5_commentmeta;
truncate table awp5_comments;
truncate table awp5_links;
truncate table awp5_postmeta;
truncate table awp5_posts;
truncate table awp5_termmeta;
truncate table awp5_terms;
truncate table awp5_term_relationships;
truncate table awp5_term_taxonomy;

--- clear users

delete from awp5_users where id > 1;
delete from awp5_usermeta where user_id > 1;

-- copy over tags

replace into dthealmostengineerwp.awp5_terms
(term_id, awp5_terms.name, slug, term_group)
select
    distinct d.tid,
    d.name,
    REPLACE(LOWER(d.name), '', '_'), 0
    FROM dthealmostengineerd7.bsal_taxonomy_term_data d
    INNER JOIN dthealmostengineerd7.bsal_taxonomy_term_hierarchy h
    USING (tid)
    WHERE (1);

INSERT INTO dthealmostengineerwp.awp5_term_taxonomy
(term_id, taxonomy, description, parent)
SELECT
DISTINCT d.tid `term_id`,
'post_tag' `taxonomy`,
ifnull(d.description, '') `description`,
h.parent `parent`
FROM
dthealmostengineerd7.bsal_taxonomy_term_data d
INNER JOIN
dthealmostengineerd6.bsal_taxonomy_term_hierarchy h
USING (tid)
INNER JOIN
dthealmostengineerd7.bsal_taxonomy_index n
USING (tid)
WHERE (1);

-- copy over posts

INSERT INTO
dthealmostengineerwp.awp5_posts(id, post_author, post_date,
    post_date_gmt,
    post_name,
    post_content,
    post_title, post_excerpt,
    post_modified, post_modified_gmt,
    post_type, post_status,
    to_ping, pinged, post_content_filtered, post_mime_type,
    comment_status)
SELECT DISTINCT n.nid `id`, n.uid `post_author`, FROM_UNIXTIME(n.created) `post_date`,
FROM_UNIXTIME(n.created) `post_date_gmt`,
IF(SUBSTR(a.alias, 11, 1) = '/', SUBSTR(a.alias, 12), a.alias) `post_name`,
body.body_value `post_content`, n.title `post_title`, '' `post_excerpt`,
FROM_UNIXTIME(n.changed) `post_modified`, FROM_UNIXTIME(n.changed) `post_modified_gmt`,
n.type `post_type`, IF(n.status= 1, 'publish', 'private') `post_status`,
'' `to_ping`, '' `pinged`, '' `post_content_filtered`, '' `post_mime_type`,
'closed' as comment_status
FROM dthealmostengineerd7.bsal_node n
INNER JOIN dthealmostengineerd7.bsal_node_revision r
USING (vid)
left outer join dthealmostengineerd7.bsal_field_data_body body
on body.revision_id = n.vid
LEFT OUTER JOIN dthealmostengineerd7.bsal_url_alias a
ON a.source=CONCAT('node/', n.nid)
WHERE n.type IN ('post', 'page', 'blog', 'article', 'portfolio_item') and
-- remove the part below if you do not have duplicate aliases in your D7 database 
a.alias not in ('client-faq', 'liftmaster-8500-review-1-year-later-almost-afro-engineered',
'install-drush-ubuntu', 'install-chrome-ubuntu', 'sow-app',
'selenium-webdriver-photo-uploader', 'changing-battery-nissan-altima-intelligent-key',
'how-start-garden', 'when-stuck-do-something-different',
'i-can-do-without-app', '7-reason-i-can-do-without-app',
'testing-markdown', 'wysiwyg-vs-markdown',
'yes-i-want-my-cornbread', 'testing-deploying', 'copying-bad-code',
'copying-termites-your-code')
;

-- combine post types

update dthealmsotengineerwp.awp5_posts
set post_type = 'post'
where post_type in ('blog', 'article', 'portfolio_item')
;

-- to define the post/tag relationship, apply the following:

INSERT INTO dthealmostengineerwp.awp5_term_relationships (object_id, term_taxonomy_id)
SELECT DISTINCT nid, tid
FROM dthealmostengineerd7.bsal_taxonomy_index;

-- update tag counts 

UPDATE dthealmostengineerwp.awp5_term_taxonomy tt
SET `count` = (SELECT COUNT(tr.object_id)
FROM dthealmostengineerwp.awp5_term_relationships tr
WHERE tr.term_taxonomy_id=tt.term_taxonomy_id);

-- Apply this query to migrate comments:

-- INSERT INTO dthealmsotengineerwp.awp5_comments(comment_post_ID, comment_date, 
-- comment_content, comment_parent, comment_author,comment_author_email, comment_author_url, 
-- comment_approved)
-- SELECT DISTINCT nid, FROM_UNIXTIME(timestamp), comment, thread, name, mail, homepage, ((status + 1) % 2)
-- FROM dthealmostengineerd7.bsal_comments;

-- Update comments count on wp_posts table.

-- UPDATE dthealmostengineerwp.awp5_posts
-- SET `comment_count` = (SELECT COUNT(`comment_post_id`)
-- FROM dthealmostengineerwp.awp5_comments
-- WHERE dthealmostengineerwp.awp5_posts.`id` = dthealmostengineerwp.awp5_comments.`comment_post_id`);

-- update links to images and files

update dthealmostengineerwp.awp5_posts
set post_content = replace(ifnull(post_content, ''), '/files', '/wp-content/uploads');

-- fix taxonomy

update ignore dthealmostengineerwp.awp5_term_relationships, dthealmostengineerwp.awp5_term_taxonomy
set dthealmostengineerwp.awp5_term_relationships.term_taxonomy_id = dthealmostengineerwp.awp5_term_taxonomy.term_taxonomy_id
where dthealmostengineerwp.awp5_term_relationships.term_taxonomy_id = dthealmostengineerwp.awp5_term_taxonomy.term_id
;

-- copy user accounts

insert into dthealmostengineerwp.awp5_users
(id, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
select 
uid, name, '' as user_pass, name, mail, 'http://blueprint/almostengineer/aewordpress/wordpress' as user_url,
FROM_UNIXTIME(created) as registered, '' as user_key,
0 as status, name as display_name
FROM dthealmostengineerd7.bsal_users
where uid >= 2
;

-- assign users administrator status

UPDATE dthealmostengineerwp.awp5_usermeta
SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
WHERE user_id in (1) and meta_key = 'wp_capabilities';

UPDATE dthealmostengineerwp.awp5_usermeta
SET meta_value = '10'
where user_id in (1) and meta_key = 'wp_user_level';

update dthealmostengineerwp.awp5_posts
set post_author = 1
where post_author NOT IN (select distinct id from dthealmostengineerwp.awp5_users);

update dthealmostengineerwp_awp5_posts
set post_name = reverse(substring(reverse(post_name), 1, locate('/', reverse(post_name))-1));

commit;