Fixing MySQL error - "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)"

On one of our websites, the above error started showing suddenly after an upgrade. There seemed to be no reason for this error as there were no updates on the database or any related component.

After quick googling, there were no. of solutions proposed. Basically, all solutions mentioned that code & MySQL should handle strings using same char set & collation.

I tried changing collation of tables using phpMyAdmin's "Operations" tab. It changed charset & collation for the table, but did not affect individual columns of the table.

I then tried executing this query:

ALTER TABLE `dataexchange`.`crnotes` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

But, it failed showing me "Error copying file ???? to table-name, errno 150". Error No. 150 is foreign key constraint error.

I created this a SQL file containing following commands and tried from phpMyAdmin's "Import" tab, And it worked:


/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

ALTER TABLE `dataexchange`.`crnotes` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;


One more point, I had tried same set of commands from "SQL" tab of phpMyAdmin, which did not work.