MySQLSyntaxErrorException: REFERENCES command denied to user

Owned by Andy Brook [Plugin People]
Last updated: Jul 15, 2020 by Mike Harrison
1 min read

Problem

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

You have mysql 5.7.6 or higher, you have upgraded and find apps fail, even Confluence (uknown start ver) or Jira 7.3+ will have problems, reviewing the logs, you will find the topic title

Docs

https://confluence.atlassian.com/jirakb/com-mysql-jdbc-exceptions-jdbc4-mysqlsyntaxerrorexception-references-command-denied-to-user-872266070.html

Solution

You need to grant the REFERENCES permission to the addon tables affected.

Custom Space User Management (CSUM) for Confluence

CSUM uses an Atlassian framework called ActiveObjects, which causes tables to be created for app usage, all these tables need to be updated or related apps will likely fail.

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <CONFDB>.AO_45E2D6* TO '<USERNAME>'@'<CONF_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>'; flush privileges;