Hi all, I’‘ve setup a wildfire server but while we’'re testing, it doesnt have any traffic,
SO mysql connections in ConnectionPool timeout (yes, I’'ve tried to tune /etc/my.cnf:wait_timeout=xxx).
The question is after “wait_timeout” in mysql, the server closes the connections, and WildFire doesn’'t realize it… so whenever DbConnectionManager/Pool is asked for a Connection, the connection will be likely closed, so the SQL query WILL_NOT succeed, even if the database is all right
Ive tried with the autoReconnect=true parameter, but it only improves the behaviour a little bit (you get a closed Connection every two ‘‘getConnection’’’'s)
I’'ve done a quick-hack to solve this behaviour in WildFire 3.2.2 (attached patch), but its a quick hack, the best would be someone to implement “database.defaultProvider.checkOpenConnection” because is marked “TODO” in
http://wiki.igniterealtime.org/display/WILDFIRE/Wildfire+Properties
These are some interesting links for anyone affected by the same issue:
http://grid.tsinghua.edu.cn/docs/mysql-jdbc/#cj-implementation-notes
(about java.sql.Connection.isClosed() )
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
(about mysql configuration variables, ie. wait_timeout and autoReconnect)
http://wiki.igniterealtime.org/display/WILDFIRE/Wildfire+Properties
(The wildfire todo properties)
(The same issue but in JBoss/FDS/Hibernate, and how it was solved. Hint: copy the solution from JBoss to wildfire )
It would be very nice to have this fixed in next release
thanks to all, and best regards
Elias Baixas
============== Tha dirt-hack patch =================
diff -c3 src/wildfire_src_3_2_2/wildfire_src/src/java/org/jivesoftware/database/DbConnec tionManager.java workspace/wildfire_3_2_2/src/java/org/jivesoftware/database/DbConnectionManager .java
- src/wildfire_src_3_2_2/wildfire_src/src/java/org/jivesoftware/database/DbConnect ionManager.java 2007-02-20 00:52:13.000000000 +0100
— workspace/wildfire_3_2_2/src/java/org/jivesoftware/database/DbConnectionManager .java 2007-03-02 18:14:31.000000000 +0100
- 90,95 ****
— 90,108 -
}
}
Connection con = connectionProvider.getConnection();
-
boolean haveConnection=false;
-
int maxTries = 10;
-
while(!haveConnection && maxTries>0){
-
try{
-
PreparedStatement s =con.prepareStatement("SELECT 1");
-
s.execute();
-
haveConnection = true;
-
}catch(SQLException ex){
-
maxTries--;
-
con.close();
-
con = connectionProvider.getConnection();
-
}
-
}
if (con == null) {
Log.error("WARNING: ConnectionManager.getConnection() " +
IdleConnectionDetectorTestcase.zip (3578 Bytes)