- Joined
- Sep 3, 2014
- Messages
- 6,138
- Likes
- 12,831
- Degree
- 9
Dealt with this all day and figured I should mention it and provide a solution in case one of you runs into it. I like to work on live staging environments so friends, clients, etc. can see what I'm doing and comment.
Long story short, I'm using one of these Wordpress themes that asks you to import demo content, but the little shared server I normally use wouldn't cut it, so I had to import on a VPS and then move back to the little server. And the problem occurred.
The Problem
When migrating Wordpress around, you may encounter this error, thrown by PHPmyAdmin, BackupBuddy, UpdraftPlus, Duplicator, and the like. I tried everything until I narrowed down the issue. The error is some variation on:
The Reason
This is happening due to a change in Wordpress 4.6 that will run the Unicode Collation Algorithm that can encode in less bytes here and there, if it detects you're using MySQL 5.6 or higher. Out of the three servers I'm juggling here, only one does, and it was the one I installed on. The others use MySQL 5.5 and will not accept an import no matter what with this utf8mb4 setting.
The Solution
Once I figured out the issue, naturally I googled it and there's a ton of bad information out there about editing the .sql database with a search and replace in a text editor to tell it to use the old style. Yeah, it might import, but then what? A lot of broken text depending on what you use, what the plugins expected, etc.
The real solution is to not use any site cloning / duplication / migration plugins and to do it the old fashioned way. Copy the files and reupload them, reconfigure the wp-config.php to point to the right database... and then use PHPmyAdmin to do a manual export.
However, as you export, do the following:
Long story short, I'm using one of these Wordpress themes that asks you to import demo content, but the little shared server I normally use wouldn't cut it, so I had to import on a VPS and then move back to the little server. And the problem occurred.
The Problem
When migrating Wordpress around, you may encounter this error, thrown by PHPmyAdmin, BackupBuddy, UpdraftPlus, Duplicator, and the like. I tried everything until I narrowed down the issue. The error is some variation on:
**ERROR** database error write Unknown collation: ‘utf8mb4_unicode_520_ci’
It may not include "520", which refers to a new encoding algorithm available for MySQL.
The Reason
This is happening due to a change in Wordpress 4.6 that will run the Unicode Collation Algorithm that can encode in less bytes here and there, if it detects you're using MySQL 5.6 or higher. Out of the three servers I'm juggling here, only one does, and it was the one I installed on. The others use MySQL 5.5 and will not accept an import no matter what with this utf8mb4 setting.
The Solution
Once I figured out the issue, naturally I googled it and there's a ton of bad information out there about editing the .sql database with a search and replace in a text editor to tell it to use the old style. Yeah, it might import, but then what? A lot of broken text depending on what you use, what the plugins expected, etc.
The real solution is to not use any site cloning / duplication / migration plugins and to do it the old fashioned way. Copy the files and reupload them, reconfigure the wp-config.php to point to the right database... and then use PHPmyAdmin to do a manual export.
However, as you export, do the following:
- Select CUSTOM as your export method. This will open up a ton of options.
- Find the list item that says Database system or older MySQL server to maximize output compatibility with:
- From that item's dropdown menu, choose MYSQL40 as the compatibility option.
- Export it and import it to the new database without a problem.
