Jira server throws unknown error when creating projects using MySQL
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Problem
Creating a project in JIRA throws the following error
Hmm... we couldn't create your project due to an unknown error. Try refreshing the page to start again
REFERENCES command denied to user error appears in the atlassian-jira.log
2016-07-21 06:59:29,223 Caesium-2-3 ERROR ServiceRunner [c.a.scheduler.core.JobLauncher] Scheduled job with ID 'com.atlassian.jira.plugins.dvcs.scheduler.DvcsScheduler:job' failed
com.atlassian.cache.CacheException: com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.atlassian.jira.plugins.jira-dvcs-connector-plugin]
at com.atlassian.cache.memory.DelegatingCachedReference.get(DelegatingCachedReference.java:84)
at com.atlassian.jira.plugins.dvcs.dao.impl.CachingOrganizationDaoImpl.getAllCachedOrgs(CachingOrganizationDaoImpl.java:192)
at com.atlassian.jira.plugins.dvcs.dao.impl.CachingOrganizationDaoImpl.getAll(CachingOrganizationDaoImpl.java:69)
at com.atlassian.jira.plugins.dvcs.service.OrganizationServiceImpl.getAll(OrganizationServiceImpl.java:89)
....
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:MySQL
- version:5.7.12-0ubuntu1.1
- minor version:7
- major version:5
- name:MySQL Connector Java
- version:mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad )
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user 'jiradb'@'localhost' for table 'AO_E8B6CC_CHANGESET_MAPPING'
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:47)
at com.atlassian.jira.plugins.dvcs.activeobjects.v3.To_12_SplitUpChangesetsMigrator.upgrade(To_12_SplitUpChangesetsMigrator.java:308)
at com.atlassian.activeobjects.internal.ActiveObjectUpgradeManagerImpl$1.doInTransaction(ActiveObjectUpgradeManagerImpl.java:68)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user 'jiradb'@'localhost' for table 'AO_E8B6CC_CHANGESET_MAPPING'
at sun.reflect.GeneratedConstructorAccessor174.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2246)
at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2302)
at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2274)
at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
at net.java.ao.EntityManager.migrate(EntityManager.java:128)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:45)
... 51 more
Diagnosis
Environment
- Using JIRA 7.1.x and above
- Using MySQL 5.7.x
Cause
The database user doesn't have the REFERENCES privilege. MySQL 5.7 introduced the REFERENCES privilege, but as per our Supported platforms page, only JIRA 7.3 and above supports MySQL 5.7.
From http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_references
The REFERENCES privilege is unused before MySQL 5.7.6. As of 5.7.6, creation of a foreign key constraint requires the REFERENCES privilege for the parent table.
From: http://dev.mysql.com/doc/refman/5.7/en/grant.html
REFERENCES - Enable foreign key creation. Levels: Global, database, table, column.
Suggestion ticket raised to address this: JRA-40198 - Getting issue details... STATUS
Resolution
JIRA 7.3 and above
Grant the REFERENCES permission the the database user
GRANT REFERENCES on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>';JIRA 7.2.x and below
Switch to a supported version of database
- If installing JIRA for the first time, re-install with a supported database
- If MySQL was upgraded, refer to Switching Databases to switch to a supported database