May i know how to solve the collation_name issue at MySQL.

[Casper]

May i know below collation_name is wrong or correct?  MySQL version is 5.7. 

I did follow this  How to Fix the Collation and Character Set of a MySQL Database, still having the error below.

[DPKJ · Accepted solution]

Casper `utf8_bin` is not correct if you are using Jira 8+. You need to migrate your database to `utf8mb4_bin` to get rid of collation error in health check.

If you look closely on the page you have linked, there are queries to help you do this process.

[Casper]

hi, thanks for the reply, do you mean below queries? 

i did the below queries , still getting the same out put ... 

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'yourDB'
AND
(
    C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
    C.COLLATION_NAME != 'utf8mb4_bin'

[DPKJ]

When you run this query you will get bunch of queries as output, and later you need to run these queries to get desired output.

[Casper]

like below right? 

after that which queries should i run ?

[DPKJ]

You need to run queries that you are getting in output of above query.

'ALTER TABLE ... CHARACTER SET utfmb4 COLLATE utfmb4_bin'

...

...

[Casper]

I already run, but my out put still the same with utf8_bin, may i know any other way to fix this ?

[DPKJ]

You need to run queries that you are getting in output of above query.

'ALTER TABLE ... CHARACTER SET utfmb4 COLLATE utfmb4_bin'

...

...

[Casper]

hi, you mean run"ALTER DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin" first? then only run those queries? 

[DPKJ]

@CasperHere are steps that I followed

• Shutdown Jira
• Backup database
• Alter database collation,
  • Run query 'ALTER DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin'
• Alter table collation,
  • Run query

  • SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
    FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
    WHERE C.collation_name = T.table_collation
    AND T.table_schema = 'yourDB'
    AND
    (
        C.CHARACTER_SET_NAME != 'utf8mb4'
        OR
        C.COLLATION_NAME != 'utf8mb4_bin'
    );

  • This will return list of alter statement in output console, copy these and run again
  • 
• Alter column collation
  • Run query

    • SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
      FROM information_schema.COLUMNS 
      WHERE TABLE_SCHEMA = 'yourDB'
      AND DATA_TYPE = 'varchar'
      AND
      (
          CHARACTER_SET_NAME != 'utf8mb4'
          OR
          COLLATION_NAME != 'utf8mb4_bin'
      );

    • SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
      FROM information_schema.COLUMNS 
      WHERE TABLE_SCHEMA = 'yourDB'
      AND DATA_TYPE != 'varchar'
      AND
      (
          CHARACTER_SET_NAME != 'utf8mb4'
          OR
          COLLATION_NAME != 'utf8mb4_bin'
      );

  • These both query will also output some more queries run those.
• Start Jira.

In case you face some issue related to foreign key, you can switch this off and turn back on.

• OFF

  • SET FOREIGN_KEY_CHECKS=0;

• ON

  • SET FOREIGN_KEY_CHECKS=1;

[Casper]

@DPKJ  Thanks for your great support and teaching me on this, my issue is solved.