Unsupported database collation after switching to utf8mb4_bin

[Tech Support]

I have just upgraded from JIRA 8.18.1 to 8.22.6. We are using MySQL 5.7 as the database.

I noticed that the database collation was utf-8 and that JIRA versions past 8.12 do not support this. I therefore followed the steps on the atlassian site to upgrade the database collation to utf8mb4 which all went fine on the MySQL side.

However, I'm now receiving the following error in JIRA database check:

Result

The database collation 'utf8mb4_bin' and table collation 'utf8mb4_bin' are not supported by Jira.

Yet this is the only collation that 8.22.6 is supposed to support?

Does anyone have any idea why this might be? Everything seems to be running, it's just an error on this page only - is it safe to ignore it?

[Nic Brough -Adaptavist-]

I'm afraid this is what it says it is - you have not set up the database correctly.

Have another look through https://confluence.atlassian.com/adminjiraserver0822/connecting-jira-applications-to-mysql-5-7-1142236990.html and pay close attention to exactly what each collation setting is.

[Tech Support]

Thanks for the response. This was not a new set up, it's been set up for the last 5 years+ so was originally in utf8. I followed this guide:

https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-manually-744326173.html

Copying and pasting each section, converting the database, then the tables and then the columns. Using this afterwards:

To check database collation:
use jiradb;
SELECT @@character_set_database, @@collation_database;

Gives me all the correct responses that utf8mb4 with a collation of utf8mb4_bin is in use.

It's just JIRA complains that this database collation isn't supported?! I only swapped to this collation because they said it was the only one supported now....

[Nic Brough -Adaptavist-]

There are three settings to look at, and you need to be very very careful about the exact names you are using.  Give me a few minutes, I need to go look up what the third one is.

[Nic Brough -Adaptavist-]

Sorry, couldn't remember the last client who had this problem, but I found it:

mysql> select @@character_set_database, @@collation_database, @@character_set_server ;
+--------------------------+----------------------+------------------------+
| @@character_set_database | @@collation_database | @@character_set_server |
+--------------------------+----------------------+------------------------+
| utf8mb4                  | utf8mb4_bin          | utf8                   |
+--------------------------+----------------------+------------------------+

[Tech Support]

Thanks, this is what I have:

should the last one only be utf8? I only changed the things that the guide said.

[Nic Brough -Adaptavist-]

Your screenshot is too small to be clear, but yes, you need to get all three settings right.

[Tech Support]

Sorry, what do you mean by right? Exactly as in your example with character_set_server set to utf8?

[Tech Support]

Just in the atlassian guide it says to set it to this:

[mysqld]
...
character_set_server=utf8mb4
...

[Nic Brough -Adaptavist-]

Sorry, I should not have included the actual settings in my answers, it's misleading you.   They are the values found before we corrected it.

Yes, you should follow the docs, but you need to get all three of those settings right.

[Tech Support]

All 3 appear to be correct according to the documentation which is what's confusing me :(

@@character_set_database    -    utf8mb4

@@collation_database           -     utf8mb4_bin

@@character_set_server         -     utf8mb4

I've restarted the mysql server and restarted the jira web server and still it complains for some reason. Everything appears to be working fine, just worried incase something is going wrong in the background and then it all falls over in a week.

[Tech Support]

I finally found the issue....

I had to change the database type from mysql to mysql57 in the dbconfig.xml

Thanks for your help

[Andrey Kiyanovsky]

And for MySQL 8 it has to be 'mysql8'. Thanks!