Collaborative Editing stops working after upgrading the database to MySQL 8
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
After upgrading from MySQL 5.x to MySQL 8 and adding the JDBC driver mysql-connector-java-8.x.x to the <confluence-install>/confluence/WEB-INF/lib directory, Synchrony is unable to start.
The following error is exhibited on the Synchrony logs:
2020-03-26 14:47:56,188 DEBUG [235:StdOutHandler [D:\Confluence\jre\bin\java.exe]] 2020-03-26 18:47:56,185 WARN [C3P0PooledConnectionPoolManager[identityToken->1bqxqnra96pdvmq6v1k2z|6971c6b7]-HelperThread-#2] [mchange.v2.resourcepool.BasicResourcePool] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@53324d4 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
2020-03-26 14:47:56,188 DEBUG [235:StdOutHandler [D:\Confluence\jre\bin\java.exe]] java.sql.SQLException: Unknown system variable 'query_cache_size'Because of this, the Collaborative Editing feature stops working.
Environment
Confluence Server 7.2+
MySQL 5.6.28 upgraded to 8.0.19
Diagnosis
Reviewing the last startup logs, it's possible to see that Confluence is using the driver name com.mysql.jdbc.driver:
2020-03-26 14:46:24,298 INFO [Catalina-utility-1] [confluence.admin.actions.SystemInfoOnStartup] startup
...
Database Information
--------------------------
Database Dialect: com.atlassian.confluence.impl.hibernate.dialect.MySQLDialect
Database URL: jdbc:mysql://localhost/confluence?useSSL=false
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.19Reviewing the same startup logs, it's also possible to check the Synchrony is not using the new JDBC driver, but the older one used for MySQL 5.x:
49222:2020-03-26 14:45:51,593 INFO [synchrony-interop-executor:thread-1] [plugins.synchrony.bootstrap.DefaultSynchronyProcessManager] debugPrintEnvironment D:\Confluence\jre\bin\java.exe -classpath D:\Confluence\temp\3.1.0-master-0f086db5.jar;D:\Confluence\confluence\WEB-INF\lib\mysql-connector-java-5.1.39-bin.jar -Xss2048k -Xmx1g synchrony.core sqlCause
Cause 1: Wrong Driver Name
There was a change on the driver class name for MySQL 8. It is now named com.mysql.cj.jdbc.Driver.
Cause 2: It's possible that the older JDBC driver was not deleted
As indicated on the documentation, the older JDBC driver needs to be removed from the <confluence-install>/confluence/WEB-INF/lib directory when adding the new JDBC driver:
Solution
1. Change the Driver Class Name
- Locate and edit the <confluence-home>/confluence.cfg.xml file
Replace this:
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>For this:
<property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>Save the file
- Restart Confluence
2: Make sure the older JDBC driver was deleted
Review the <confluence-install>/confluence/WEB-INF/lib directory and make sure you removed the older JDBC driver