Jira and MySQL upgrade

This GIST is a compilation of steps/instructions to help on how to

Upgrade MySQL to version 8 from version 5
Upgrade Jira to version 9.4 LTS from version 8.x
Fix MySQL collation issues for new version of Jira

These steps have been executed on Ubuntu 24.04 after a few subsecuent Ubuntu OS upgrades with the do-release-upgrade tool. It might be different for you if you have a different configuration and OS, but the overall process/idea should be universal.

As always, backup everything before proceeding with the upgrades. Ideally, you'd take a few snapshots of the VM(s) as you go on with the process.

MySQL Upgrade and collation fix

After executing whichever command is relevant to your case to upgrade MySQL to version 8, you must:

Update MySQL configuration to match requirements for new version of JIRA

/etc/mysql/mysql.conf.d/mysqld.cnf

default-storage-engine=INNODB
max_allowed_packet=256M
innodb_default_row_format=DYNAMIC
innodb_redo_log_capacity=4G
innodb_log_file_size=2G
character_set_server=utf8mb4
collation-server=utf8mb4_bin
transaction-isolation=READ-COMMITTED
binlog_format=row
log_bin_trust_function_creators = 1

For your DB, tables, and collums, the
- character set must be changed to utf8mb4
- collation must be changed to utf8mb4

For that you may follow this article which will help with the required process in order to do so.

Pro tip: Copy each generated SQL syntax that get created from running the commands in the article above, into your favorite editor, and use this ([\|\s][\s+\|]+) regex to remove all the pipes in go. Leaving you with a syntax like

ALTER TABLE `workflowscheme` MODIFY `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `worklog` MODIFY `worklogbody` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `worklog_version` MODIFY `DELETED` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

that you can easily paste and execute, instead of

...
| ALTER TABLE `workflowscheme` MODIFY `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;                                                                                               |
| ALTER TABLE `worklog` MODIFY `worklogbody` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;                                                                                                  |
| ALTER TABLE `worklog_version` MODIFY `DELETED` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;                                                                                                  |
...

JIRA

OS Settings

One important aspect, is to ensure JIRA runs with its own Linux user. For that, ensure that

You have a jira user available.
/opt/atlassian is owned entirely by jira user with: chown -R jira: /opt/atlassian
/var/atlassian is owned entirely by jira user with: chown -R jira: /var/atlassian
You start/stop JIRA app as the jira user

Finally, you can also create a startup script for JIRA, so you can easily execute service jira start|stop|restart. See here on how to do so.

Upgrade preparations

This article walks you through the steps on upgrading JIRA and the requirements to do so.

But in essence, you'll have to

Update all the installed apps to their latest version BEFORE upgrading Jira (it would be best to clean/delete whatever is not essential/being used)
Disable indexing for the duration of the upgrade process
Download the JIRA Core 9.4.x bin installer to /home/jira from official site with wget i.e.: wget https://www.atlassian.com/software/jira/downloads/binary/atlassian-jira-core-9.4.30-x64.bin
Execute the binary, as root, and set it to upgrade an existing installation
After the upgrade, execute again the steps mentioned above so that the atlassian directories are all owned by jira user

Post upgrade

Ensure that the database-type parameter inside dbconfig.xml is configured for mysql8

<database-type>mysql8</database-type>

Remove indexing configurations
Update all JIRA plugins to their latest versions again
Go to /plugins/servlet/applications/versions-licenses and check whether you need to update Jira Service Management software, and do so if needed.

Additional references

How to Create Users in Linux

JIRA Database Setup For MySQL

Connecting Jira applications to MySQL 8.0

w0rldart/README.md