Solved MySQL autoReconnect problem

Hi,

I have a problem with my MySQL auto reconnect code.
For example, if no players on server in 24hrs, when they will login i'll get error, which means jdbc didn't reconnect automatically to MySQL server, also i try to retrieve mysql connection in every method than include mysql queries.

Here's some part of my code:
http://pastebin.com/YSr56WAQ

 

I encountered that exact problem a few days ago, what happens is that phpmyadmin(or any other mysql management program) automatically releases (not closes!) mysql connections after a set amount of time, usually this goes from 8-24 hours, what you could do is add &autoReconnect=true to the connection url, but you should use the autoReconnect property only when no other way works, since it can cause severe problems when used with jdbc.

One way you could solve it is use connection pools, however that isnt quite recommended since bukkit is single thread. Another way (the way i use) is to initiate a sudo query to the default table all mysql versions come with - the DUAL table, and see if you get a response, if you do, the connection is not idle, if you dont, reconnect.

I contributed my solution to the FruitSQL library, along with some other useful methods, they are all tested and working, you can check it out here, however im not sure if the author updated his post containing the library with my recent updated methods, so i suggest you scroll down and check out my latest post containing the full library.

Enjoy ^-^

 

I do not want to use any libraries. Also u didn't look at my code, i have autoreconnect enabled.

 

Zeluboba I saw, i said i wouldn't recommend it, not that it shouldn't be used, i gave you an alternative, if you still want to use you're own code, you can just copy the refresh connection method in the library, and add it before any method that requires a connection.

 

what library? also, i check if connection is not closed or == null before make a new one. Though, it doesn't work.

 

Zeluboba i know, in my first post i specifically said "automatically releases (not closes!) mysql connections", thus, connection.isClosed() will not work, i even added the doc from the mysql website which regards this exact problem, it explains things quite well.

Just trust me on this one, send a sudo query:

Code:java

public void refreshConnection() {
 
PreparedStatement st = null;
ResultSet valid = null;
try
{
st = connection.prepareStatement("SELECT 1 FROM Dual");
valid = st.executeQuery();
if (valid.next())
return;
} catch (SQLException e2)
{
System.out.println("Connection is idle or terminated. Reconnecting...");
} finally
{
this.closeQuietly(valid);
}
 
long start = 0;
long end = 0;
 
try
{
start = System.currentTimeMillis();
System.out.println("Attempting to establish a connection the MySQL server!");
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
end = System.currentTimeMillis();
System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
System.out.println("Connection took " + ((end - start)) + "ms!");
} catch (SQLException e)
{
System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
} catch (ClassNotFoundException e)
{
System.out.println("JDBC Driver not found!");
}
}

 

That is very strange for me. What is 'dual'?

 

Okay, i suggest you read the first post again, i explain it there, even added in a doc link.

From the wiki:

In normal english - the DUAL table is a column ALL mysql installations come with.
What i did is i usedit to check if there was a response from the mysql server, i used the DUAL table because that saved me the effort of checking for other existing tables, since i know this table comes with EVERY mysql installations.

The query i used is SELECT 1 FROM DUAL, i used SELECT 1 because that would save me the trouble of finding out the type of datatype the table's columns have.

Basically, that query will work on ALL mysql installations, no matter what. Its simply error-proof, and that is why i used it to check if the connection was alive(not closed, alive!), if i got a ResultSet back from the table, i know the connection is alive, if i didint, i simply reconnected.

All clear?

 

Problem solved. Without any duals. Than you, btw.