When WordPress Treats an Administrator Like a Contributor

The first sign that something was wrong was when I tried to create a new page on the client’s site. The blue Publish button I normally see was replaced with Submit for Review. What the…? That’s what WordPress users with the lowly Contributor role usually see. But I’m an Administrator — the most mighty role known to the world of (single-site) WordPress. (Yes, multi-site installations also confer the fearsome title of Super Admin upon a select few.)

Worse still, if I tried to click Submit for Review, it wouldn’t actually save!

Other problems abounded — I tried to create a new user with Administrator privileges, just to see if my own user account was corrupt. Couldn’t save that, either.

I had Debug Bar installed, and I noticed it was giving an error:

WARNING: wp-admin/includes/post.php:641 - Creating default object from empty value
get_default_post_to_edit

Well, that’s not good. Googling the error didn’t lead to anything immediately helpful, besides this comment that led me to explore the database structure in phpMyAdmin for any problems.

Yes, there were problems. Many of the tables, including wp_options, wp_posts, wp_postmeta and wp_users were missing their primary keys. A bit more digging into the WordPress core showed that, for complex reasons (i.e. I don’t totally get it), without primary keys on these tables, WordPress can’t determine the post type of a new post, and if it can’t determine the post type, it can’t determine the user’s capabilities with regard to that post type, which all comes back to…

WARNING: wp-admin/includes/post.php:641 - Creating default object from empty value
get_default_post_to_edit

Googling on the matter of WordPress tables missing their primary keys (or, perhaps more pertinently, their auto-increments), led me to a solution!!

Fixing WordPress indexes, foreign keys and auto_increment fields

Well, a partial solution. Because the database I was working with was not damaged in exactly the same way as the one the OP was working with, I couldn’t use the sample code directly. I had to go through the database and manually create a few primary keys, delete a bunch of auto-draft posts that all had an ID of 0, etc. Then I had to skip a few lines of the OP’s SQL code because they referred to tables that hadn’t lost their keys in my case, for whatever reason. But this is the… key… to solving the problem.

Now then, how did the database get this way? Well, the site lives on a fairly creaky old Fatcow (ugh, that name) shared hosting account, running an old version of MySQL and an almost unrecognizably ancient version of phpMyAdmin. We were undertaking major content changes on the site, so I copied it over to my own sleek, modern staging server running the latest and greatest of everything. The idea was that we’d get all of our changes in place just the way we wanted on the staging server, rather than mess up the live site for 2-3 weeks, and when we were done, we’d just copy everything back over.

Slick. Right? Sure, if both servers are running reasonably identical software versions. Which of course is never the case. Ever.

Apparently when I copied the site back to Fatcow, due to the older MySQL (or possibly phpMyAdmin) version, certain things like the primary keys and auto-increments — and, I’d be willing to bet, but I’m not sure it matters, the collation as well — got lost along the way.

WordPress challenge of the day: sorting by meta value, including posts WITHOUT that meta value set

I dug around quite a bit for a solution to this today, and eventually I found one, even though it’s a bit ugly.

The problem here is in setting up a WordPress query that sorts posts based on a meta value. I wanted to sort a list of pages by template, but I wanted to include all of the pages, even ones that don’t explicitly have a template set. But the default query was only showing the ones that did have the template value.

Several dead ends almost led me to give up, until I realized it was a JOIN issue. Specifically, the need to change an INNER JOIN to a LEFT JOIN. I just needed to figure out how to do that in the context of WP_Query.

Cut to the chase, here’s what I ended up with.

add_action('pre_get_posts', function($query) {
  if (!is_admin()) { return; }
  $orderby = $query->get('orderby');
  if ('_wp_page_template' == $orderby) {
    $query->set('meta_key','_wp_page_template');
    $query->set('orderby','meta_value');
    // Workaround to include items without this meta key
    // Based on: https://core.trac.wordpress.org/ticket/19653#comment:11
    add_filter('get_meta_sql', function($clauses) {
      $clauses['join'] = str_replace('INNER JOIN','LEFT JOIN',$clauses['join']) . $clauses['where'];
      $clauses['where'] = '';
      return $clauses;
    });
  }
});

