Why does my upgrade to anything newer than 8.9 fail?

[dominik_manser]

Hi everyone

I have a Server installation using the official docker image here : https://hub.docker.com/r/atlassian/jira-core.

My version was 8.8.0 and it was running smoothly connected to a mysql database in the background (version 5.7.31). As the documentation of the image on dockerhub states, we can just replace the image tag and start jira with a newer version. That's what I intended to do with the new version 8.13.0. It failed because of some columns that are missing in the mailserver table in the database. Turns out I can upgrade to 8.9.0, but not anything above (8.10.0 fails with the same error message). Can you tell me how I can upgrade my Jira-Version? I had to roll back to the old version and use a database backup to get things up and running again.

Here is part of the exception I get when starting with any version newer than 8.9:

2020-10-15 14:53:15,690+0200 Caesium-1-3 ERROR [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=812001] failed
com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select MAIL_SERVER.id, MAIL_SERVER.name, MAIL_SERVER.description, MAIL_SERVER.mailfrom, MAIL_SERVER.prefix, MAIL_SERVER.smtp_port, MAIL_SERVER.protocol, MAIL_SERVER.server_type, MAIL_SERVER.servername, MAIL_SERVER.jndilocation, MAIL_SERVER.mailusername, MAIL_SERVER.mailpassword, MAIL_SERVER.istlsrequired, MAIL_SERVER.timeout, MAIL_SERVER.socks_port, MAIL_SERVER.socks_host, MAIL_SERVER.auth_conf, MAIL_SERVER.cipher_type
from aki_dbs_jira.mailserver MAIL_SERVER
where MAIL_SERVER.mailpassword is not null and MAIL_SERVER.cipher_type is null
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build812001.lambda$runUpgrade$1(UpgradeTask_Build812001.java:71)
at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$execute$1(DefaultQueryDslAccessor.java:74)
at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:105)
at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)
at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:100)
at com.atlassian.jira.database.DefaultQueryDslAccessor.execute(DefaultQueryDslAccessor.java:73)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build812001.runUpgrade(UpgradeTask_Build812001.java:65)
at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.runOneUpgradeTask(DefaultUpgradeTaskFactoryProcessor.java:109)
at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.lambda$performUpgradesUnsafe$13(DefaultUpgradeTaskFactoryProcessor.java:80)
at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:174)
at java.util.stream.SortedOps$RefSortingSink.end(SortedOps.java:400)
at java.util.stream.Sink$ChainedReference.end(Sink.java:258)
at java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:500)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:486)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
at java.util.stream.FindOps$FindOp.evaluateSequential(FindOps.java:152)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.util.stream.ReferencePipeline.findFirst(ReferencePipeline.java:531)
at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.performUpgradesUnsafe(DefaultUpgradeTaskFactoryProcessor.java:81)
at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.performUpgrades(DefaultUpgradeTaskFactoryProcessor.java:46)
at com.atlassian.upgrade.core.DefaultUpgradeTaskManager.upgradeHostApp(DefaultUpgradeTaskManager.java:41)
at com.atlassian.jira.upgrade.LicenseCheckingUpgradeService.executeUpgrades(LicenseCheckingUpgradeService.java:134)
at com.atlassian.jira.upgrade.LicenseCheckingUpgradeService.runUpgrades(LicenseCheckingUpgradeService.java:97)
at com.atlassian.jira.upgrade.ClusterLockingUpgradeService.runUpgrades(ClusterLockingUpgradeService.java:35)
at com.atlassian.jira.upgrade.LoggingUpgradeService.lambda$runUpgradesWithLogging$0(LoggingUpgradeService.java:28)
at com.atlassian.jira.upgrade.LoggingUpgradeService.runWithTaskLogging(LoggingUpgradeService.java:43)
at com.atlassian.jira.upgrade.LoggingUpgradeService.runUpgradesWithLogging(LoggingUpgradeService.java:28)
at com.atlassian.jira.upgrade.IndexingUpgradeService.runUpgrades(IndexingUpgradeService.java:19)
at com.atlassian.jira.upgrade.UpgradeScheduler.runHostUpgrades(UpgradeScheduler.java:95)
at com.atlassian.jira.upgrade.UpgradeScheduler.runUpgrades(UpgradeScheduler.java:76)
at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:430)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:454)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:382)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'MAIL_SERVER.auth_conf' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.

[dominik_manser · Accepted solution]

I got help from the support and was able to fix this problem (although using docker images the solution is not very elegant as you have to remove a parameter in the dbconfig.xml that is set by default in the official image): 

The biggest issue, that blocks JIRA from functioning properly is its inability to create/update tables. Upon closer inspection of your dbconfig.xml file, we suspect, that the root cause of your problems may be an incorrect MySQL schema. You can read more about this issue in our documentation. The steps to fix this problem are listed below.

1. Stop the JIRA Server.
2. Delete the "<schema-name>somename</schema-name>" line from your dbconfig.xml file.
3. Start JIRA Server. The tables should be updated successfully.

[pszalko]

Thanks for the hint!

After struggling many hours with this issue I can finally confirm the simple and elegant solution to run Jira in docker.

The root of the issue is of course <schema-name> entry in dbconfig.xml, and it is populated when you define ATL_DB_SCHEMA_NAME at container start.

However, if you do not define ALT_DB_SCHEMA_NAME, then Jira will set it for you to "public". You can of course manually remove this entry from dbconfig.xml, but I think I found simpler one.

Here is the solution without the need of modifying the running container.

1. Define ATL_DB_SCHEMA_NAME as empty variable when running the Jira container.

2. Define schema name in ATL_JDBC_URL variable instead, for example:
ATL_JDBC_URL=jdbc:mysql://db_host:3306/db_name

[creker]

Tried updating step by step

8.5->8.6->8.7->8.9 went ok. 

8.9->8.10 - doesn't work due to the error above. mailserver table is missing auth_conf column. It seems for some reason Jira doesn't apply database migrations. /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/entitydefs/entitymodel.xml does indeed contain that column. Ended up manually creating the column with 

alter table mailserver add column auth_conf longtext

 This fixed the issue and all seemed to be working.

8.10->8.13 - even more missing columns. Manually added them with

alter table mailserver add column auth_conf longtext
alter table cwd_group add column external_id varchar(255)

Now everything appears to be working but who knows how many more columns Jira missed.

Maybe this is related to Jira being deployed in docker? I don't see any errors that would indicate that Jira couldn't migrate database schema. It starts properly and either you get an error on the UI or Jira itself encounters an error during "[c.a.jira.upgrade.UpgradeScheduler] Running scheduled upgrades".

I'm using official docker images. Database is also in docker mysql:5.7

[dominik_manser]

Awesome, thanks for your effort. I still thought it necessary to open an official Issue concerning this upgrade because we can not be sure that the DB Migration has properly worked.

I will post any new information here.

[creker]

Seeing the same error when trying to upgrade docker container from 8.5 to 8.13

[dominik_manser]

Exactly. I was upgrading from 8.8 to 8.13. After some experimenting I found out that I can upgrade only up to 8.9. This error appears from 8.10 upwards