MySQL Bugs: #65618: JDBC connection takes several minutes behind router.

Bug #65618	JDBC connection takes several minutes behind router.
Submitted:	14 Jun 2012 13:30	Modified:	27 Aug 2012 13:48
Reporter:	Justin Moen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.1.20	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
I am establishing a connection to a MySQL server from a Java Applet using the JDBC Connector as follows:

Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://205.178.146.104:3306/ex_ttekdb?user=notDisclosed&password=fatChance&SelectMethod=direct&responseBuffering=adaptive");

The connection code is inside a worker thread.  While the worker thread is executing a message is displayed, "Establishing connection to database.  Please wait...".

The connection takes several minutes if the user is behind a router.  
How can I speed up this connection or workaround this issue?

How to repeat:
The applet is located at http://www.thermotekusa.com/doctor/.  On some computers(IP addresses) the connection takes 10-15 seconds.  On others the connection can take 1-15 minutes.

Suggested fix:
I suspect that this problem could be resolved by starting the mysql server with the "skip-name-resolve" parameter, however, the host/admin is a web hosting company.  Asking the admin to start the server with this option may cause problems for their other clients.

For me your URL opened in less than 5 seconds. How comes that problems of (reverse?) DNS lookup or something like that is a bug in Connector/J?

[14 Jun 15:28] Valeriy Kravchuk 
For me your URL opened in less than 5 seconds. How comes that problems of (reverse?) DNS lookup or something like that is a bug in Connector/J?

Because I only have the connection hang with this JDBC driver.  PHP connection to MySQL is working instantly.

If it is not a bug with the connector then it is a bug with the server.  Either way, the connection hangs for several minutes for the majority of my clients.

Because of the security manager, to ensure your connection is being made to where the user believes it to be, Java is making reverse DNS lookups on the client side that PHP is probably not making. I'm guessing these are timing out.

I disable the Java Security Manager for this applet and still have the same issue.

Please, send the output of:

show variables like 'general%';
select version();

from your MySQL server. Other possible reason is that Connector/J sends some queries upon connection that may be slow. It would be nice to have general query log enabled to capture these queries and check.

show variables like 'general%';
select version();

yields...

5.0.91-log

I cannot access the logs.  Does the connector send a query with the connection routine?

This problem continues to plaque my Java Applet.

If I run the Applet from NetBeans IDE the initial connection to MySQL server takes 1-2 seconds.  If I run the Applet from an external server, the initial connection takes 2-4 minutes.  The problem must be in the connector and it must be a security setting.  Any ideas?

Justin, just a thought, but another difference between Java and PHP is in that PHP does not have notion of charsets. Can you please check on your charset_server and charset_client server parameters and peek into c/J log to see what's happening by setting, say, profileSQL or traceProtocol connection parameters.

Log file for mysqlconnect.htm

Attachment: mysqlconnect.log (text/plain), 102.82 KiB.

The character set client and server are set to utf8.  I have the program stripped down to just a connection and one short select statement.

Here are the log results of jdbc:mysql://205.178.146.104:3306/ex_ttekdb?user=someuser&password=blahblah&traceProtocol=true&characterSetResults=UTF-8&profileSQL=true" ran from NetBeans.

Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 47 ms, connection-id: 50531881, statement-id: 1, resultset-id: 1, message: /* mysql-connector-java-5.1.20 ( Revision: tonci.grgin@oracle.com-20111003110438-qfydx066wsbydkbw ) */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 = 'character_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 = '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_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 47 ms, connection-id: 50531881, statement-id: 1, resultset-id: 1
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 47 ms, connection-id: 50531881, statement-id: 1, resultset-id: 2, message: /* mysql-connector-java-5.1.20 ( Revision: tonci.grgin@oracle.com-20111003110438-qfydx066wsbydkbw ) */SELECT @@session.auto_increment_increment
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 50531881, statement-id: 1, resultset-id: 2
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 62 ms, connection-id: 50531881, statement-id: 2, resultset-id: 3, message: SHOW COLLATION
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 50531881, statement-id: 2, resultset-id: 3
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 203 ms, connection-id: 50531881, statement-id: 999, resultset-id: 0, message: SET autocommit=1
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 50531881, statement-id: 999, resultset-id: 0
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 109 ms, connection-id: 50531881, statement-id: 999, resultset-id: 0, message: SET sql_mode='STRICT_TRANS_TABLES'
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 50531881, statement-id: 999, resultset-id: 0
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [QUERY] 	at mysqlconnect.MySQLconnect$DatabaseConnection.doInBackground(MySQLconnect.java:56) duration: 93 ms, connection-id: 50531881, statement-id: 3, resultset-id: 4, message: SELECT ModelName FROM Firmware WHERE ModelPartNumber='0P9PTVASCU'
Fri Jun 29 10:34:02 CDT 2012 INFO: Profiler Event: [FETCH] 	at mysqlconnect.MySQLconnect$DatabaseConnection.doInBackground(MySQLconnect.java:56) duration: 0 ms, connection-id: 50531881, statement-id: 3, resultset-id: 4

That connection was relatively fast.  Now the log results running the same applet in a browser from an external website are attached.

You can probably read this same information in your Java Console by visiting, www.thermotekusa.com/doctor/mysqlconnect.htm.

Solved my issue with this.  The problem is with an older version of Netbeans and how it packages and indexes included jar files.  I found the answer here:

http://stackoverflow.com/questions/5770807/java-webstart-slow-requesting-libraries-from-in...