MySQL Bugs: #75592: "SHOW VARIABLES WHERE" is expensive.

Bug #75592	"SHOW VARIABLES WHERE" is expensive.
Submitted:	23 Jan 2015 0:30	Modified:	28 Apr 2015 15:01
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
this is similar to http://bugs.mysql.com/bug.php?id=27655

on every connection Connector/J will do "SHOW VARIABLES WHERE" which is very inefficient/slow/breaksservers/etc ( see http://bugs.mysql.com/bug.php?id=75590 )

it should not, and use SELECT @@global.... instead

How to repeat:
use connector/J to connect to server, observe server melting

Suggested fix:
SELECT @@variable;

Hello Domas Mituzas,

Thank you for the report.
Confirmed that on every connection Connector/J triggers "SHOW VARIABLES WHERE". 

Thanks,
Umesh

// Test script

import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

class Test1 {
   public static void main(String[] args) {
        Connection conn;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=15000)(user=root)/test";
            conn = DriverManager.getConnection(url, "", "");
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO t1 (created_time) values (?)");
            stmt.setTimestamp(1, new Timestamp(1300883333501L));
            stmt.execute();
        } catch (Exception e) {
            System.out.println("Exception: "+e);
            e.printStackTrace();
        }
    }
}

//

: javac Test1.java

// Run script couple of times and check general log
: java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.34/mysql-connector-java-5.1.34-bin.jar' Test1

// From gen log 

bin/mysqld, Version: 5.6.22 (MySQL Community Server (GPL)). started with:
Tcp port: 15000  Unix socket: /tmp/mysql_ushastry.sock
Time                 Id Command    Argument
..
.                57630 Query     select @@version_comment limit 1
150123 12:59:42 57630 Query     SELECT DATABASE()
                57630 Init DB   test
150123 13:00:11 57630 Query     create table t1(created_time datetime)
150123 13:03:37 57631 Connect   root@localhost on test
                57631 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n
ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va
riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR
 Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n
ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
                57631 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment
                57631 Query     SET NAMES latin1
                57631 Query     SET character_set_results = NULL
                57631 Query     SET autocommit=1
                57631 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                57631 Query     select @@session.tx_read_only
                57631 Query     INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501')
150123 13:04:17 57630 Query     select * from t1
150123 13:06:22 57632 Connect   root@localhost on test
                57632 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n
ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va
riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR
 Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n
ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
                57632 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment
                57632 Query     SET NAMES latin1
                57632 Query     SET character_set_results = NULL
                57632 Query     SET autocommit=1
                57632 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                57632 Query     select @@session.tx_read_only
                57632 Query     INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501')
150123 13:06:25 57633 Connect   root@localhost on test
                57633 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n
ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va
riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR
 Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n
ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
                57633 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment
                57633 Query     SET NAMES latin1
                57633 Query     SET character_set_results = NULL
                57633 Query     SET autocommit=1
                57633 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                57633 Query     select @@session.tx_read_only
                57633 Query     INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501')
150123 13:06:26 57634 Connect   root@localhost on test
                57634 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n
ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va
riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR
 Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n
ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
                57634 Query     /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment
                57634 Query     SET NAMES latin1
                57634 Query     SET character_set_results = NULL
                57634 Query     SET autocommit=1
                57634 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                57634 Query     select @@session.tx_read_only
                57634 Query     INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501')

Somewhere in 5.6 LOCK_plugin_delete was added into the show variables path which reduces the incoming connection rate from connector/j from about ~16,000/s to ~2,500/s. This is a pretty significant performance regression.

Added the following entry to the Connector/J 5.1.36 changelog:

"At every connection, Connection/J executed a SHOW VARIABLES WHERE statement over a multitude of variables, which consumed a lot of time and memory. To improve the efficiency of the code, this fix replaces the statement with the more efficient SELECT @@variable query and also implements some other related changes."

Tested Connector/J-5.1.36 against MySQL-5.0.80, and found this performance-enhancement change in ConnectionImpl is breaking backward-compatibility.

MySQL-5.0.80 is unable to handle "SELECT @@language"; not sure if this is also applicable to all other 5.0.x releases.

The connector knows which version of mysql it is connected to before issuing these queries. You can easily decide to do different things for different versions.

As said: MySQL-5.0.80 is unable to handle "select @@language".

However, in Connector/J-5.1.36, method com.mysql.jdbc.ConnectionImpl.loadServerVariables() triggered by ConnectionImpl.getInstance() call is issuing "select @@language" at around line #3855. This is shown by code snippet:

                    if (!versionMeetsMinimum(5, 5, 0)) {
                        nameToFieldNameMap.put("language", "@@language");
                    }

As a result, JDBC client would hit SQLException on attempt to connect to MySQL-5.0.80 with this driver:-

java.sql.SQLException: Unknown system variable 'language'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432)
        at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java                                                                              :3870)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImp                                                                              l.java:3284)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2                                                                              293)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct                                                                              orAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC                                                                              onstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java                                                                              :325)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(Poole                                                                              dConnection.java:307)

There was no such issue with Connector/J prior to 5.1.36 release.