How to change all columns' and tables' collation to 'utf8_bin' in MySQL

Still need help?

The Atlassian Community is here for you.

Ask the community

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.

Problem

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

Diagnosis

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.


Resolution

Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

  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.

    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 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;
    Description This article provides instructions on how to update to the supported collation utf8_bin for both the columns' and tables' in a database.
    Product Jira
    Platform Server
Last modified on Oct 12, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.