Problems to fix CHARACTER SET and COLLATION

[Gines Escudero]

Hi,

We are having problems with our Jira server. We initially installed it on a CentOS 8.1 using MariaDB as database, and it had by default the wrong CHARACTER SET and COLLATION. I have tried to amend that to avoid the warnings when you login into Jira, but if I try to export the database from MariaDB, and import it into MySQL with the correct COLLATION and CHARACTER SET, the top navigation bar dissapears from Jira.

I have even used the command  iconv -f ISO8859-1 -t UTF-8 mydatabase.sql to make sure that all the characters are converted into the UTF-8 CHARACTER SET, but still have the problem with the navigation bar. Could you help me to solve the problem?

Thanks,
Ginés.

[Andy Heinzer · Accepted solution]

Hi Ginés,

Sorry to hear about these problems with databases Jira is using.  I understand that you're trying to correct a collation problem that Jira has identified, but it sounds like you are having problems when this happens.

Rather than trying to export the database direct from MariaDB or correct that collation directly, instead I'd recommend a different means of doing this.  First, Follow the steps in Connecting Jira applications to MySQL 5.7 in order to create a new empty MySQL 5.7.x database.  Honestly, you can select any database type/version that is listed on the Jira 8.5 supported platforms documentation, I just suggest MySQL because you mentioned it.  Take note to make sure you use the correct collation here.

Next you can create an XML backup within Jira of your entire database.  Once you have that backup file, you can then follow the steps in Switching databases: Migrating Jira's data to a different type of database server.  This approach tends to work much better at correcting collation problems because Jira can import that data from the XML backup into your new database with the correct collations and character encodings as part of the import process.  Just as long as the new database is setup correctly, this tends to be the best approach to correcting these kinds of configuration problems.

Try this and let me know if you run into any problems with this approach.

Cheers,

Andy

[Gines Escudero]

Thanks for the help, it fixed all my problems!