Symptoms

The following error is logged:

Hibernate operation: could not execute update query; uncategorized SQLException for SQL [delete from `REMOTEGROUPMEMBERS` where (`REMOTEGROUPDIRECTORYID`, `REMOTEGROUPNAME`)=(?, ?)]; SQL state [HY000]; error code [1267]; Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=';
nested exception is java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Cause

There is a conflict in your MySQL database between the tables and columns character encoding/collation.

Diagnostic

Run the following validation queries so we can confirm that your tables and/or database is not using the default encoding/character set:

SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<databasename>' AND collation_name != 'utf8_bin';

SELECT * FROM information_schema.TABLES WHERE table_schema = '<databasename>' AND table_collation != 'utf8_bin'

Where <databasename> is the name of your Crowd database.

If the above query returns any results, it means your database is not using the default encoding/character set.

Resolution

To address this, it's necessary to convert the MySQL server default collation and convert the current collations of the tables/columns to utf_bin, which is the recommended collation.

1. Shutdown Crowd
2. Shutdown the MySQL server used by Crowd

3. Edit the my.cnf file (or my.ini if your MySQL server is on Windows), locate the [mysqld] section, add/replace the following parameters:

   [mysqld]
   ...
   character-set-server=utf8
   collation-server=utf8_bin
   default-storage-engine=INNODB

4. Start MySQL server

5. Run the following queries against Crowd's database:

   ALTER DATABASE <databasename> CHARACTER SET utf8 COLLATE utf8_bin; 

   SET foreign_key_checks = 0;

   ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

   If the diagnostics query returns more than one result, you can the query below to generate the ALTER TABLE query for all the affected tables:

   select concat('alter table ',  table_name, ' convert to character set utf8 collate utf8_bin;') from information_schema.tables  where table_schema='<yourdatabasename>' and table_collation != 'utf8_bin' group by table_name;

   
   

6. Now run the diagnostic queries again. If you are still getting results, make note of the tables and run the query below again:

   ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

7. Once you get zero results on both diagnostic queries, run this last command to re-enable foreign key checks:

   SET foreign_key_checks = 1;

8. Start Crowd

9. Check for the behaviour again