StreamSets

StreamSets

Connect with experts and peers to elevate technical expertise, solve problems and share insights.

 View Only
Back to discussions
Expand all | Collapse all

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

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

    Posted Sun March 12, 2023 09:27 AM

    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