MySQL connection error while accessing remote server

I have a MySQL DB hosted on a web server... I have granted all privileges and allowed my IP address to remotely connect to this database from my local computer. It connects and I can pull data from the database into a Java Swing application. But sometimes I get this error and my connection to the hosted DB fails. The error is displayed below:

Apr 7, 2012 12:49:20 AM scm.new_fas txtSearchKeyReleased
SEVERE: null
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1207)
    at scm.DBControl.getResult(DBControl.java:49)
    at scm.new_fas.txtSearchKeyReleased(new_fas.java:1686)
    at scm.new_fas.access$2300(new_fas.java:28)
    at scm.new_fas$23.keyReleased(new_fas.java:1136)
    at java.awt.Component.processKeyEvent(Component.java:5999)
    at javax.swing.JComponent.processKeyEvent(JComponent.java:2794)
    at java.awt.Component.processEvent(Component.java:5815)
    at java.awt.Container.processEvent(Container.java:2058)
    at java.awt.Component.dispatchEventImpl(Component.java:4410)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848)
    at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:693)
    at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:958)
    at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:830)
    at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:657)
    at java.awt.Component.dispatchEventImpl(Component.java:4282)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Window.dispatchEventImpl(Window.java:2429)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)


** END NESTED EXCEPTION **



Last packet sent to the server was 2 ms ago.
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2622)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1207)
    at scm.DBControl.getResult(DBControl.java:49)
    at scm.new_fas.txtSearchKeyReleased(new_fas.java:1686)
    at scm.new_fas.access$2300(new_fas.java:28)
    at scm.new_fas$23.keyReleased(new_fas.java:1136)
    at java.awt.Component.processKeyEvent(Component.java:5999)
    at javax.swing.JComponent.processKeyEvent(JComponent.java:2794)
    at java.awt.Component.processEvent(Component.java:5815)
    at java.awt.Container.processEvent(Container.java:2058)
    at java.awt.Component.dispatchEventImpl(Component.java:4410)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848)
    at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:693)
    at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:958)
    at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:830)
    at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:657)
    at java.awt.Component.dispatchEventImpl(Component.java:4282)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Window.dispatchEventImpl(Window.java:2429)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)

      

Can someone please help me in this situation. I don't understand what happened! Is it a server or? Many thanks:)

This is my class for DB connection:

package scm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBControl {

    private static DBControl con;
    private static String severIp = null;
    private static String severPort = null;
    private static String userName = null;
    private static String password = null;
    private static ResultSet rs = null;
    private static Connection cc = null;

    public DBControl() {
    }

    public static synchronized DBControl getInstance() {
        if (con == null) {
            con = new DBControl();
        }
        return con;
    }

    public void setSeverIp(String Ip) {
        severIp = Ip;
    }

    public void setSeverPort(String Port) {
        severPort = Port;
    }

    public void SetUserName(String Name) {
        userName = Name;
    }

    public void setPassword(String passWord) {
        password = passWord;
    }

    public static ResultSet getResult(String url) throws Exception {
        Statement s = cc.createStatement();
        rs = s.executeQuery(url);
        return rs;
    }

    public static Connection getConnection() throws Exception {
//        try {
        Class.forName("com.mysql.jdbc.Driver");
        cc = (Connection) DriverManager.getConnection("jdbc:mysql://" + getSeverIp() + ":" + getSeverPort() + "/anuradha_rr", getUserName(), getPassword());
        return cc;
    }

    public void setResult(String url) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        cc = (Connection) DriverManager.getConnection("jdbc:mysql://" + getSeverIp() + ":" + getSeverPort() + "/anuradha_rr", getUserName(), getPassword());
        Statement s = (Statement) cc.createStatement();
        s.executeUpdate(url);
    }

    public static String getSeverIp() {
        return severIp;
    }

    public static String getSeverPort() {
        return severPort;
    }

    public static String getUserName() {
        return userName;
    }

    public static String getPassword() {
        return password;
    }
}

      

+3


source to share


2 answers


Make sure you are not using a dead jdbc connection. Depending on how you create the jdbc connection, they may be idle. If you are using the same connection over and over, check the connection before trying to issue a query and get a new one if mysql has closed it.

UPDATE:



You create a new connection everywhere except getResult (), where the stack trace identifies the error. You can either create a new connection, as elsewhere, or improve the encapsulation of getConnection (). In other words, change getConnection () to return the static connection stored in your class, provided it is not null and still valid. Otherwise, create a new connection and return it.

+3


source


This is a fairly common problem in MySQL, unfortunately, it usually only happens after it's created, when the application has been running for a while, as opposed to a short development cycle.

MySQL closes connections without notifying its clients, and a not too compelling exception is what appears when trying to use a synchronized connection. you can, for example, periodically bind the connection to some trivial SQL statement to avoid timeout.

It is good practice to use connection pooling (to avoid problems with too many users accessing the DB at once), for example. DBCP in general. DBCP can be added to your application easily and you only install it as a quasi JDBC driver. There you can set the correct path.



I copied this DBCP config from one of my projects:

                 MaxActive=20,
                 MaxWait=60000,
                 TestWhileIdle=true,
                 TimeBetweenEvictionRunsMillis=300000,
                 MinEvictableIdleTimeMillis=300000,
                 TestOnBorrow=true,
                 ValidationQuery='SELECT 1'

      

Update: As a very simple solution, you can try to catch the access exception and open a new connection in that case.

+2


source







All Articles