WordPress database error illegal mix of collations can be resolved with this quick fix by our team of experts. 

At Bobcares, we offer solutions for every query, big and small, as a part of our WordPress Support Service.

Let’s take a look at how our Support Team is ready to help customers resolve WordPress database error: illegal mix of collations.

How to fix WordPress Database Error: Illegal Mix of Collations

Some of our customers have been running into an error like the one below when they attempt to migrate a website to another host or upgrade software like WordPress:

WordPress database error Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

After careful analysis, our Support Techs have come to the conclusion that the error is due to a collation not being defined while importing a database. This results in MySQL assuming the default collation for CHARSET.

Furthermore, certain plugins addon to the issue when they install tables without defined CHARSET resulting in a database with a mixed bag of different CHARSETs. Fortunately, the Support Techs have also come up with a solution for the error. It involves converting all database tables to a single CHARSET like UTF8 (utf8_general_ci).

How to convert database tables to a single CHARSET

1. Initially, we have to export the current database and make 2 backup copies of it.
2. Then, open one of the database backup copies and perform a search and replace function for the specific CHARSET we want to change. For instance, if we want to convert all tables and columns from latin8 to UTF8 as seen below:

   Search for: CHARSET=latin8
   Replace with: CHARSET=utf8

   Our Support Techs recommend replacing the CHARSETs individually rather than all at once. After replacing the CHARSETs, we have to save the file.
3. Then it is time to head to phpMyAdmin and drop all the tables. After that, we have to click on Import and import the recently edited SQL file into the database.
4. Next, we have to change the database CHARSET as well. This involves running the following SQL query:

   ALTER DATABASE YOURDATABASENAME CHARACTER SET utf8;

   Here, we have to replace YOURDATABASENAME with the database name including its prefix, and utf8 with the CHARSET we prefer.

Alternatively, we can also get the job done via phpMyAdmin with the following steps:

1. First, select the database and choose the Operations tab.
2. Then, select the desired collation under the Collation section.
3. Next, put a check in the checkbox next to Change all tables collations.
4. After that put a check in the checkbox next to the Change all tables columns collations option that pops up.
5. Finally, click the Go button.

Moreover, our Support Techs also suggest checking whether a plugin is causing the issue and disabling the plugin if it is the case.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, our skilled Support Engineers at Bobcares demonstrated how to fix WordPress database error: illegal mix of collations.