One of the common issue which arises after migration of database is dealing with different collations in tables. Database cannot handle the mix of these collations and throw error like
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
The cause for this error is because two different fields are been set with two different collation in same table.
To solve this problem
1. Go to PhpMyAdmin, select your database.
2. Look for the table having the issue, in this case it is #__virtuemart_products table.
3. Check the field which is having a different collation than other fields, in this example it is product_gtin. Normally fields use utf8_general_ci as collation or check what is the collation for your other fields in the table.
4. Select the Structure tab to see the complete field structure of the table.
5. Click on the edit icon or Change link for the field, here it is product_gtin. In the collation drop down look for utf8_general_ci and save the field.
Thats all to be done. If you still continue having the error, check for the same issue in other related tables.
Here are the few queries which can be used to convert column name or table or database to required collation.
To convert a column:
ALTER TABLE `your_table` CHANGE `your_column` `your_column` your_field_column_type
CHARSET utf8 COLLATE utf8_general_ci NULL;
your_field_column_type is the data type of the column like VARCHAR(10).
You need to replace this to the type of your column you are changing the collation.
To convert a Table:
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
To convert a Database
ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci
If you have collation looking good on tables and columns and you still have this error, Check if your import file is UTF-8 encoded. You can check that by using CSVI Analyser page. If you see any unreadable characters as in the image then you need to Save your CSV file as UTF8 encoded file before running the import.