I don’t like doing a str_replace() on part of the pre-built query, but sometimes you gotta do what you gotta do. Also note that this is part of a larger function I am writing that is only for use in the admin side; you could remove that is_admin() check if you want this to work everywhere.

I haven’t had a chance to dig into the details of the query to figure out why the original source post included moving $clauses['where'] into $clauses['join'], but it’s essential. I tried skipping it, and it didn’t work.

Sorry I can’t provide any more context here… but I hope it’s helpful to anyone who finds themselves in a similar situation!

How I migrated a client’s blog from Drupal to WordPress 3.0

I just finished migrating a client’s blog from Drupal to WordPress 3.0. When I took on the project, I assumed there was a straightforward way to migrate content from Drupal to WordPress, but I was mistaken. Fortunately, there is a way, but it involves directly mucking about with the Drupal and WordPress databases. Never fear! There are some tools to help.

After a bunch of research, I found the most comprehensive and up-to-date solution on Mike Smullin’s blog. It wasn’t that up-to-date, though, having been written over a year ago and targeted at WordPress 2.7. Some helpful comments on that post offered a few refinements, and I added a few of my own to accommodate the particular quirks of this client’s site.

Since Mike Smullin (and D’Arcy Norman and Dave Dash before him) was so kind to share his work, I thought I should do the same. And so, I present my further refined SQL script for migrating Drupal data to a WordPress 3.0 site.

# DRUPAL-TO-WORDPRESS CONVERSION SCRIPT

# Changelog

# 07.29.2010 – Updated by Scott Anderson / Room 34 Creative Services http://blog.room34.com/archives/4530
# 02.06.2009 – Updated by Mike Smullin http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/
# 05.15.2007 – Updated by D’Arcy Norman http://www.darcynorman.net/2007/05/15/how-to-migrate-from-drupal-5-to-wordpress-2/
# 05.19.2006 – Created by Dave Dash http://spindrop.us/2006/05/19/migrating-from-drupal-47-to-wordpress/

# This assumes that WordPress and Drupal are in separate databases, named 'wordpress' and 'drupal'.
# If your database names differ, adjust these accordingly.

# Empty previous content from WordPress database.
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;

# If you're not bringing over multiple Drupal authors, comment out these lines and the other
# author-related queries near the bottom of the script.
# This assumes you're keeping the default admin user (user_id = 1) created during installation.
DELETE FROM wordpress.wp_users WHERE ID > 1;
DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;

# TAGS
# Using REPLACE prevents script from breaking if Drupal contains duplicate terms.
REPLACE INTO wordpress.wp_terms
    (term_id, `name`, slug, term_group)
    SELECT DISTINCT
        d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0
    FROM drupal.term_data d
    INNER JOIN drupal.term_hierarchy h
        USING(tid)
    INNER JOIN drupal.term_node n
        USING(tid)
    WHERE (1
         # This helps eliminate spam tags from import; uncomment if necessary.
         # AND LENGTH(d.name) < 50
    )
;

INSERT INTO wordpress.wp_term_taxonomy
    (term_id, taxonomy, description, parent)
    SELECT DISTINCT
        d.tid `term_id`,
        'post_tag' `taxonomy`,
        d.description `description`,
        h.parent `parent`
    FROM drupal.term_data d
    INNER JOIN drupal.term_hierarchy h
        USING(tid)
    INNER JOIN drupal.term_node n
        USING(tid)
    WHERE (1
         # This helps eliminate spam tags from import; uncomment if necessary.
         # AND LENGTH(d.name) < 50
    )
;

