Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

The database setup is not supporting utf8mb4 but it is.

Database: We've found an error in MySQL supported version!

The database setup is not supporting utf8mb4
See our documentation for more information on setting up MySQL 5.7.

 

I am running all of this on Windows Server 2012. Jira, Confluence, and mysql.

MYSQL is version 5.7.25.0

I followed the Jira Instructions Step by step and I can't get passed this error.

 

2 answers

0 votes

Hi,

which Jira version are you trying to install?

Best

JP

0 votes
Daniel Eads Atlassian Team Mar 21, 2019

Hey there Joshua,

We see errors like this when the MySQL collation is not set to what Jira expects to use. Luckily this is an easy fix! The process is documented on our knowledgebase, but I'll also paste the info here.

We recommend backing up your MySQL instance before making changes! If Jira is the only thing you've got on this MySQL instance (since Jira isn't working for you yet) go ahead and skip a backup if you wish. But if you're not sure that Jira is the only database there, better safe than sorry.

 

Changing the Database Collation

Change yourDB to suit your database name:

ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin

Changing Table Collation

The following query will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_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 != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

Changing Column Collation

The following queries (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_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 != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_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 != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

Dealing with Foreign Key Constraints

It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.

SET FOREIGN_KEY_CHECKS=0;
 
-- Insert your other SQL Queries here...
 
SET FOREIGN_KEY_CHECKS=1;

Let us know if you still have any issues getting set up!

Cheers,
Daniel | Atlassian Support

Followed these instructions.

 

No errors on the queries

Restarted Jira

 

 

Database: We've found an error in MySQL supported version!

The database setup is not supporting utf8mb4
See our documentation for more information on setting up MySQL 5.7.

Still broken. Still Down

Did you ever receive a resolution for this?

I'm facing the same issue with MySQL 5.7.28 and Jira version 8.5.1

Does anybody have a solution or should I just ditch Jira and try something else?

Well, 

you should follow the setup instructions for MySQL 5.7 for Jira.

You must use 

CREATE DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

 There is not success with utf8 or utf8_bin

Best

JP

I tried creating the database with all kinds of collations. Didn't really help.

I finally deleted the whole Jira installation and re-installed it. This time I told the setup program I was using a MySQL Version 5.6 DB - even though we are using 5.7.

Now it works

Like Chang Yang likes this

This fixed it for me too - choose MySQL 5.6 in the setup wizard, regardless of the version of MySQL you're actually running!

This fixes all kinds of other issues too, such as the SSL warning.

Also bear in mind the URL in Atlassian's documentation is completely wrong.  It should be in the following format:

 

<url>jdbc:mysql://address=(protocol=tcp)(host=10.0.0.5)(port=3306)/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>

 

I should add, the above solution isn't problem-free.

When using the MySQL 5.7 connector, I get the warning that the database isn't using "utf8mb4"... but ....

when using the MySQL 5.6 connector, I get the warning that the database is using "utf8mb4", and that isn't supported!

/facepalm

I'd use Postgres, but sadly Jira doesn't support the default charset/collation for that (and I'm running this all in Google Cloud Platform).

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

How to create Jira issus from Excel file?

When to use CSV importer When managing your processes in Jira, there are many occasions where you need to create a lot of tasks. Creating them one by one will cost you a lot of time and effort and i...

4,407 views 22 32
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you