Issues with MySQL 5.7 and Jira 8.12

[Alexander Badich]

I've recently upgraded to 8.12 a little over a month ago and I was running windows updates last night.  I came back to not being able to connect to Jira via the web because of an error speaking about utf8mb4 and collation utf8mb4_bin, after trying to change the schema of for the db there was no change in Jira.   I then went back and dropped the db and restarted by creating a new db with the correct chara set and collation, but now I am still getting the same error, and as well in my newly created db there were no tables created. 

*****UPDATE

I have restored my VM back a few days and I am now back to my original error:

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

With this being said, I haven't ran into any issues but rebooted the VM yesterday and now things are terrible.

I read an article to check my character set and collation and this is the query I ran on MySQL 5.7.29:

SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'character\_set\_%' OR variable_name LIKE 'collation%';

These are my results and I am soooo confused:

How do I set character set and collation to all tables, and will this screw anything up to my data?

****Update UPDATE

I found a work around to a bigger problem.  As for the variable and values shown above I have no yet found a way to get these to change and for the JIRA to be happy with the changes.  I can go and run:

ALTER DATABASE <yourDB> CHARACTER SET <charset> COLLATE <collation>

and so on to the 'table name' and as well for the column but that turns into a disaster because I have 100s of tables and columns, and one could 'alter' could blow the machine to pieces. 

 As for the FIX:   I am running utf8 under 'character_set_system' and nothing else, which is weird and probably a bigger problem THAT I STILL NEED HELP WITH SO THROW ME SOME IDEAS, but what I did was manually edit dbconfig.xml and instead of having set to mysql57, I just set it to mysql....and its running as of now!

BUT STILL LET ME THE ME KNOW THE BEST WAY TO CHANGE THE VARIABLES ABOVE TO MYSQL5.7 REQUIREMENTS. 

THANK YOU!

Please help!

Thank you!

[Andy Heinzer · Accepted solution]

Hi Alexander,

I understand that you're running a MySQL 5.7 database for Jira 8.12 to connect to, but that it sounds like you might be getting healthcheck warnings that the database collation or encoding is using an unsupported character set.

I can see from your screenshot that the character_set_server looks to be using latin1 instead of the utf8mb4 value we would expect.   This is a MySQL setting more than it is a specific database setting.  We have documented this setting in our Connecting Jira applications to MySQL 5.7 however in my own recent experiences, I'm rarely finding MySQL installs that still use a my.cnf or my.ini config file for setting this.   In my own experience I have had better success using a database utility such as MySQL WorkBench  for changing MySQL system settings like this one.

Once that setting is corrected, I would still then try to follow these steps to prevent other such warnings:

1. Create a new XML backup of your data in Jira (default store is in the $JIRAHOME/export/ folder)
2. Stop Jira
3. Create a new database per the exact steps in Connecting Jira applications to MySQL 5.7 
4. Use the Jira Config tool to point your existing Jira install to use this new, empty database, and then save these changes.
5. After saving those changes, check the $JIRAHOME/dbconfig.xml file.  Make sure that the <url> parameter is using the characterEncoding parameter as noted in the Database connection fields section of the Connecting guide.  Such as

       <url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
   

6. Start Jira - when Jira starts connected to an empty database it will launch the setup wizard
7. In the setup wizard, choose the option to 'import data from backup' and select the XML backup .zip file created in step 1.  You might have to copy/move that .zip file from the $JIRAHOME/export/ folder into the $JIRAHOME/import/ folder

By following these steps, we can be much more confident that your data will be imported correctly and that the database will have the correct correlation and encoding.  Trying to alter an existing database tends to be a much more complex and less successful in my experience than just utilizing the backup and restore options in Jira.

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

Andy

[Alexander Badich]

Andy,

I really appreciate the assistance.   I have tried to do this re-make of the jira database, and then point the JIRA instance to the new database with correct credentials.  I also tried to bring over an XML backup from  /EXPORT to /IMPORT, but for some reason when starting up JIRA and hitting our URL, I never found the "import from backup" link or maybe I was missing it. 

Besides that though,  but creating a new jira database with the correct collation/character set utf8mb4 and utf8mb4_bin before importing all of my data from the XML, won't that scramble a bunch of stuff?   I just don't want to screw all of the tickets etc up...

Thank you!

[Andy Heinzer]

When you create a new database, setting the collation has nothing to mess up yet.  There is no data in that new database yet.  The setup wizard of Jira will only appear when Jira is configured without any database, or the database it is pointed to is empty, and in turn it has to create the tables it will need when starting up.

If you're in that setup wizard, you should see a link called 'import existing data' (sorry I think I got the exact title wrong on my last post).  You need to use that to get to the import wizard.  If you are in the setup wizard and you get to a screen prompting you for the admin username, admin full name, admin email, and password, then you have gone too far.  The link for the import is on a page before this.  There is also a KB about this in Importing data via Setup Wizard that might help.

This wizard importing your xml backup won't muck anything up.  All your data will still be there, just this time in a database setup that Jira is actually expecting it to be in.

[Alexander Badich]

I am still seeing issues on this matter....

Whenever I create a new empty database, new creds etc...I change the location of the db and all the required information.  I check the dbconfig.xml, and everything looks fine. After starting up JIRA again, I get a message with the same error as before speaking about character set and collation. I have backup my dbconfig.xml and deleted it, and when doing so I finally was able to get back to the setup wizard.  When I follow your instructions above, there is no button for "import existing data".   

As well if I go back one step:

[Alexander Badich]

I found a workaround but its sloppy. 

-if I edit the dbconfig.xml to show the db version is just mysql and not mysql57, run the setup wizard it will start to import jira tables.  GREAT!  I have done it...WRONG.

-Even though I was able to change the character set/collation to the "correct" utf8mb4/utf8mb4_bin, and import all my data to that new db.  I thought hmmmm...lets change the dbconfig.xml to mysql57, and starterrrup....back to same error. 

------

SELECT T.TABLE_NAME, C.CHARACTER_SET_NAME, C.COLLATION_NAME
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 != '<charset>'
 OR
 C.COLLATION_NAME != '<collation>'

I think I found the issue, even though both of the above value show the correct schema:

utf8mb4/bin, the character-set-server value hasn't changed...

 

So where do I change this value...maybe that is what is causing the whole problem.

[Alexander Badich]

Now, the big question is why/how is Jira picking up on an issue about the character set/collation if it was a brand new db, is there someplace else that needs to be changed that Jira look over before starting?

--any ideas?

I appreciate all your help!

[Alexander Badich]

I also got help from G-Borrow and Bob S.  advice here:

https://community.atlassian.com/t5/Jira-questions/MySQL-5-7-doesn-t-start-after-settings-for-utf8mb4-are-applied/qaq-p/1076947#U1527011

Thank you!