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

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

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

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

Step-by-step guide

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;

    (info) 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;

    (info) 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.

    (info) 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 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;

There is no content with the specified labels

Last modified on Sep 12, 2017

Was this helpful?

Yes
No
Provide feedback about this article

Not finding the help you need?

Ask the community

Powered by Confluence and Scroll Viewport.