MySQL Bugs: #72712: No way to configure Connector JDBC to not do extra queries on connection

Bug #72712	No way to configure Connector JDBC to not do extra queries on connection
Submitted:	21 May 2014 19:54	Modified:	7 Jul 2014 16:49
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.1.30	OS:	Any
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
Connector/JDBC does a lot of extra queries to the DB when you create a connection.  This is horrible when you have long distance connections with high latency.  

I would like someway to be able to configure both MySQL and the Connector to have zero roundtrips.

Two things are required for this to be true:

1.  Don't invalidate the server config cache on errors.
2.  Create someway to avoid the SET NAMES.

How to repeat:
Use test case:  http://p.defau.lt/?Uw2u2mNu_7ma_aV4n4rI1A

Add parameters to avoid roundtrips to the DB on connect time:

cacheServerConfiguration=true
useLocalSessionState=true
jdbcCompliantTruncation=false
characterSetResults=Cp1252

Still have an extra roundtrip and 2 additional extra when you get an SQL STATE 08 error.

Suggested fix:
Let us configure the JDBC driver and MySQL server the same so the JDBC driver doesn't need to read anything from the DB at connection time.  We control both, let us configure both to avoid the roundtrips.  

Also, please make it easier to use.  Requiring so many options to do a simple task is a poor user experience.

Harrison,

Thanks for your bug report, we are working on solution.

Added the following item to the Connector/J 5.1.32 changelog:

"Trying to use any character sets other than UTF-8 for communications between client programs and the MySQL server caused Connector/J to perform extra queries after the initial connection, resulting in higher latency and overhead for the connection. To prevent extra queries, this fix eliminates the mechanism of setting the character set to values other than 'utf-8' by issuing a SET NAME statement to the server, and allows the use of the connection property characterEncoding to set the character set value in Connector/J's response packet during handshake."