# POSTS
# Keeps private posts hidden.
INSERT INTO wordpress.wp_posts
    (id, post_author, post_date, post_content, post_title, post_excerpt,
    post_name, post_modified, post_type, `post_status`)
    SELECT DISTINCT
        n.nid `id`,
        n.uid `post_author`,
        FROM_UNIXTIME(n.created) `post_date`,
        r.body `post_content`,
        n.title `post_title`,
        r.teaser `post_excerpt`,
        IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
        FROM_UNIXTIME(n.changed) `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)
    # Add more Drupal content types below if applicable.
    WHERE n.type IN ('post', 'page', 'blog')
;

# Fix post type; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-17826
# Add more Drupal content types below if applicable.
UPDATE wordpress.wp_posts
    SET post_type = 'post'
    WHERE post_type IN ('blog')
;

# Set all pages to "pending".
# If you're keeping the same page structure from Drupal, comment out this query
# and the new page INSERT at the end of this script.
UPDATE wordpress.wp_posts SET post_status = 'pending' WHERE post_type = 'page';

# POST/TAG RELATIONSHIPS
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT nid, tid FROM drupal.term_node
;

# Update tag counts.
UPDATE wp_term_taxonomy tt
    SET `count` = (
        SELECT COUNT(tr.object_id)
        FROM wp_term_relationships tr
        WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# COMMENTS
# Keeps unapproved comments hidden.
# Incorporates change noted here: http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-32169
INSERT INTO wordpress.wp_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 drupal.comments
;

# Update comments count on wp_posts table.
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`
    )
;

# Fix images in post content; uncomment if you're moving files from "files" to "wp-content/uploads".
# UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');

# Fix taxonomy; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-27140
UPDATE IGNORE wordpress.wp_term_relationships, wordpress.wp_term_taxonomy
    SET wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_taxonomy_id
    WHERE wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_id
;

# OPTIONAL ADDITIONS — REMOVE ALL BELOW IF NOT APPLICABLE TO YOUR CONFIGURATION

# CATEGORIES
# These are NEW categories, not in Drupal. Add as many sets as needed.
INSERT IGNORE INTO wordpress.wp_terms (name, slug)
    VALUES
    ('First Category', 'first-category'),
    ('Second Category', 'second-category'),
    ('Third Category', 'third-category')
;

# Set category names to title case (in case term already exists [as a tag] in lowercase).
UPDATE wordpress.wp_terms SET name = 'First Category' WHERE name = 'first category';
UPDATE wordpress.wp_terms SET name = 'Second Category' WHERE name = 'second category';
UPDATE wordpress.wp_terms SET name = 'Third Category' WHERE name = 'third category';

# Add categories to taxonomy.
INSERT INTO wordpress.wp_term_taxonomy (term_id, taxonomy)
    VALUES
    ((SELECT term_id FROM wp_terms WHERE slug = 'first-category'), 'category'),
    ((SELECT term_id FROM wp_terms WHERE slug = 'second-category'), 'category'),
    ((SELECT term_id FROM wp_terms WHERE slug = 'third-category'), 'category')
;

# Auto-assign posts to category.
# You'll need to work out your own logic to determine strings/terms to match.
# Repeat this block as needed for each category you're creating.
INSERT IGNORE INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT p.ID AS object_id,
        (SELECT tt.term_taxonomy_id
        FROM wordpress.wp_term_taxonomy tt
        INNER JOIN wordpress.wp_terms t USING (term_id)
        WHERE t.slug = 'enter-category-slug-here'
        AND tt.taxonomy = 'category') AS term_taxonomy_id
    FROM wordpress.wp_posts p
    WHERE p.post_content LIKE '%enter string to match here%'
    OR p.ID IN (
        SELECT tr.object_id
        FROM wordpress.wp_term_taxonomy tt
        INNER JOIN wordpress.wp_terms t USING (term_id)
        INNER JOIN wordpress.wp_term_relationships tr USING (term_taxonomy_id)
        WHERE t.slug IN ('enter','terms','to','match','here')
        AND tt.taxonomy = 'post_tag'
    )
;

