Import/Export issues between MySQL 5.1 and 5.5+

Posted on August 5, 2014

There have been many advances in recent history that make a WordPress developer’s life easier. WordPress itself is already pretty easy as it’s very tolerant of a myriad of hosting environments. To make things easier for local setup, there’s a fantastic product called DesktopServer by ServerPress which will quickly and easily set up a multitude of local WordPress sandboxes to hack on. DesktopServer currently ships with MySQL 5.1 which is A-OK because WordPress requires version 5.0 or greater.

When setting up a new development environment, we typically use WP Migrate DB Pro (HIGHLY RECOMMENDED!) to download the latest database data from staging or production into our local sandboxes. Other ways work just as well, like exporting from phpMyAdmin (or native mysqldump) and then using interconnect/it Search and Replace. WP Migrate DB Pro is just so easy because it does everything in one step.

However, a problem reared its head recently while exporting from WPEngine (which currently uses MySQL 5.6) to DesktopServer. There are sometimes a COMMENT added to a KEY (index) in the CREATE TABLE statement:

CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`) COMMENT '(null)',
  KEY `meta_key` (`meta_key`) COMMENT '(null)'
) ENGINE=InnoDB AUTO_INCREMENT=175022 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

This is apparently a MySQL 5.5 and up feature. MySQL 5.1 doesn’t know how to ignore it, so we need to get rid of it. I haven’t figured out how to tell mysqldump to exclude them, so we’ll do it by other means…

Killing the KEY COMMENT(s)

We can get rid of it in one of two ways:

1. Use sed on the CLI to strip COMMENT

cat site-export.sql | sed "s/ COMMENT '(null)'//" > no-comment-export.sql

Replace site-export.sql with the name (and path) of your export file. Then import the new no-comment-export.sql file into your local MySQL and do any additional search & replaces as needed.

2. Add a COMMENT filter to WPMDB Pro

To an active plugin or your theme’s functions.php file (on staging or wherever the data is originating from) add the following filter:

//for MySQL 5.5 -> 5.1 COMMENT fix via wp-migrate-db-pro
add_filter( 'wpmdb_create_table_query', 'mysql_compat_51', 10, 2 );

function mysql_compat_51( $create_table, $table ) {
	return str_replace( " COMMENT '(null)'", '', $create_table );
}

Note that this only strips out the ‘null’ KEY COMMENTs. If any of your tables have actual comments on any of the keys, they would still be included and potentially cause problems for MySQL.