Environment:

• StreamSets Data Collector any version.
• MySQL database + ProxySQL LB.
• JDBC producer or JDBC consumer stage.

Issue:

It has been noticed that when connecting to the MySQL server when ProxySQL Load balancer is placed, the pipeline validation fails with following error:

2023-02-22 12:28:12,112 2user:test] ]pipeline:test_mysql/mysqlce] ]runner:] ]thread:preview-pool-16-thread-4] ]stage:] ERROR HikariPool - HikariPool-4 - Exception during pool initialization.
java.sql.SQLException: Unknown system variable 'query_cache_size'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.createDataSourceForRead(JdbcUtil.java:1052)
    at java.lang.Thread.run(Thread.java:750)
Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)

Resolution:

query_cache_size was removed in MySQL 8 however as per the code the variable need to be set.

MySQL Connector/J source code shows:

if (!versionMeetsMinimum(8, 0, 3)) {
queryBuf.append(", @@query_cache_size AS query_cache_size");
queryBuf.append(", @@query_cache_type AS query_cache_type");
}

The workaround is to set the following variables:

update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk