Change column and table collation to utf8_bin in MySQL

お困りですか?

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

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

問題

This article provides instructions on how to update to the supported collation utf8_bin for both the columns' and tables' in a database.

診断

Find database, database tables, and database columns with collations different than the recommended utf8_bin using the below queries. Then, take note of the ones returned for next steps.

SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = '<database-name>' AND DEFAULT_COLLATION_NAME != 'utf8_bin';
SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<database-name>' AND collation_name != 'utf8_bin';
SELECT * FROM information_schema.TABLES WHERE table_schema = '<database-name>' AND table_collation != 'utf8_bin';

Replace <database-name> with the name of the database used by your JIRA application instance.


ソリューション

データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。

  1. Stop your JIRA application;
  2. Run the below queries in the application database to alter the database default collation;

    ALTER DATABASE <database-name> CHARACTER SET utf8 COLLATE utf8_bin;

    Replace <database-name> with the name of the database used by your JIRA application instance.

  3. Disable MySQL's constraint checks,otherwiseitwon'tallowmodificationsonthetables;

    SET foreign_key_checks = 0;
  4. Alter the collation of a table with the below query;

    ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

    Replace <table-name> with the name of a table returned by the second query in the Diagnosis section.


    • If the above fails, use the below instead;

       ALTER TABLE <table-name> CHARACTER SET utf8 COLLATE utf8_bin;

    If the query for tables set with wrong collation ran on the Diagnostics step returns more than one result, you can run the below steps to generate a set of queries for all affected tables in a file. The following is an example provided for UNIX platform only.

    ここをクリックして展開...

    1. Generate a file containing the ALTER TABLE queries.

    	SELECT CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') INTO OUTFILE '/tmp/alterstatements.sql'
    	FROM information_schema.tables
    	WHERE table_schema='<database-name>'
    	AND table_collation != 'utf8_bin' 
    	GROUP BY table_name;

    2. Check if the output file is correct.

    	cat /tmp/alterstatements.sql

    3. Run the SQL file against the current database.

    	mysql my_jira_db  < /tmp/alterstatements.sql


  5. After running thequeryforallaffectedtables,verifythatthequeriesbelow now return no results;

    SELECT * FROM information_schema.COLUMNS WHERE table_schema = '<database-name>' AND collation_name != 'utf8_bin';
    SELECT * FROM information_schema.TABLES WHERE table_schema = '<database-name>' AND table_collation != 'utf8_bin';
  6. If the above queries return any results, take note of the tables it returns andexecutethe below query, which will define the default collation for the table;

    ALTER TABLE <table-name> DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
  7. Revert the MySQL constraint checks for foreign keys with the below query;

    SET foreign_key_checks = 1;
    説明This article provides instructions on how to update to the supported collation utf8_bin for both the columns' and tables' in a database.
    製品Jira
    プラットフォームサーバー
最終更新日 2019 年 9 月 25 日

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

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