Install/Upgrade of Confluence fails with MySQL error 'You do not have the SUPER privilege and binary logging is enabled'
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
An install or upgrade of Confluence Data Center version 7.11 or newer fails due to a MySQL 5.7/8.x configuration error.
Error log detail
2021-02-06 20:58:24,363 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@c8e620 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER denormalised_space_trigger_on_update
AFTER UPDATE
ON SPACES FOR EACH ROW
sp: BEGIN
DECLARE isServiceDisabled BOOL DEFAULT TRUE;You do not have the SUPER privilege and binary logging is enabled
CALL space_procedure_for_denormalised_permissions(isServiceDisabled);
IF (isServiceDisabled) THEN
LEAVE sp;
END IF;
IF (NEW.LOWERSPACEKEY = OLD.LOWERSPACEKEY) THEN
LEAVE sp;
END IF;
INSERT INTO DENORMALISED_SPACE_CHANGE_LOG(SPACE_ID)
VALUES (NEW.SPACEID);
END;]; SQL state [HY000]; error code [1419]; You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable); nested exception is java.sql.SQLException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@c8e620 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER denormalised_space_trigger_on_update
AFTER UPDATE
ON SPACES FOR EACH ROW
sp: BEGIN
DECLARE isServiceDisabled BOOL DEFAULT TRUE;
CALL space_procedure_for_denormalised_permissions(isServiceDisabled);
IF (isServiceDisabled) THEN
LEAVE sp;
END IF;
IF (NEW.LOWERSPACEKEY = OLD.LOWERSPACEKEY) THEN
LEAVE sp;
END IF;
INSERT INTO DENORMALISED_SPACE_CHANGE_LOG(SPACE_ID)
VALUES (NEW.SPACEID);
END;]; SQL state [HY000]; error code [1419]; You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable); nested exception is java.sql.SQLException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)Solution
The Confluence database user is not allowed to create and alter stored functions, because the parameter log_bin_trust_function_creators = 1 is missing from the [mysqld] section of the my.cnf file, as described in Database Setup For MySQL documentation. To prevent this error, add the parameter.
Self-hosted MySQL
To allow the installation/upgrade to complete successfully, we need to specify the following parameter under the [mysqld] section of the my.cnf file, as explained in our documentation Database Setup For MySQL, as follows:
- Stop Confluence
- Stop the MySQL database
Specify the below parameter under [mysqld] in your my.cnf file:
log_bin_trust_function_creators = 1- Start the MySQL database
- Start Confluence
MySQL in AWS
- For a MySQL instance running in AWS RDS, we will need to make the above parameter change in a parameter group.
- Update the value to 1 and then associate the parameter group with the relevant database.
MySQL in Azure
- For a MySQL instance running in Azure, we will need to make the above parameter change in Server parameters.
- Update the value to ON then save the change.
Environment
- Confluence Data Center 7.11+
- MySQL 5.7 / MySQL 8