Not being able to connect to MySql database

Question

I've had more problems with my database in the past: It could not always get connection. The database runs on a website (webhosting), and I try to access it from my own PC. Here things go wrong, if I access it from localhost to localhost then it works okay.

Error that I get in Java: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

I got no clue why, I'm using similar structure as ever, namely the following:

public class SQL {
    private final static String USERNAME = "";
    private final static String PASSWORD = "";
    private final static String URL = "jdbc:mysql://www.fvheeswijk.nl:3306/p28004_bf4";

    private static Connection cachedConnection;

    private static void createConnection() {
        cachedConnection = null;
        Properties connectionProperties = new Properties();
        connectionProperties.put("user", USERNAME);
        connectionProperties.put("password", PASSWORD);
        try {
            cachedConnection = (Connection) DriverManager.getConnection(URL, connectionProperties);
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static Connection getConnection() {
        if (cachedConnection == null) {
            createConnection();
        }
        return cachedConnection;
    }
}

The data is blanked out of course.

I then tried to ping my website, all fine.

Later I tried to ping www.fvheeswijk.nl:3306, the database, but Windows cmd cannot find it. Then I tried visiting it via the browser (does this even make sense?), but I got some message along the lines of "packets received out of order". And I have already (way before) added my PC's (Router/Network's) host name to the allowed host list of the database.

Any clue or suggestions what is going wrong?

EDIT: Now I am getting this, might explain something... java.sql.SQLException: null, message from server: "Host '541DB0AA.cm-5-6c.dynamic.ziggo.nl' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

The main problem is that you are opening too much connections and probably never closing them or they are being closed by the application server (or wherever you run this application). This can be known from two facts in your post:

private static Connection cachedConnection. The database connection must not be cached manually, instead it should be retrieved only when needed, and closed after being used.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. This error is very explicit, you're trying to use a connection that is closed.

Also, you're naively opening connections manually, this is noted here:

cachedConnection = (Connection) DriverManager.getConnection(URL, connectionProperties);

To solve all these problems, you should move to a database connection pool. In short, the connection pool will open a bunch of physical database connections and keep them alive in sleeping status, and will wake up a connection on demand, then when closing the connection, instead of closing the physical connection, it will be back to the sleeping status, thus saving time for opening a new connection.

More info:

About your last edit, seems that you need to close some connections to your database. You should kill some of them and try to connect again using the database pool instead.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 15 '13 at 17:00

Luiggi Mendoza

85,076
16
154
332

Okay I will definately take a look at it as there have always be *some* issues with this, though dependant on the application they may be more severe. I'm not using any container environment though, so I hope there are simple implementations of connection pooling aswell. I understand that I was using a connection that was already closed, however I have not been able to do anything with that connection nor do I manually close it, hence my confusion. – skiwi Nov 15 '13 at 17:26
@skiwi in the last link of the more info section of my answer, there are some database connection pooling libraries you can use in your application. – Luiggi Mendoza Nov 15 '13 at 17:31
I'll go with apache commons dbcp, however I have googled for the past minutes, but have been enabled to find a fully comprehensive tutorial for DBCP without using a container environment. Do you happen to know any? – skiwi Nov 15 '13 at 17:48
I for example found a very single simple at: http://www.roseindia.net/tutorial/java/dbcp/ConnectingToMySQL.html Though it seems somewhat fishy, would this code actually use connection pooling? – skiwi Nov 15 '13 at 18:07
@skiwi this is a personal opinion: I don't trust in roseindia.net tutorials for failed about real world examples satisfying both functional and non-functional requirements in the past, in my workplace we use BoneCP (after an analysis of the architecture team) and we don't have any connectivity issue (except when we forgot to close connections, of course). – Luiggi Mendoza Nov 15 '13 at 19:38

Not being able to connect to MySql database

1 Answers1