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.

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

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

Unknown collation: 'utf8mb4_unicode_ci'

Sometimes it will come up as:

Unknown collation: 'utf8mb4_unicode_520_ci'

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

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

Format-specific options during a phpMyAdmin database export

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

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

Other possible solutions include:

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

Side-effects of a character encoding downgrade

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

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

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.