MySQL JDBC appears with multiple show VARIABLES statements.

A chance, show processlist time, found a Client has been executing "mysql-connector-java-5.1.21 (Revision: ${bzr.revision-id}) */show Varia Bles WHERE variable_name "

After the discussion with the base friends, a little bit of a wisp. Probably the idea is this:

The 1.MySQL JDBC connection process is probably as follows (open the information obtained by General log):

5089492 Connect [email protected] on DB 5089492 Query/* mysql-connector-java-5.1.21 (Revision: ${b  Zr.revision-id}) */show VARIABLES WHERE variable_name = ' language ' or variable_name = ' net_write_timeout ' or variable_name = ' Interactive_timeout ' or variable_name = ' wait_timeout ' or variable_name = ' character_set_client ' or Variable_name = ' C Haracter_set_connection ' or variable_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 = ' Ti Mezone ' or variable_name = ' time_zone ' or variable_name = ' system_time_zone ' or variable_name = ' lower_case_table_names ' O R variable_name = ' Max_allowed_packet ' or variable_name = ' net_buffer_length ' or variable_name = ' sql_mode ' or Variable_na me = ' query_cache_type ' or variable_name = ' query_cache_size ' or variable_name = ' init_connect ' 5089492 Que RY/* mysql-connector-java-5.1.21 (Revision: ${bzr.revision-id}) */select @ @session. auto_increment_increment 5089492 Query SH OW COLLATION 5089492 Query set NAMES utf8mb4 5089492 query Set character_set_results = N ULL 5089492 Query Set autocommit=1 5089492 query set sql_mode= ' Strict_trans_tables '

1) Connect [email protected] on db

2) SHOW VARIABLES WHERE variable_name = ' language ' ... Gets the session level variable that the Client cares about.

3) Get @ @session. auto_increment_increment. Please refer to Https://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment for the function of this parameter. The primary is the interval that controls the value of each increment of the column of the Auto_increment property. For example, the auto_increment_increment can be set to 2 when it grows from 1 3 5 7.

4) followed by the remainder of the operation.

As can be seen from here, if it is a new JDBC connection, it will need this request process, is inevitable. So if we try to reduce the Client's request. The use of DB connection pooling technology is now common.

The same configuration information that happened to be developed is as follows:

<Environmentsdefault="Development">        <Environment ID="Development">            <TransactionManager type="JDBC"/>            <DataSource type="Unpooled">                <Property name="Driver" value="${db.driverclass}"/>                <Property name="url" value="${db.connecturl}"/>                <Property name="Username" value="${db.User}"/>                <Property name="Password" value="${db.pass}"/>            </DataSource>        </Environment>    </Environments>

The final inference is <datasource type= "unpooled" > This parameter setting problem. Where type has three selectable values: type= "[unpooled| pooled| JNDI] "by Http://www.mybatis.org/mybatis-3/configuration.html's interpretation you can see unpooled –this Implementation of Dataso Urce simply opens and closes a connection each time it is requested.

Later let the development change the configuration to Type= "pooled", the test found that the above SQL request will still appear, the frequency will be smaller. It is now understood that, although the connection pool is available, the first time the JDBC connection is established requires the MySQL variables value to be requested, but there is no need to retrieve these values when the connection is reused later.

Conclusion:

1). Connect to the database through JDBC, as far as possible with the connection pool, or each access will require additional access to variables, is also very resource-intensive.

2). Connection pooling the first time the connection is established is generally as follows, with different JDBC versions, there may be slightly different requests.

3). This is a normal phenomenon.

One more question: The following command is flooded with MySQL slow query log. Some say that it is the client to test and database connectivity. But can it be tested in a different way? Using this SQL will make my slow query log all of this stuff, very annoying.

# time:161122 11:21:01# [email protected]: db[db] @  [ip]# query_time:0.000018  lock_time:0.000000 rows_sent:0
   rows_examined:0 logical_reads:0 physical_reads:0set timestamp=1479784861;# Administrator command:ping;

　　

MySQL JDBC appears with multiple show VARIABLES statements.