Migrate from Mysql 8.0.25 to 8.0.29 - Collation error

Question

Hello,

my old Jira installation was on Debian 9 with Mysql 8.0.25 and all worked fine.

I exported the DB with dump to SQL file, installed a fresh Debian 11 with Mysql 8.0.29 (8.0.25 not available on Debian 11) and imported it in the new database created using this help https://confluence.atlassian.com/jiracore/connecting-jira-to-mysql-8-0-1018272102.html

Jira start but i have the error "

The table collation: 'utf8_bin' is unsupported by Jira. The Database collation: 'utf8mb4_bin' is supported by Jira." but the database on the new user is correctly configured.

I checked with this command :

mysql> use jiradb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
@collation_database;
+--------------------------+----------------------+
@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_bin |
+--------------------------+----------------------+
1 row in set (0.00 sec)

What's wrong for you ?

I checked the same on the old server :

Database changed
@collation_database;
+--------------------------+----------------------+
@collation_database |
+--------------------------+----------------------+
| utf8mb3 | utf8_bin |
+--------------------------+----------------------+
1 row in set (0.00 sec)

It's different but no error was showed..

Jira Service Management 4.20.2

Jira Software 8.20.2

Thank you for help..

Julian

Julian Castello · Answer

Hello,

any news?

Julian Castello · Answer

Hello Nic!

Here the result :

mysql> use jiradb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
@character_set_server ;
+--------------------------+----------------------+------------------------+
@character_set_server |
+--------------------------+----------------------+------------------------+
| utf8mb4 | utf8mb4_bin | utf8mb4 |
+--------------------------+----------------------+------------------------+
1 row in set (0.00 sec)

Is it correct?

Thank you

Nic Brough -Adaptavist- · Answer

I keep getting this one wrong myself.

There are three collation settings you need to check, not just the two you've given us here.

I think you'll get something like the below, where you'll find the bold one is wrong!

@character_set_server ;
+--------------------------+----------------------+------------------------+
@character_set_server |
+--------------------------+----------------------+------------------------+
| utf8mb4                  | utf8mb4_bin          | utf8                   |
+--------------------------+----------------------+------------------------+

If that's the case, you've missed one of the settings mentioned in the database setup doc you've linked to.