Until this week I never really cared about different storage engines supported by MySQL. I just assumed that the default is okay for my small-scale applications. But then this happend:

WordPress database error Can't create table 'blogs.wp_site_categories_relationships' (errno: 150) for query CREATE TABLE wp_site_categories_relationships (
	blog_id BIGINT NOT NULL,
	category_id BIGINT NOT NULL,
	PRIMARY KEY id (blog_id, category_id),
	INDEX blog_ind (blog_id),
	INDEX category_ind (category_id),
	FOREIGN KEY (blog_id)
		REFERENCES wp_blogs(blog_id),
	FOREIGN KEY (category_id)
		REFERENCES wp_site_categories(id)
	)
DEFAULT COLLATE utf8_general_ci made by require('wp-admin/plugins.php'), activate_plugin, do_action('activate_network-summary/network-summary.php'), call_user_func_array, Network_Summary->activate, Site_Category_Repository->create_table, dbDelta

This error causes me a lot of headaches and quite some time to debug. The problem was, that it only occurred on certain environments (most notably on the production) while not occurring on my local environment, thus being hard to debug.

The problem

The first hint was the error number, which lead me to the following documentation:

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

I could also verify that it was not a problem of the WordPress function dbdelta as the same error was returned when executed the statement manually.

More information was obtained from Stack Overflow:

• The two tables must be ENGINE=InnoDB. (can be others: ENGINE=MyISAM works too)
• The two tables must have the same charset.
• The PK column(s) in the parent table and the FK column(s) must be the same data type.
• The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
• If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.
• And the child table cannot be a temporary table.

But this is not 100% accurate. Especially the first point needs some clarification. It is important to understand that only InnoDB supports foreign keys anyhow! So despite being valid statements regardless the engine, the foreign keys do not get created or validated in any other engine. For more on the different storage engines, check the manual.

This leads to the following behavior:

• If the referencing table (the one with the foreign key) uses MyISAM, the above mentioned conditions do not matter as foreign keys get ignored anyhow – even if the referenced table uses InnoDB. But obviously no foreign keys are actually created.
• If the referencing table uses InnoDB and the referenced table uses MyISAM the statement will fail and return the error 150.
• If the referencing table uses InnoDB and the referenced table uses InnoDB the statement succeeds and the foreign keys will be created.

I was not aware of that and it has some implications for WordPress plugin development if you create tables and use foreign keys on existing WordPress tables. Mainly because you can not safely assume which storage engine is used by WordPress. Mainly because different MySQL versions have different default engines or the default engine can be overwritten by any database administrator. MySQL < 5.5 uses MyISAM and MySQL >= 5.5 uses InnoDB. WordPress uses the MySQL default as far as I am aware of. In theory this problem can also occur if you only have references between tables of the same plugin (e.g. the default engine changed between to updates of your plugin) but is less likely to happen.

More concrete I had the problem that the default engine set to InnoDB (despite not being 5.5 or more recent) but the WordPress tables were all MyISAM. Thus when I tried to activate the plugin it resulted in the referenced table using MyISAM and my referencing table using InnoDB.

The solution

Concluding it might be a smart idea to check for the engine when creating tables with foreign keys (or just in general, to have consistent settings for all tables). I added the following lines to my plugin code:

$engine = $wpdb->get_row( "SELECT ENGINE FROM information_schema.TABLES where TABLE_NAME = '$wpdb->blogs'", ARRAY_A );
$engine = $engine['ENGINE'];

The variable $engine can then be used in your CREATE TABLE statements and in the statements to migrate your legacy tables as well if you have such, e.g.:

ALTER TABLE $wpdb->site_categories_relationships ENGINE=$engine;

If you want to check out the full source code where I added this behavior, feel free to browse the code on trac.