WordPress database error: Specified key was too long

This has been resolved in MM 2.2.7+

Download the most recent version and learn how to manually upgrade

Question

I'm receiving this database error WordPress database error Specified key was too long; max key length is 767 bytes for query...

Short Explanation of Problem

This issue is a result of the collation used on your database tables which is set to utf8mb4_unicode_ci or something similar. The problem with this is that this particular collation style restricts the length of keys on the database tables resulting in certain MemberMouse tables failing to be installed during plugin activation. 

If you review your database error logs you will see these errors reflected like this:

WordPress database error: [Specified key was too long; max key length is 1000 bytes]

As you can imagine, when tables are missing from the database this will manifest in different ways depending on which tables are missing. In this case, you're experiencing a problem creating a product which is specifically the result of the mm_affiliate_providers table not being installed. 

However, when the utf8mb4_unicode_ci collation is used the following is the complete list of tables that won't be installed:

mm_affiliate_providers

mm_affiliate_rebill_commissions

mm_email_service_providers

mm_log_events

mm_social_login_linked_profiles

mm_stripe_customer_links

Solution Steps (Follow these if you're just getting started with MemberMouse)

If you're just getting started and don't have any data in MemberMouse, or very little data, the easiest and safest way to address this is to do the following: 

1. Follow the steps to Uninstall MemberMouse.

2. Via phpMyAdmin, navigate to the database that's being used for your WordPress install. This is the database table listed in your wp-config.php file.

3. Set the default collation of the database to utf8_unicode_ci

4. Perform a fresh install of the MemberMouse plugin

Following this all of the MemberMouse tables will have been installed.

Advanced Explanation of Problem

This problem is caused by a perfect storm of conditions that come together to create a hosting environment that is incompatible with current versions of MemberMouse. These conditions are very technical and low-level, but what follows is an explanation of what is happening along with potential solutions.

The database that you are using (and that MemberMouse is built to be compatible with) is the MySQL database. The MySQL database can be configured with different “storage engines” which control how data is stored and retrieved. Each engine has certain advantages and tradeoffs, so the best engine to use in a particular environment is highly subjective. The storage engine that your database is using is InnoDB. One of the tradeoffs of the InnoDB engine is that it has a maximum index length of 767 bytes, which is normally not a problem because in most instances indexes are not created to be that large (an index is a database object used to speed up queries).

The MySQL database also has a setting known as “collation”. Collation essentially defines what type of characters the database can store. Characters outside of the expected character set are usually rejected, so its generally best to use a collation that is capable of storing all of the characters that you can expect your application to produce. We usually recommend a collation known as utf8_unicode_ci, which is designed to store UTF8 characters, which covers most of the international languages you would normally expect to encounter. utf8_unicode_ci allows 3 bytes to be used to store characters, which means that indexes can be used on fields with up to 255 characters (255 * 3 = 765). Since normally the maximum size of an indexed field in MemberMouse is 255 characters, this generally works well.

Your database is configured to use a collation known as utf8mb4_general_ci, which allows up to 4 bytes to be used to store characters. My understanding is that utf8mb4_general_ci allows for all of the characters stored by utf8_unicode_ci, but additionally emojis, special characters used in asian languages, etc. The problem comes in when using utf8mb4_general_ci with the InnoDB engine. A 255 character database field now requires 4 bytes per character to create an index. (255 * 4 = 1020 bytes). The maximum allowed by InnoDB is 767, so this causes a SQL error, like what you are seeing.

MySQL during normal operation will issue a warning when the maximum InnoDB index is exceeded, and then adjust the index to be 767 bytes, however there is a mode called “strict mode” that MySQL can run in which will treat all errors/misconfigurations as hard errors and refuse to process the SQL. 

So the combination of the utf8mb4_general_ci collation, the InnoDB storage engine, and MySQL running in strict mode has created an environment where MemberMouse is unable to install its tables. 

Advanced Potential Solutions 

(Follow these if you've already been using MemberMouse and have data that you don't want to lose)

1. Turn off Strict Mode. Doing this and then restarting the MySQL database followed by a deactivation/reactivation of MemberMouse should allow the MemberMouse tables to be created with your current collation, and will silently adjust the index sizes. This is probably the easiest solution.

2. Change the default collation on your database to utf8_unicode_ci, and then deactivate/reactive MemberMouse. You will probably need to engage a developer or your hosting provider to do this. The default collation setting for the database only affects newly created tables, so your existing tables will keep their current collations, which could cause problems. The safest way to deal with this issue would be to export all of your tables and data without collation settings, drop the existing tables in the database, set the default collation on the database to utf8_unicode_ci, and then reimport your data. This could potentially be an onerous task, but we have no data on the effects of mixing the collations of different tables, so just setting the default collation to utf8_unicode_ci and letting MemberMouse recreate the missing tables may not be enough. 

3. Change the storage engine used by MySQL. This is a non-trivial task and could cause issues with the existing data; this avenue should only be pursued if you have access to someone very familiar with MySQL and the various storage engines and the intricacies of doing this type of conversion.

The path of least resistance is option #1, but if you are unwilling or unable to turn off strict mode, any of these solutions should work. You will want to back up your current database and put the site into maintenance mode prior to performing any of these actions.