Description:
A utf8mb4_unicode_ci collation-server setting when it is combined with the setting of optimistic updates in MySQL for Excel, leads to incorrect SQL updates and depending on the size of the column may end up to MySQL error.
In particular, optimistic updates setting leads to SQL update queries with redundant erroneous WHERE clauses. Disabling this setting helps you workaround the problems caused by the utf8mb4_unicode_ci collation-server setting.
How to repeat:
1. Import the attached database (import_demo_db.sql)
2. Disable in MySQL For Excel the "Optimistic updates" setting (Options, Edit Sessions)
3. Perform via MySQL For Excel the following statements
UPDATE `bug`.`v_vd_hrms` SET `extEmail`='asdfghjklzxcvb@gmail.com' WHERE `registrationID`='1001';
UPDATE `bug`.`v_vd_hrms` SET `employeeStatusDate`='20180102' WHERE `registrationID`='1001';
UPDATE `bug`.`v_vd_hrms` SET `extEmail`='asdfghjklzxcvbn@gmail.com' WHERE `registrationID`='1001';
UPDATE `bug`.`v_vd_hrms` SET `employeeStatusDate`='20180101' WHERE `registrationID`='1001';
4. Repeat the 1st step and import the attached database (import_demo_db.sql)
5. Enable in MySQL For Excel the "Optimistic updates" setting (Options, Edit Sessions)
6. Repeat via MySQL For Excel the updates of the 3rd step.
During the last step (6) notice the following:
Finding (a), by using the preview SQL statements setting, check the SQL syntax of the constructed updates.
Finding (b), when the value of the column extEmail (VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci) reaches a length of 25 (3rd update) the immediate next update on any other column fails with the error:
"MySQL Error 1267: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"
Suggested fix:
You can workaround the above type of MySQL Errors if you either disable the optimistic update setting, or by removing the collation-server setting, if possible.
Even if you remove the collation-server utf8mb4_unicode_ci setting, the MySQL For Excel SQL statements preview, continues to show the same erroneous WHERE clauses. Haven't checked if this is the actual SQL query that is sent on the MySQL Server, though.
