MySQL Bugs: #54645: Connecting to MySql-Server from Java-Application

Bug #54645	Connecting to MySql-Server from Java-Application
Submitted:	20 Jun 2010 18:35	Modified:	23 Jul 2010 9:01
Reporter:	Patrick Miege	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.48 Community	OS:	Windows (Server 2003 R2 x64)
Assigned to:		CPU Architecture:	Any
Tags:	long delay at first connection to mysql-server

Description:
We have installed a new mysql server. This server is accessed externally. The connection accepts a Java application via webstart is running.

We run this application on the internal network of the server, the connection takes about 300ms.

If you connect to external mysql server with the request for this association takes about 30 sec.

The name xxxx.dyndns.org is dissolved by the java application correctly. Enter the external IP of the router also brought no improvement.

In the my.ini was already commanding the - skip-name-resolve and the - skip-host-cache what brought no improvement.

Firewall is completely OFF!

Excerpt my.ini:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

skip-name-resolve
skip-host-cache

How to repeat:
JAVA CODE --  USING mysql-connector-java-5.0.8

import java.sql.*;
/**
 *
 * @author p.miege
 */
public class Main {

    public static void main( String[] args )
    {
        long tstamp = System.currentTimeMillis();

        String url = "jdbc:mysql://miege.dyndns.org:3306/test";
        String drv = "com.mysql.jdbc.Driver";
        String usr = "newuser";
        String pwd = "newuser";

        if( args.length > 3 )
        {
              url = args[0];
            drv = args[1];
            usr = args[2];
            pwd = args[3];
        }

        // Connect to MySQL Server

        try
        {
            Class.forName( drv );

            System.out.println( "Loading of driver class in: " + (System.currentTimeMillis() - tstamp) );
        }
        catch ( ClassNotFoundException e )
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return;
        }

        try
        {
            Connection dbCon = DriverManager.getConnection(url,
                usr,
                pwd );

            

            System.out.println( "Connecting to server in: " + (System.currentTimeMillis() - tstamp) );
        }
        catch ( SQLException ex )
        {
            // TODO Auto-generated catch block
            ex.printStackTrace();
        }
    }

}

Suggested fix:
7.5.11. How MySQL Uses DNS

When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the host name is in the host name cache. If not, the thread attempts to resolve the host name:

    *

      The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.
    *

      If the operating system supports the thread-safe gethostbyaddr_r() and gethostbyname_r() calls, the thread uses them to perform host name resolution.
    *

      If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve host names that are not in the host name cache until the first thread unlocks the mutex.

You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP numbers in the MySQL grant tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default value: 128) and recompiling mysqld.

You can disable the host name cache by starting the server with the --skip-host-cache option. To clear the host name cache, issue a FLUSH HOSTS statement or execute the mysqladmin flush-hosts command.

To disallow TCP/IP connections entirely, start mysqld with the --skip-networking option.

Thank you for the report.

Do you experience same issues if connecting to remote server via MySQL command line client? Do you get performance improvement if change hostname with IP address in the Java program?

Do you experience same issues if connecting to remote server via MySQL command line
client? -->

How can I create a remote connection from the external command line? I use the MySQL Workbench and talk to the IP using dyndns.org, but also takes a long time.

Do you get performance improvement if change hostname with IP address in the Java
program? -->

The name is in the program for ip adrese resolved and working. See the source code.
The result is no different whether the address or the host name registered with the connection

try
        {
	java.net.InetAddress inetAdd = java.net.InetAddress.getByName("xyz.dyndns.org");
	ip = inetAdd.getHostAddress();
        }
        catch(java.net.UnknownHostException uhe)
        {
	//handle exception
        }

        Connection conn = null;
        String usernameDB = "newuser";
        String passwordDB = "newuser";

        String url = "jdbc:mysql://" + ip + ":3306/" + jComboBox1.getSelectedItem();

Thank you for the feedback.

> How can I create a remote connection from the external command line? I use the MySQL
> Workbench and talk to the IP using dyndns.org, but also takes a long time.

You can use --host and --port options. But your test shows WB has same issues as Java application.

In the code you provided is not clear if MySQL connection or java.net.InetAddress.getByName("xyz.dyndns.org"); takes time.

Please modify code as follow:

test.java:
//necessary headers
class test
{
public static void main(){
try
        {
	java.net.InetAddress inetAdd = java.net.InetAddress.getByName("xyz.dyndns.org");
	ip = inetAdd.getHostAddress();
        System.out.println(ip);
        }
        catch(java.net.UnknownHostException uhe)
        {
	//handle exception
        }
}
}

Compile and run this test.class, wrtie ip somewhere

Now take your program and replace expression

        String url = "jdbc:mysql://" + ip + ":3306/" + jComboBox1.getSelectedItem();

with ip, then try to connect. See if there is any difference.

Alternatively you can use WB or MySQL command line client to test numerical IP.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".