Confluence fails to install or run with 'SQL statement command denied' error with MySQL DB
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
We're unable to finish Confluence installation or there are issues during runtime and the following errors appear in the atlassian-confluence.log
command denied to user 'confluencedbuser'@'localhost' for table 'CONTENTPROPERTIES'
Environment
- Confluence Server/Data-Center all versions
- MySQL DB all versions
Diagnosis
The installation has failed. Analyzing the atlassian-confluence.log, the following errors may appear:
INFO [http-nio-8090-exec-3] [atlassian.plugin.manager.DefaultPluginManager] earlyStartup Plugin system earlyStartup finished in 0:01:47.369
ERROR [http-nio-8090-exec-3] [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create table CONTENTPROPERTIES (PROPERTYID bigint not null, PROPERTYNAME varchar(255) not null, STRINGVAL varchar(255), LONGVAL bigint, DATEVAL datetime, CONTENTID bigint, primary key (PROPERTYID))
-- referer: <ServerBaseURL>/setup/setupstandarddb-start.action?database=mysql | url: /setup/setupstandarddb.action | userName: anonymous | action: setupstandarddb
execute CREATE command denied to user 'confluencedbuser'@'host' for table 'CONTENTPROPERTIES'During runtime, analyzing the atlassian-confluence.log, the following errors may appear
Caused by: java.lang.RuntimeException: Could not get unique fields for table 'AO_21F425_MESSAGE_AO'
.....
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'confluencedbuser'@'host' for table 'AO_21F425_MESSAGE_AO'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)If we see this error during runtime, verify the privileges/roles of the database or database user with:
select * from mysql.db where db='confluencedb';
SHOW GRANTS FOR confluencedbuser'@'host;
If these are correct but the problem persists, it was identified that using an unsupported/incompatible MySQL driver may present a problem with the AO tables and such errors will be present. Changing to a supported/compatible driver may solve the problem.
Cause
The database user account has insufficient privileges to Confluence Database.
An incompatible/unsupported database driver is in use.
Resolution
Grant the appropriate permissions to the Confluence Database user, as per our Database Setup For MySQL documentation:
GRANT ALL PRIVILEGES ON confluencedb.* TO 'confluencedbuser'@'localhost' IDENTIFIED BY 'confluencepass';
flush privileges;- If Confluence is not running on the same server, replace localhost with the hostname or IP address of the Confluence server. For wildcard use '%'
- Updates are required based on the database, user, and hosts of the MySQL database
Verify if the driver used by Confluence is supported and compatible with the MySQL database version.