Install/Upgrade Confluence 7.11+ fails with 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
Upon installing or upgrading your Confluence server to 7.11+, using MySQL 5.7/8, the installation/upgrade fails with the following error:
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)Environment
- Confluence Server or Data Center 7.11+
- MySQL 5.7 / MySQL 8
Diagnosis
On the import of the function or trigger to MySQL database, the following error arises:
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)Cause
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.
Solution
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 again.
- Start Confluence again.
MySQL in AWS
For MySQL instance running in AWS RDS, we will need to make the above parameter change in parameter group.
Update the value to 1 and then associate the parameter group with the relevant database.
MySQL in Azure
For 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.