WooCommerce Foreign Key Errors and MySQL Engines

Here at Sterner Stuff, we don’t like throwing away existing websites and content. We call that stuff “equity”, and no one wants to throw away equity. When possible, we’ll repurpose as much of a WordPress site as possible when implementing a new design, features, etc. So while it may seem like a brand new site, we’ve kept valuable content.

But sometimes that means old data and new data don’t play nicely. This recently became an issue for us running some WooCommerce updates on a client site. While deploying updates, we got this error:

WordPress database error Cannot add foreign key constraint for query ALTER TABLE wp_wc_download_log ADD FOREIGN KEY (permission_id) REFERENCES wp_woocommerce_downloadable_product_permissions(permission_id) ON DELETE CASCADE made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Install::check_version, WC_Install::install, WC_Install::create_tablesCode language: PHP (php)

What we’re seeing here is that part of WooCommerce’s installation and table creation logic is failing (which, thanks to WordPress’s magic dbDelta functionality, safely runs every time WooCommerce updates). MySQL is unable to add a foreign key to one of our tables.

A quick Google turned up this GitHub issue, which suggested the problem could be that we’re running two different MySQL engines across our tables. And that was the case. And we fixed it. Here’s how.

How did we get into this mess?

First, how did we even end up with different engines on different tables?

Prior to MySQL 5.5, the default database engine was MyISAM. After 5.5, that become InnoDB. These are the mismatched engines. If you originally installed your WordPress site with MySQL pre-5.5, you would get MyISAM tables, specifically for your WordPress core tables and any tables created by plugins you installed, like WooCommerce.

Then let’s say you upgraded MySQL to version 5.5 or greater, either on your existing hosting or as part of a hosting migration (which we’re often doing with new clients here at Sterner Stuff). The default engine is now InnoDB, but your existing tables won’t change. They’re still MyISAM.

Finally, let’s say you update WooCommerce, and that update includes some new tables. WooCommerce creates a lot of new tables in the WordPress database for better performance, and continues to add and alter them over time. The new tables added after your MySQL update will be InnoDB. And now you have tables with mismatched engines.

Fixing it

To fix the above error and get our WooCommerce migrations to run properly, we need to convert our old tables to InnoDB and then re-run the migrations.

First, take a backup of your database.

Convert MySQL engines

Next up, let’s confirm that mismatched tables are even your problem. Run the following MySQL query via phpMyAdmin, TablePlus, or your MySQL client of choice.

SHOW TABLE STATUS;

This will show you all your tables and the engines they use. If you find mixed table engines, that’s probably the culprit and should be resolved.

Assuming you just want to move everything to InnoDB (MySQL’s current default, remember!), you can run this command (be sure to swap out the database name):

SET @DATABASE_NAME = 'your_database_name';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;Code language: SQL (Structured Query Language) (sql)

That’ll get you a list of commands to convert all your tables to InnoDB. If you still haven’t, take a backup. Then copy, paste, and run that output to migrate engines. Now all your tables are on the same engine, which you can confirm by running SHOW TABLE STATUS; again.

Re-run WooCommerce’s migrations

The next time you update WooCommerce, it’ll automatically re-run all its installation logic, including configuration of the foreign keys that failed in the error we’re fixing. But you can force-run this logic immediately to verify your fix if you prefer.

You can do that with a little PHP. Either create a PHP script to run, or use a WP-CLI shell session (wp shell). Run the following:

WC_Install::install();Code language: PHP (php)

The output won’t be very exciting (NULL), but you should now be able to inspect the wp_wc_download_log table and see that the permission_id column now has a foreign key configured.

All fixed.