Upgrade failure 7.4.11 t0 7.13.2

[COP Help Desk]

Environment: CentOS 8, 1CPU/4GB VMWare VM, Mysql8.0.17 from CentOS repo.

log says 'Upgrade succeeded' but Confluence shows an error page trying to log in:

Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@56c2df90 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)

Does this mean I need to grant SUPER privilege to the Confluence  DB user?

Full info from log for the system:

Server Information

--------------------------

Application Server: Apache Tomcat/9.0.45

Servlet Version: 4.0

--------------------------

Database Information

--------------------------

Database Dialect: com.atlassian.confluence.impl.hibernate.dialect.MySQLDialect

Database URL: jdbc:mysql://localhost/confluence

Database Driver Name: com.mysql.jdbc.Driver

Database Driver Version: mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf)

Database Name: MySQL

Database Version: 8.0.17

Database Latency (ms): 0

--------------------------

System Information

--------------------------

date = Wednesday, 3 November 2021

jvmInputArguments = --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.util.concurrent=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED -Djava.util.logging.config.file=/opt/atlassian/confluence/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 -Datlassian.plugins.startup.options= -Dorg.apache.tomcat.websocket.DEFAULT_BUFFER_SIZE=32768 -Dconfluence.context.path= -Djava.locale.providers=JRE,SPI,CLDR -Dsynchrony.enable.xhr.fallback=true -Datlassian.plugins.enable.wait=300 -Djava.awt.headless=true -Xloggc:/opt/atlassian/confluence/logs/gc-2021-11-03_11-25-20.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=5 -XX:GCLogFileSize=2M -Xlog:gc+age=debug:file=/opt/atlassian/confluence/logs/gc-2021-11-03_11-25-20.log::filecount=5,filesize=2M -XX:G1ReservePercent=20 -XX:+UseG1GC -XX:+ExplicitGCInvokesConcurrent -XX:+PrintGCDateStamps -XX:+IgnoreUnrecognizedVMOptions -XX:ReservedCodeCacheSize=256m -Xms1024m -Xmx1024m -Dignore.endorsed.dirs= -Dcatalina.base=/opt/atlassian/confluence -Dcatalina.home=/opt/atlassian/confluence -Djava.io.tmpdir=/opt/atlassian/confluence/temp 

workingDirectory = /opt/atlassian/confluence/bin

javaVersion = 11.0.12

javaSpecificationVersion = 11

operatingSystem = Linux 4.18.0-147.5.1.el8_1.x86_64

operatingSystemVersion = 4.18.0-147.5.1.el8_1.x86_64

appServer = Apache Tomcat

javaVm = OpenJDK 64-Bit Server VM

javaRuntime = OpenJDK Runtime Environment

operatingSystemArchitecture = amd64

fileSystemEncoding = UTF-8

jvmInputArgumentsFiltered = -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=5 -XX:GCLogFileSize=2M -XX:G1ReservePercent=20 -XX:+UseG1GC -XX:+ExplicitGCInvokesConcurrent -XX:+PrintGCDateStamps -XX:+IgnoreUnrecognizedVMOptions -XX:ReservedCodeCacheSize=256m -Xms1024m -Xmx1024m

systemLanguage = en

jvmVendor = Oracle Corporation

javaVendor = Eclipse Foundation

userName = confluence

tempDirectory = /opt/atlassian/confluence/temp

jvmImplementationVersion = 11.0.12+7

jvmVersion = 11

favouriteCharacter = White Rabbit

favouriteColour = Celadon

time = 11:32:31

systemTimezone = America/Phoenix

operatingSystemName = Linux

[Brant Schroeder]

@COP Help Desk 

I believe that the issue you are having is similar to this.  https://confluence.atlassian.com/confkb/install-upgrade-confluence-7-11+-fails-with-error-you-do-not-have-the-super-privilege-and-binary-logging-is-enabled-1044096915.html

The database user needs to be allowed to create and alter stored functions.  I think you can make the change provided in the KB to rectify the issue.

[COP Help Desk]

That didn't fix it, in fact it made things worse, as the website stopped responding entirely instead of showing the error.

I even granted the confluence mysql account with SUPER privileges, which finally  got rid of the SQL errors in the logs, but then the log filled with connection Read timed out errors:

2021-11-03 15:12:41,487 ERROR [AtlassianEvent::CustomizableThreadFactory-1] [renderer.internal.http.HttpClientFetcher] fetch Unable to perform a request to: https://confluence.pharmacy.arizona.edu:8443/rest/gadgets/1.0/g/messagebundle/en_GB/gadget.common%2Cgadget.confluence

org.apache.http.conn.ConnectTimeoutException: Connect to confluence.pharmacy.arizona.edu:8443 [confluence.pharmacy.arizona.edu/10.128.206.210] failed: Read timed out

followed by about 70 at ... lines.

Going to revert to the pre-update snapshot, and try again.

[Brant Schroeder]

@COP Help Desk 

Did you revert and try again with the new permission set or just change the permissions and start your instance?  

[COP Help Desk]

Reverted to pre upgrade, then changed permissions, then re-installed the upgrade, etc. 

I'm just going to build out a new VM, install as new then import everything from the old one. It's just a little 10-license server we use for our own internal documentation, but we do refer t it a lot so I need it to be up.

Did that a couple years ago but that was going from an ancient version to 7.4 I've done minor updates without an issue, don't know what's going wrong with this one.

[COP Help Desk]

In the end, what worked was installing Confluence as a new install on the VM, exporting an xml backup from the old server, restoring it on the new server, which worked without a problem.