Unable to perform administrative functions in crowd console due to error "Illegal mix of collations"

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

アトラシアン社外秘

この記事は未検証なのでお客様と共有できません。

症状

次のエラーがログに記録されます。

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 '='

原因

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

診断

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.

ソリューション

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.

  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

最終更新日 2018 年 11 月 2 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.