com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user

Still need help?

The Atlassian Community is here for you.

Ask the community

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

Summary

This error is commonly seen in JIRA Server environments that have been set up to use a MySQL 5.7.6 database version or higher. 

Environment

  • This error message is specific to MySQL 5.7.6 versions and higher. 
  • Jira only added support for MySQL 5.7.x versions beginning with the release of Jira 7.3.0 per Jira 7.3 Supported platforms.  If you encounter this problem on a version of Jira that is 7.2.x or before, then we would recommend that you either upgrade your instance to Jira 7.3.x or choose a supported database from your corresponding FIZME Supported platforms documentation.

Diagnosis

The most common times when you might see this kind of error would be:

  • During the initial setup of Jira
  • During a migration from Cloud to Server
  • If you have recently upgraded your database server or migrated your Jira database to a different database server.
  • During a project import
  • During a plugin installation/update
The following appears in the atlassian-jira.log
    at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:72)
    at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32)
    at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91)
    ... 6 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user 'jiradbuser'@'localhost' for table 'AO_5FB9D7_AOHIP_CHAT_LINK'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) 


Cause

The cause for this is explained deeper in MySQL: MySQL 5.7 Reference Manual: 7.2.1 Privileges Provided by MySQL

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.

Since this was unused, our previous documentation/guides never made note that this was required.  Since MySQL has changed its requirements, in turn, Atlassian has also updated our documentation on how MySQL databases must be configured in order to work properly with Jira.

Hence this error can happen anytime the database user has not been granted the needed permissions, and Jira is expected to create a new foreign key constraint.

Solution:

1 - Stop Jira

2 - Run the SQL commands

SQL commands
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';

flush privileges;  

3 - Start Jira
(info) The database name <JIRADB> is case-sensitive. If needed double-check the database name on $JIRA_HOME/dbconfig.xml

(info) Please refer to the KB Connecting Jira 7.3 applications to MySQL to have more details on how to grant the user permissions.


(star) Restarting Jira is needed.


DescriptionThis error is commonly seen in Jira Server environments that have been set up to use a MySQL 5.7.6 database version or higher
ProductJira
PlatformServer
Last modified on Dec 21, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.