So I had an encounter that I’ve never seen before while deploying a new website over to production at Bluehost.  Using Backup Buddy, I received something like this:

• Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_comments’ doesn’t exist` Errno: `1146`.Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_comments’ doesn’t exist` Errno: `1146`.Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_comments’ doesn’t exist` Errno: `1146`.Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_links’ doesn’t exist` Errno: `1146`. Click toview error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_links’ doesn’t exist` Errno: `1146`. Click toview error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
• Error #9010: Unable to import SQL query. Error: `Table ‘webvolut_ss_dbname2cf.wp_amcm_options’ doesn’t exist` Errno: `1146`. Click toview error details in the Knowledge Base
• Additional 9010 errors were encountered but have not been displayed to avoid flooding the screen. All 9010 errors are logged to `/home6/xxxxxx/public_html/yyyyy/importbuddy/mysql_9010_log-nk1p6e8p5umf.txt` if possible.

This is a MySQL incompatibility specifically on the collation.  I’ve just recently upgraded MAMP Pro to version 4 and by default it has setup MySQL to use collation utf8mb4_unicode_520_ci which doesn’t exist in MySQL 5.5.  And if you use many of the cheap shared hosting solutions such as Bluehost, which is what I use, you may run into this issue when migrating a project from your local machine to production.  They seem to be a bit behind in keeping their technologies up-to-date.

My setup locally is using MAMP Pro 4.0.3 with MySQL 5.6.28.  Bluehost however is running MySQL 5.5.51.  There doesn’t appear to be an option to upgrade MySQL within cPanel like there is for PHP.  I think its because hosting providers such as Bluehost can’t just upgrade MySQL on an account basis and so it would have to be done to the entire server which would effect other websites shared on said server. 

The solution that works for me is to change the collation of all the WordPress tables and columns to from utf8mb4_unicode_520_ci to utf8mb4_unicode_ci.  Fortunately, I didn’t run into any issues of lost or corrupted data.  It worked perfectly.  But the project that was having this issue was in English and didn’t have any special characters beyond what you see on a keyboard.  If you have special characters and non-English characters, you may be out of luck.  From what I’ve seen, there’s no fool-proof backwards compatibility of changing a collation that exists in 5.6 such as utf8mb4_unicode_520_ci to a collation that exists in 5.5 without the lost of data.

 I did learn an important lesson through all this.  Check your local stack against production stack and the smallest difference (in this case a minor release) in versions can make a big different!