JDBC stage is failing with "Unknown system variable 'query_cache_size'" when connecting to MySQL server via Proxy SQL.

  • 12 March 2023
  • 0 replies
  • 934 views

Userlevel 4
Badge

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 [user: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

 


0 replies

Be the first to reply!

Reply