# Update category counts.
UPDATE wp_term_taxonomy tt
    SET `count` = (
        SELECT COUNT(tr.object_id)
        FROM wp_term_relationships tr
        WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# AUTHORS
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, u.mail, NULL, u.name, u.mail,
        FROM_UNIXTIME(created), '', 0, u.name
    FROM drupal.users u
    INNER JOIN drupal.users_roles r
        USING (uid)
    WHERE (1
        # Uncomment and enter any email addresses you want to exclude below.
        # AND u.mail NOT IN ('test@example.com')
    )
;

# Assign author permissions.
# Sets all authors to "author" by default; next section can selectively promote individual authors
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
    INNER JOIN drupal.users_roles r
        USING (uid)
    WHERE (1
        # Uncomment and enter any email addresses you want to exclude below.
        # AND u.mail NOT IN ('test@example.com')
    )
;
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
        u.uid, 'wp_user_level', '2'
    FROM drupal.users u
    INNER JOIN drupal.users_roles r
        USING (uid)
    WHERE (1
        # Uncomment and enter any email addresses you want to exclude below.
        # AND u.mail NOT IN ('test@example.com')
    )
;

# Change permissions for admins.
# Add any specific user IDs to IN list to make them administrators.
# User ID values are carried over from Drupal.
UPDATE wordpress.wp_usermeta
    SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
    WHERE user_id IN (1) AND meta_key = 'wp_capabilities'
;
UPDATE wordpress.wp_usermeta
    SET meta_value = '10'
    WHERE user_id IN (1) AND meta_key = 'wp_user_level'
;

# Reassign post authorship.
UPDATE wordpress.wp_posts
    SET post_author = NULL
    WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users)
;

# VIDEO – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_video table to store links to YouTube videos,
# this query will insert the video URLs at the end of all relevant posts.
# WordPress will automatically convert the video URLs to YouTube embed code.
UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_video v
    SET p.post_content = CONCAT_WS('\n',post_content,v.field_video_embed)
    WHERE p.ID = v.nid
;

# IMAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_image table to store images associated with posts,
# but not actually referenced in the content of the posts themselves, this query
# will insert the images at the top of the post.
# HTML/CSS NOTE: The code applies a "drupal_image" class to the image and places it inside a <div>
# with the "drupal_image_wrapper" class. Add CSS to your WordPress theme as appropriate to
# handle styling of these elements. The <img> tag as written assumes you'll be copying the
# Drupal "files" directory into the root level of WordPress, NOT placing it inside the
# "wp-content/uploads" directory. It also relies on a properly formatted <base href="" /> tag.
# Make changes as necessary before running this script!
UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_image i, drupal.files f
    SET p.post_content =
        CONCAT(
            CONCAT(
                '<div class="drupal_image_wrapper"><img src="files/',
                f.filename,
                '" class="drupal_image" /></div>'
            ),
            p.post_content
        )
    WHERE p.ID = i.nid
    AND i.field_image_fid = f.fid
    AND (
        f.filename LIKE '%.jpg'
        OR f.filename LIKE '%.jpeg'
        OR f.filename LIKE '%.png'
        OR f.filename LIKE '%.gif'
    )
;

# Fix post_name to remove paths.
# If applicable; Drupal allows paths (i.e. slashes) in the dst field, but this breaks
# WordPress URLs. If you have mod_rewrite turned on, stripping out the portion before
# the final slash will allow old site links to work properly, even if the path before
# the slash is different!
UPDATE wordpress.wp_posts
    SET post_name =
    REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;

# Miscellaneous clean-up.
# There may be some extraneous blank spaces in your Drupal posts; use these queries
# or other similar ones to strip out the undesirable tags.
UPDATE wordpress.wp_posts
    SET post_content = REPLACE(post_content,'<p> </p>','')
;
UPDATE wordpress.wp_posts
    SET post_content = REPLACE(post_content,'<p class="italic"> </p>','')
;

# NEW PAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# MUST COME LAST IN THE SCRIPT AFTER ALL OTHER QUERIES!
# If your site will contain new pages, you can set up the basic structure for them here.
# Once the import is complete, go into the WordPress admin and copy content from the Drupal
# pages (which are set to "pending" in a query above) into the appropriate new pages.
INSERT INTO wordpress.wp_posts
    (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,
    `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`,
    `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`,
    `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`,
    `post_mime_type`, `comment_count`)
    VALUES
    (1, NOW(), NOW(), 'Page content goes here, or leave this value empty.', 'Page Title',
    '', 'publish', 'closed', 'closed', '',
    'slug-goes-here', '', '', NOW(), NOW(),
    '', 0, 'http://full.url.to.page.goes.here', 1, 'page', '', 0)
