I ran across this issue in an upgrade from 2.9.0 data to 4.1.1.
ERROR: query failed: UPDATE billing, code_types, temp_table_one SET temp_table_one.code_type=billing.code_type, temp_table_one.code=billing.code, temp_table_one.modifier=billing.modifier WHERE billing.code_type=code_types.ct_key AND code_types.ct_fee=1 AND temp_table_one.pid=billing.pid AND temp_table_one.encounter=billing.encounter AND billing.activity!=0
Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
I tried dumping and recreating the table insuring utf-8 setting, but that did not help. There were only 21 records in the table so I emptied it for now to get past it.
This is likely because you have a “hybrid” database for your collation. Some international users have reported this issue also. Your original database was likely set to utf8_general_ci, which is the default when you ran setup.php(although pre 3.1 may of actually had some utf8 encoding mixed with latin1 per the link below). However, if you then placed this database within another database shell that defaults to utf8_unicode_ci, then new sql columns that are added that will use the utf8_unicode_ci collation, which can then break certain queries. Should be able to change the collation settings in phpmyadmin to ensure they are all the same.
After some discussion via email with an international user and Rod, think it would make a lot of sense to have a Administration->Diagnostics script/gui that did some basic testing with one of the tests ensuring that a hybrid encoding/collation mysql does not exist and that the current default settings of the database match the current entries (in addition to a bunch of likely other useful dx tests such as orphaned data/files etc).
Just upgraded from ver 4.0. to ver 4.1.2 on an Ubuntu 12.04 server. Have come up with this problem: Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’
Any help?