USE THIS ARTICLE WITH EXTREME CAUTION AND MAKE BACKUPS OF YOUR DATABASE BEFORE ATTEMPTING ANYTHING WRITTEN HERE. USE IT AT YOUR OWN RISK
As the error occurs due to the version difference i.e. MySQL 4.7 and MySQL 8 ,
MySQL 8.0 to MySQL 4.7
In the below example you go from MySQL8 to Mysql 4.7 Note that you MAY loose some data if you do this, so its not recommended:
we have to edit the database backup file in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“. Also, we have to change the below code:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
The new code should be:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Now we have to save the file and restore the database.
For Linux CLI (command line) users (you'll need ssh access), use the sed command to replace text in files directly.
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql
Change the backup.sql to the name of your database exported filename. Once done restore the database.
MySQL 4.7 to MySQL 8.0
But what if you manually transfer your data from a MySQL 4.7 to a 8.0 server. This should go without any data loss, but needs some changes as well if the utf8 and utf8_general_ci is not available as on cpanel servers when installed with MySQL 8.0 you do the above exactly the other way around:
we have to edit the database backup file in text editor and replace “utf8mb4_general_ci” with “utf8mb4_0900_ai_ci and “CHARSET=utf8” with “CHARSET=utf8mb4“. Also, we have to change the below code:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
The new code should be:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
For Linux CLI (command line) users (you'll need ssh access), use the sed command to replace text in files directly.
sed -i 's/utf8_general_ci/utf8mb4_0900_ai_ci/g' backup.sql
sed -i 's/CHARSET=utf8/CHARSET=utf8mb4/g' backup.sql
Change the backup.sql to the name of your database exported filename. Once done restore the database.
