Menu
The technique in this post worked for me
1) Click the "Export" tab for the database
2) Click the "Custom" radio button
3) Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
4) Scroll to the bottom and click "GO".
I'm not certain if doing this causes any data loss, however in the one time I've tried it I did not notice any. Neither did anyone who responded in the forums linked to above.
Edit 8/12/16 - I believe exporting a database in this way causes me to lose data saved in Black Studio TinyMCE Visual Editor widgets, though I haven't ran multiple tests to confirm.
If you have already exported a .sql file, the best thing to do is to Find and Replace the following if you have them in your file:
utf8mb4_0900_ai_ci to utf8_unicode_ci
utf8mb4 to utf8
utf8_unicode_520_ci to utf8_unicode_ci
It will replace utf8mb4_unicode_ci to utf8_unicode_ci. Now you go to your phpMyAdmin cPanel and set the DB collation to utf8_unicode_ci through Operations > Collation.
If you are exporting to a .sql, it's better to change the format on how you're exporting the file. Check out Evster's anwer (it's in the same page as this)
I had the same issue as all of our servers run older versions of MySQL. This can be solved by running a PHP script. Save this code to a file and run it entering the database name, user and password and it'll change the collation from utf8mb4/utf8mb4_unicode_ci to utf8/utf8_general_ci
<!DOCTYPE html>
<html>
<head>
<title>DB-Convert</title>
<style>
body { font-family:"Courier New", Courier, monospace; }
</style>
</head>
<body>
<h1>Convert your Database to utf8_general_ci!</h1>
<form action="db-convert.php" method="post">
dbname: <input type="text" name="dbname"><br>
dbuser: <input type="text" name="dbuser"><br>
dbpass: <input type="text" name="dbpassword"><br>
<input type="submit">
</form>
</body>
</html>
<?php
if ($_POST) {
$dbname = $_POST['dbname'];
$dbuser = $_POST['dbuser'];
$dbpassword = $_POST['dbpassword'];
$con = mysql_connect('localhost',$dbuser,$dbpassword);
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db($dbname);
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
}}
echo "<script>alert('The collation of your database has been successfully changed!');</script>";
}
?>
i use this in linux :
sed -i 's/utf8mb4/utf8/g' your_file.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_0900_ai_ci/utf8_general_ci/g' your_file.sql
then restore your_file.sql
mysql -u yourdBUser -p yourdBPasswd yourdB < your_file.sql
Wordpress 4.2 introduced support for "utf8mb4" character encoding for security reasons, but only MySQL 5.5.3 and greater support it. The way the installer (and updater) handles this is that it checks your MySQL version and your database will be upgraded to utfmb4 only if it's supported.
This sounds great in theory but the problem (as you've discovered) is when you are migrating databases from a MySQL server that supports utf8mb4 to one that doesn't. While the other way around should work, it's basically a one-way operation.
As pointed out by Evster you might have success using PHPMYAdmin's "Export" feature. Use "Export Method: Custom" and for the "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".
For a command line export using mysqldump. Have a look at the flag:
$ mysqldump --compatible=mysql4
Note: If there are any 4-byte characters in the database they will be corrupted.
Lastly, for anyone using the popular WP Migrate DB PRO plugin, a user in this Wordpress.org thread reports that the migration is always handled properly but I wasn't able to find anything official.
The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL
At this time, there doesn't appear to be a way to opt out of the database update. So if you are using a workflow where you are migrating a site from a server or localhost with MySQL > 5.5.3 to one that uses an older MySQL version you might be out of luck.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With