WordPress – Script(PHP) to convert MySQL collation from utf8mb4 to utf8

If your MYSQL database import fails with Unknown collation: ‘utf8mb4_unicode_ci’ after the latest WordPress update 4.2.2, chances are that you are running a MYSQL server version 5.5.2 or below. With the most recent update, WordPress changed their encoding standard from utf8 to utf8mb4, which is only supported starting with MYSQL server version 5.5.3.

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.

You have to delete this PHP file what you made after fixing your problem.

I highly recommend if you are running WordPress that you upgrade your MySQL server to support utf8mb4 for security. WordPress made this change to fix a security vulnerability introduced by The Trojan Emoji.

<!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 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>";
	}
?>

Reference #1: http://ben.lobaugh.net/blog/201740/script-to-convert-mysql-collation-from-utf8mb4-to-utf8
Reference #2: http://stackoverflow.com/a/29939906

Views (650)

Share on Facebook0Share on Google+0Share on LinkedIn0Tweet about this on TwitterEmail this to someonePrint this page
[Total: 1    Average: 5/5]

Leave a Reply

Your email address will not be published. Required fields are marked *

16 − fourteen =