Bug #93042 Unknown system variable 'query_cache_size'

• Submitted: 1 Nov 2018 11:06
• Modified: 8 Nov 2018 11:14
• Reporter: tingfang he
• Status: Can't repeat
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: mysql 8.0.13
• OS: Windows (windows7)
• CPU Architecture: Any

[1 Nov 2018 11:06] tingfang he

Description:
When i try to use kettle 7.1.0.0-12 to connect with mysql 8.0.13,i download jar package from website https://dev.mysql.com/downloads/connector/j/ and I choose Platform Independent (Architecture Independent), ZIP Archive,i put it in the right directory which is the kettle installation directory.However,when I try to connect mysql，there is an error that is Unknown system variable 'query_cache_size'.The solution on the internet is to uninstall the latest version of mysql and install the lower version.

How to repeat:
I was wondering if I could not uninstall and reinstall a lower version of mysql but can solve the bug.

Suggested fix:
no suggestion.

[8 Nov 2018 11:14] MySQL Verification Team

Hello!

Thank you for the report.
I'm not seeing reported issue with latest C/J version i.e with C/J 8.0.13 or at least with C/J 5.1.43.  This issue was fixed in Bug#26399958 and per Connector/J 5.1.43 changelog: Connecting to MySQL Server 8.0.3 using Connector/J resulted in an 
SQLException: Unknown system variable 'query_cache_size', because the query 
cache has been removed from the server. All query cache server variables and 
functionality has now been removed from Connector/J 5.1.".

-- MySQL Server 8.0.13, C/J version 5.1.47

##
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'ushastry'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql123';
Query OK, 0 rows affected (0.09 sec)

mysql> grant all on *.* to 'ushastry'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

## Sample test case

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *
 * @author umshastr
 */
public class Bug93042 {

    /**
     * @param args the command line arguments
     */
        public static void main(String[] args) {
                try {
             //           Class.forName("com.mysql.cj.jdbc.Driver");
                        Class.forName("com.mysql.jdbc.Driver");
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
                try {
                        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:9999/test?user=ushastry&password=mysql123&characterEncoding=UTF-8&useSSL=false");
                        DatabaseMetaData meta = con.getMetaData();
                        System.out.println(meta.getDriverVersion());
                        System.out.println(meta.getDatabaseProductVersion());
                } catch (SQLException e) {
                        e.printStackTrace();
                }
        }

    
}

run:
mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 )
8.0.13
BUILD SUCCESSFUL (total time: 4 seconds)

-- MySQL Server 8.0.13, C/J version 8.0.13

run:
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
mysql-connector-java-8.0.13 (Revision: 66459e9d39c8fd09767992bc592acd2053279be6)
8.0.13
BUILD SUCCESSFUL (total time: 3 seconds)

-- MySQL Server 8.0.13, C/J version 5.1.42

run:
java.sql.SQLException: Unknown system variable 'query_cache_size'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3766)
	at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3229)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2238)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at Bug93042.main(Bug93042.java:30)
BUILD SUCCESSFUL (total time: 2 seconds)

Thanks,
Umesh