/
Troubleshooting SQL query macro errors with MySQL 8.0 in Confluence Data Center

Troubleshooting SQL query macro errors with MySQL 8.0 in Confluence Data Center

This article provides troubleshooting steps for the java.sql.SQLException: Unknown system variable 'tx_isolation' error encountered when using the SQL for Confluence app with MySQL 8.0 databases.

Summary

After upgrading Confluence or the SQL for Confluence app, users may encounter errors when rendering the sql-query macro. This typically occurs when connecting to a MySQL 8.0 database because the system variable tx_isolation was renamed to transaction_isolation in MySQL 8.0.3 and removed in MySQL 8.0.26.

This article covers two primary scenarios:

  1. Scenario 1: The connection fails with an "Unknown system variable 'tx_isolation'" error.

  2. Scenario 2: The connection fails with an "Unknown initial character set index '255'" error.

Scenario 1: unknown system variable 'tx_isolation'

Problem

The sql-query macro fails to render with the following error:

Error rendering macro 'sql-query' Unexpected program error: java.sql.SQLException: Unknown system variable 'tx_isolation'

This happens because older versions of the MySQL JDBC driver (Connector/J) attempt to query the tx_isolation variable, which no longer exists in MySQL 8.0.

Solution

To resolve this, you'll need to update the MySQL JDBC driver to a version compatible with MySQL 8.0 and adjust the connection profile.

Step 1: update the JDBC driver

Use a version of the MySQL connector that matches your database version (for example, 8.0.26).

  1. Download the appropriate JAR file (for example, mysql-connector-java-8.0.26.jar).

  2. In the SQL for Confluence profile configuration, update the Driver JAR URL to point to the new JAR.

Step 2: update the connection string

If you're using a version of MySQL where tx_isolation is deprecated but not yet removed, you can explicitly set the isolation level in the connection string.

Connection string format:

jdbc:mysql://<hostname>:<port>/<database_schema>?sessionVariables=transaction_isolation='REPEATABLE-READ'

Scenario 2: unknown initial character set index '255'

Problem

When attempting to connect to MySQL 8.0, the "Test Connection" fails with the following error:

Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.

This occurs when the MySQL server is configured with a default character set (like utf8mb4) that the older JDBC driver doesn't recognize.

Solution

Explicitly define the character encoding in your connection string.

Updated connection string:

jdbc:mysql://<hostname>:<port>/<database_schema>?characterEncoding=utf8

If you're combining this with the isolation fix from Scenario 1, use:

jdbc:mysql://<hostname>:<port>/<database_schema>?sessionVariables=transaction_isolation='REPEATABLE-READ'&characterEncoding=utf8

Verification commands

To confirm your database version and current isolation settings, run the following queries on your MySQL instance:

Check version:

show variables like '%version%';

Check isolation level:

show variables like '%isolation%';