;

A few notes and suggestions:

  • Use at your own risk. I’m just sharing the code I used. In fact, it’s not even exactly the code I used, since I had to generalize a few things for mass consumption. I haven’t even tested this modified version. I make no guarantees as to its reliability or applicability to your particular implementation.
  • Download the script below; don’t just copy-paste the code from the block above. I had to apply some HTML formatting to make it look right, and this might result in bad characters that would break the script if run as-is above.
  • Always work with a test copy of your database before altering live site content. In theory this script can be run over and over again cleanly, but it will erase whatever data was already in the WordPress database, and there will be no way to get it back unless you have a backup.
  • Read through the code carefully, especially all comments before running the script. Most likely you will need to make some changes to the code.
  • Good luck! If you find bugs or develop further improvements, please comment below!

Download the Script

Download zip file... Drupal-to-WordPress Migration Script
drupal-to-wordpress.sql.zip • 4.8 KB

Why oh why won’t CakePHP store my tinyint(1) data?

CakePHPOK, I actually know the answer to the question posed in the subject line. Despite the palpable suspense, I am sure 99.9% of my audience can tune out now. That leaves the remaining 0.001 readers to dive into this problem with me.

I’m doing a lot of CakePHP development these days, and I’m loving it. (What? The McDonald’s legal team is on its way over here? Damn. Fine, McDonalds. Have it your way. What, Burger King too? I guess I better make a run for the border. OK, joke’s over.)

Of course, plunging into a sea of someone else’s code is always fraught with a little peril, and today I found some. Here’s the scenario:

MySQL is a pretty cool database. I’m very loyal to it. But there are some things it should do, but it just doesn’t. One of those things is support a boolean data type. So, we make do. A common way to make do, and the way preferred by CakePHP, is to use a tinyint(1) field and just store 0 or 1 in it. In fact, CakePHP loves this approach so much that whenever it sees a data field that’s a tinyint(1), it “automagically” refuses to accept any values for that field other than 0 or 1.

That’s super-dee-duper. If that’s what you want. But I have a data table, my users table, as it happens, and in that table I used to have a field called admin, a boolean value. Either the user’s an admin or not. Great. But I decided to upgrade this to allow more access levels than just 0 or 1 (and while I was at it, changing the field name, shockingly, to access_level). I wanted to be able to support up to ten levels. Well, great! A tinyint(1) will nicely store values from 0 through 9, so I’m set!

Except… it didn’t work. Every time I tried to save a value greater than 1, I’d find that it had saved 1 as the value. I could change it to 0 just fine, but anything else became 1.

I checked the documentation and found ample evidence for the “automagic” behavior, so I figured there were a couple of possible changes I could make that would fix the problem: I could change it to a tinyint(2), or I could change it to a char(1). Since I decided I’d rather (in theory) allow letters in the field than double-digit numbers, I went with char(1).

Only it still didn’t work.

I did some more research, found that I seemed to be on the right track, but I was still confounded.

Then it occurred to me. I knew I had seen a cache directory somewhere in the labyrinth of directories and subdirectories and sub-sub-sub-sub-subdirectories in the CakePHP package. And I also knew that a framework as developed as CakePHP probably wouldn’t hit up the database for schema information constantly, so maybe… just maybe… it was cached.

I burrowed down in my application to the app/tmp/cache/models directory, and sure enough… there’s a cache file for each data table, with the schema in a serialized form. Well, it’s a cache, right? Nothin’ to lose. Trash can, here we come! I refreshed my page, and voilà! Success!

So… word to the wise (or, not so wise, like me… otherwise you’d probably already know):

  1. CakePHP will only store 0 or 1 in a tinyint(1) field. Period.
  2. If you change the schema for any of your data tables, and CakePHP acts like you didn’t… dump that cache!

We now return you to our regular, slightly less geeky programming.