Jira running on mysql database does not start post performing an upgrade
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Jira running with MySql database fails with an SQL Exception post an upgrade.
Environment
Jira v8.13 or higher
MySql v5.7
Diagnosis
The following SQL Exception (Unknown error 1054) is being observed in atlassian-jira.log at the time of startup:
1
2
3
4
5
6
2022-11-15 19:17:21,275-0500 JIRA-Bootstrap ERROR [c.a.s.core.lifecycle.DefaultLifecycleManager] LifecycleAware.onStart() failed for component with class 'com.atlassian.greenhopper.Launcher' from plugin 'com.pyxis.greenhopper.jira'
io.atlassian.util.concurrent.LazyReference$InitializationException: com.atlassian.cache.CacheException: io.atlassian.util.concurrent.LazyReference$InitializationException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, group_name, active, local, created_date, updated_date, description, group_type, directory_id, external_id FROM jiradb.cwd_group WHERE directory_id=? (Unknown error 1054)
at io.atlassian.util.concurrent.LazyReference.getInterruptibly(LazyReference.java:156)
at io.atlassian.util.concurrent.LazyReference.get(LazyReference.java:116)
at com.atlassian.greenhopper.Launcher.onSystemStartPhase(Launcher.java:214)
at com.atlassian.greenhopper.Launcher.onStart(Launcher.java:197)The following error is seen immediately which reports of an upgrade task failure with MySQLSyntaxErrorException:
1
2022-11-15 19:18:17,992-0500 Caesium-1-4 ERROR [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=812001] failed com.querydsl.core.QueryException: Caught MySQLSyntaxErrorException 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 jiradb.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)Cause
Jira is using a MySQL database and has a schema specified, where it should not, as below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
=========================
Database configuration OK
___ Database Configuration _________________
Loading entityengine.xml from : [file:/var/atlassian/opt_jira/jira/atlassian-jira/WEB-INF/classes/entityengine.xml|file:///var/atlassian/opt_jira/jira/atlassian-jira/WEB-INF/classes/entityengine.xml]
Entity model field type name : mysql
Entity model schema name : jiradb
Database Version : MySQL - 5.7.24
Database Driver : MySQL Connector Java - mysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 )
Database URL : jdbc:mysql://address=(protocol=tcp)(host=<hostname>)(port=3306)/jiradb?sessionVariables=default_storage_engine=InnoDB&useSSL=false
Database JDBC config : mysql57 jdbc:mysql://address=(protocol=tcp)(host=<hostname)(port=3306)/jiradb?sessionVariables=default_storage_engine=InnoDB&useSSL=false
================Despite this configuration being incorrect, the Database Configuration check during JIRA startup will pass. There is a request for better handling this within JIRA tracked as part of JRASERVER-32590 - Database configuration incorrectly reports as OK for a MySQL DB where a schema is set.
Solution
Stop JIRA.
Remove the entire line containing the <schema-name> tag from the dbconfig.xml:
1<schema-name>jiradb</schema-name>Restart JIRA and confirm the database tables are created successfully by confirming no similar errors are present in the logs during startup.
Refer to Connecting Jira applications to MySQL 5.7 for a sample dbconfig.xml. Also note, that MySQL 5.7 is no longer supported starting with Jira 9.2 and later.
Was this helpful?