Problem

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

Diagnosis

Find the 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 the 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';

Resolution

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, otherwise, it won't allow modifications on the tables;

   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 platforms only.

   Click here to expand...

   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 the query for all affected tables, verify that the queries below 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 and execute the 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;

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