There are times when you migrate a website to another host or you upgrade a software such as WordPress and you start seeing MySQL errors in your log such as:
WordPress database error Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
This happens because if a collation is not defined when you import a database, MySQL will assume the default collation for the CHARSET which is specified. Some plugins compound the problem when they install tables which don’t have defined charset and your database becomes a mixed bag of different charsets. To fix this problem, you need to convert all database tables to a single charset such as UTF8 (utf8_general_ci), which is usually the right choice.
I have seen people give ungodly complicated instructions and voodoo style SQL Queries to accomplish this simple task. There is really not much to it, so listen up.
First of all, export your current database and make two copies of it. Keep one as original and open the other one with your favorite text editor. I use Notepad++ and it handles SQL files beautifully with no lag. Regular Windows Notepad will take forever to open large SQL files so I don’t recommend it.
Once you have opened your copy of the database backup, you need to perform a search and replace for the charset you are trying to change. Let’s say that you want to convert all tables and columns from latin1 to UTF8. In the search and replace box:
Search for: CHARSET=latin1
Replace with: CHARSET=utf8
Do the replacing individually rather than doing it all at once so you can see what you’re replacing. Once you’ve replaced all the old charsets, save the file. Now go to phpMyadmin or however you access your database and drop all the tables. Then click on import and import your freshly edited SQL file into your database.
If you look at your tables now, you’ll see that you’ve changed the charsets for everything and they all should be the same.
The last thing you need to do is to change the database charset as well. Run the following SQL Query and it will convert database charset and you are done:)
ALTER DATABASE YOURDATABASENAME CHARACTER SET utf8;
Note: Replace “YOURDATABASENAME” with your actual database name including its prefix. (e.g. WP_MyDatabase) and set whatever charset you want – in this case it is UTF8.
If you have any questions or comments, leave it below:)