Unable to perform administrative functions in crowd console due to error "Illegal mix of collations"
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
The following error is logged:
1
2
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:
1
SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<databasename>' AND collation_name != 'utf8_bin';1
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.
Before starting the procedure backup your database and application directories.
Shutdown Crowd
Shutdown the MySQL server used by Crowd
Edit the
my.cnffile (ormy.iniif your MySQL server is on Windows), locate the [mysqld] section, add/replace the following parameters:1 2 3 4 5[mysqld] ... character-set-server=utf8 collation-server=utf8_bin default-storage-engine=INNODBStart MySQL server
Run the following queries against Crowd's database:
1ALTER DATABASE <databasename> CHARACTER SET utf8 COLLATE utf8_bin;1SET foreign_key_checks = 0;1ALTER 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:
1select 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;Now run the diagnostic queries again. If you are still getting results, make note of the tables and run the query below again:
1ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;Once you get zero results on both diagnostic queries, run this last command to re-enable foreign key checks:
1SET foreign_key_checks = 1;Start Crowd
Check for the behaviour again
Was this helpful?