Upgrade to Jira 9.0 Errors

[europoint]

I upgraded Jira using the latest Docker image to Jira version 9.0

Three issues have occurred:

1. Jira complains that the db and table collations are not compatible. Both are set to UTFMB8_BIN on a MySQL 8 database, which is expressly what is required by Jira.

Obviously, the system still works, but why is it throwing this error?

2. When I view a Jira board, I get the following error:

SELECT ID, PROJECT, cname, description, URL, `LEAD`, ASSIGNEETYPE, ARCHIVED, DELETED FROM component WHERE PROJECT=? ORDER BY cname (Unknown column 'DELETED' in 'field list')

In the database schema for version 8.20, there is no DELETED column, so I can only assume that this has been introduced by version 9.0. Since there is no published schema for version 9.0, I can't update it manually (could be boolean, could be decimal, could be varchar).

Again, the system still works, but I would expect any upgrade to apply schema changes itself.

3. Navigating to the Components screen, I get the following error:

Jira's base URL is set to https://{url} but you are accessing Jira from https://{url}undefined.

Come on Atlassian, surely these are the first things you check when publishing an upgrade?

[SISUP CSJT · Accepted solution]

I have the same error when upgrading from version 8.22.6 to 9.4.1.
My installation is jira server standalone

[europoint]

I managed to find a copy of the database schema and manually updated the database to resolve the DELETED issue.

Providing you have access to the database, you can run the following, replacing {jiradb} with your jira database name:

USE {jiradb};

ALTER TABLE component
ADD DELETED1 varchar(10) NULL DEFAULT NULL COLLATE 'utf8mb4_bin';

[Nic Brough -Adaptavist-]

Are all three collation settings correct on your database?

How did you perform the upgrade on your data?

[europoint]

Yes, all three collations are correct on the database. Version 8.22.3 doesn't complain about the collation at all.

I use the Docker Hub instance of Jira-Software:latest, so it just replaces the entire container, and restarts automatically. I've read the release notes, and there is no indication that I need to run anything else to complete the upgrade.

[Nic Brough -Adaptavist-]

Yeah, replacing an install is not an upgrade.

You'll need to run an upgrade.

[europoint]

Running an upgrade involves installing the new version. The instructions for an upgrade is to basically copy over the new files and run Jira (slightly more detail here: https://confluence.atlassian.com/adminjiraserver/upgrading-jira-manual-938846939.html)

Updating the Docker container from 8.22.3 to 9.0 is doing exactly the same thing. If you had to run an upgrade on your data using a non-dockerised install, and then replace it with the latest Docker container, that would completely abrograte the point of Docker containers.

[Nic Brough -Adaptavist-]

Note the warning on that page under the summary to-do list.

For the jump for 8 to 9, that absolutely applies - you can NOT just smack a new release over the top of the old one.

You need to run the upgrader in full.

The docker containers are for clean installations.

[europoint]

I must respectfully disagree with your statement that the docker containers are for clean installations: Docker containers are designed expressly for in-place upgrades, removing the downtime and effort required for a manual install.

Furthermore, it is expressly stated on the page for Jira that you can do the upgrade (https://hub.docker.com/r/atlassian/jira-software):

Upgrades

To upgrade to a more recent version of Jira you can simply stop the jira container and start a new one based on a more recent image:

docker stop jira
docker rm jira
docker run ... (See above)

As your data is stored in the data volume directory on the host it will still be available after the upgrade.

If this is not the case, then this needs to be made clear to end users, and the steps provided to upgrade the database. Suggesting that you must run a full manual install to upgrade your database, before then upgrading the docker container is not a solution.

[Nic Brough -Adaptavist-]

It's clearly not for upgrades - the upgrade process changes the shape of the database, and that's not happening when you swap 9 in over 8.

You're right that Atlassian have not accurately documented this failure.

[Andreas Schroeder]

During Jira 8 to Jira 9 upgrade we've seen the same issue of the missing 'deleted' column, which was caused in our case by wrong table permissions in the Postgres database, so that Jira could not apply database migrations. 

After fixing the table permissions in the database, Jira 9 migrated the database schema without further issues. This also means that you do _not_ need to re-run any upgrade assisstent. Jira will apply migrations on every restart. Most likely the same would also work when running from Docker containers.

[europoint]

That's really handy to know @Andreas Schroeder

I can't say for certain if that was the case for me last year, but it's certainly a possibility.