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

[23 Jan 2015 0:30] Domas Mituzas
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;
[23 Jan 2015 12:12] Umesh Shastry
Hello Domas Mituzas,

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

Thanks,
Umesh
[23 Jan 2015 12:15] Umesh Shastry
// 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')
[12 Mar 2015 18:16] Eric Bergen
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.
[28 Apr 2015 15:01] Daniel So
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."
[10 Jul 2015 8:02] Charles Lip
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.
[10 Jul 2015 14:26] Eric Bergen
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.
[13 Jul 2015 1:09] Charles Lip
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.