Login

open the sql file on Notepad++ and `ctrl + H.`
Then you put "`utf8mb4`" on search and "`utf8`" on replace.
The issue will be fixed then.

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

?>

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][1] (it's in the same page as this)

[1]:

[To see links please register here]

If you are using PHP7, then you would need a PHP script like this one in order to migrate your collation:

```
<!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'];

$mysqli = new mysqli('db',$dbuser,$dbpassword);
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$mysqli -> select_db($dbname);
$result= $mysqli->query('show tables');
while($tables = $result->fetch_array) {
foreach ($tables as $key => $value) {
$mysqli->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

polianthes791641

antibody179968

Proanabellajfpbsxw

osricomwobn

cottager483883