com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user
プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。
This error is commonly seen in JIRA Server environments that have been setup to use a MySQL 5.7.6 database version or higher.
The most common times when you might see this kind of error would be:
- During an 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)
- 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.
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 be granted the needed permissions, and JIRA is expected to create a new foreign key constraint.
The documentation on Connecting JIRA 7.3 applications to MySQL now has the correct SQL commands need to configure the user permissions correctly. The new command is:
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>'; flush privileges;