I’ve been working on setting up a new set of WP sites on my servers and ran into a funky problem I wasn’t able to solve (initially) or find any other documented solutions out there.

I’m using a plugin called ‘WordPress MU Domain Mapping‘ and I’d followed the install instructions.  However, when I would visit the ‘Domain Mapping Configuration’ page in my Setting tab I’d always see a message at the top of the page ‘Domain mapping database table created.’.  This message appeared every time I went to that page.

Now some googling did find other folks who were having that message always appear, and it seemed that it was because the table wasn’t actually being created.  Things such as permissions and access were pointed out and I’d checked all that and every thing seemed in order.  I could even create a new table using phpMyAdmin with the same user WP used, so I knew the user was setup correctly.

Checking out the tables with phpMyAdmin, I could see that the ‘domain_mapping_logins’ table had been created, but the ‘domain_mapping’ table didn’t exist, and this was obviously needed and the root of the error.  I turned on WP debugging, hoping I’d see an error message about the table not being created, but no such luck.

In the end, I solved it by exporting a working table from another WP install I had, and then imported it via phpMyAdmin. This created the table for me, and all was well with the world!

I’d like to know why the plugin was unable to create the required table, but that’s a project for another day I think.  The table should have been created by the following code:

if ( $wpdb->get_var("SHOW TABLES LIKE '{$wpdb->dmtable}'") != $wpdb->dmtable ) {
			$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$wpdb->dmtable}` (
				`id` bigint(20) NOT NULL auto_increment,
				`blog_id` bigint(20) NOT NULL,
				`domain` varchar(255) NOT NULL,
				`active` tinyint(4) default '1',
				PRIMARY KEY  (`id`),
				KEY `blog_id` (`blog_id`,`domain`,`active`)
			);" );
			$created = 1;
		}

Which is similar, but with obvious differences from the code that creates the ‘domain_mapping_logins’ table:

if ( $wpdb->get_var("SHOW TABLES LIKE '{$wpdb->dmtablelogins}'") != $wpdb->dmtablelogins ) {
			$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$wpdb->dmtablelogins}` (
				`id` varchar(32) NOT NULL,
				`user_id` bigint(20) NOT NULL,
				`blog_id` bigint(20) NOT NULL,
				`t` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
				PRIMARY KEY  (`id`)
			);" );
			$created = 1;
		}

Update: Figured out what was going on:  The version of mysql running on my webserver is 32bit and I dug into the actual error.  It turns out that the ‘domain’ field exceeded the maximum length of a key (1,000 bytes).  This is because UTF8 is used as the default encoding, which allows basically 9 bytes per character, so the domain can be 2,295 bytes long.  To remedy this, I added the following to the SQL statement:

ENGINE=InnoDB DEFAULT CHARSET=latin1;

Which keeps the field length under the 1,000 byte limit and allows the field to be created.