[Fedora-users] MySQL connection timeout problem and solution

[Jochen Schirrwagen]

Hi,

I encountered the following problem with this configuration:
-Fedora1.2.1
-MySQL 4.0.15
-as JDBC-driver: mysql-connector-java-3.0.14

MySQL by default closes idle connections after *8* *hours*.
After that by any new database request the following Exception will be
thrown:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1388)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1532)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1923)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2233)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2181)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1158)
at
fedora.server.storage.lowlevel.DBPathRegistry.get(DBPathRegistry.java:104)
...

I realized that fedora adapted the connectionpool implementation from
http://www.coreservlets.com/.
So far so good, but it is not resistant against the MySQL phenomenon*.
*I would ask the fedora-developers, why not to use the Database
Connection Pool implementation from Apache or the JNDI-Feature in Tomcat
4 and above, where the timeout problem not occurs?

Jochen

[Ross L. Wayland]

Are you setting the autoReconnect parameter on the mysql JDBC driver in fedora.fcfg? This usually will get around the mysql timeout problem.  e.g.,

    <datastore id="localMySQLPool">
        <comment>MySQL database on localhost with db name of fedora12</comment>
        <param name="dbUsername" value="fedoraAdmin"/>
        <param name="dbPassword" value="fedoraAdmin"/>
        <param name="jdbcURL"               value="jdbc:mysql://localhost/fedora12?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true"/>
        <param name="jdbcDriverClass" value="com.mysql.jdbc.Driver"/>
        <param name="ddlConverter" value="fedora.server.utilities.MySQLDDLConverter"/>
        <param name="minPoolSize" value="10"/>
        <param name="maxPoolSize" value="100"/>
    </datastore>

Ross

-- 
________________________________________________________________________

Ross Wayland
Email: r...@virginia.edu                  University of Virginia
Phone: 434-924-0746                    Alderman Library - Systems Office
FAX:   434-924-1431                    Charlottesville, VA 22903-2498

Jochen Schirrwagen wrote:

_______________________________________________
Fedora-users mailing list
Fedora...@comm.nsdl.org
http://comm.nsdl.org/mailman/listinfo/fedora-users

[Jochen Schirrwagen]

Wether or not I set the autoReconnect parameter after 8 hours of
inactivity I got the error message.
That's why I disabled the usage of the fedora connectionpool and use the
DBCP pool in tomcat over JNDI .
It works fine for me.

Jochen

--
Jochen Schirrwagen
Hochschulbibliothekszentrum NRW (HBZ)
Postfach 27 04 51
50510 Köln
tel +49-221-40075-460
fax +49-221-40075-190
url http://www.dipp.nrw.de

[Jochen Schirrwagen]

Annotation:
I think it's more an error in the MySQL-JDBC-driver than in the
connectionpool implementation of Fedora.
On the other side the DBCP-pool of Apache resp. Tomcat seems to be more
robust.

Maybe the problem in the Fedora code is here:
fedora.server.storage.ConnectionPool.java: method getConnection:
if (existingConnection.isClosed()){...}

The Java-API says:
______________________________________________

public boolean *isClosed*()
throws SQLException <cid:part1.06040...@hbz-nrw.de>

Retrieves whether this |Connection| object has been closed. A connection
is closed if the method |close| has been called on it or if certain
fatal errors have occurred. This method is guaranteed to return |true|
only when it is called after the method |Connection.close| has been called.

This method generally cannot be called to determine whether a connection
to a database is valid or invalid. A typical client can determine that a
connection is invalid by catching any exceptions that might be thrown
when an operation is attempted.

______________________________________________

The method doesn't check of a timed-out connection and instead of catching the SQL-exception
it is thrown to the user.



regards,
Jochen

[Ross L. Wayland]

Good catch...

In versions of the mysql-connector after 3.0.8, the way the isClosed()
method is implemented was changed to conform to the latest JDBC spec. In
earlier versions , connection.isClosed() apparently would return "true"
if the connection had been timed out by mysql and a new connection would
be established so you don't see this problem when using earlier versions
of the driver. A fix for this will be available in Fedora 2.0 scheduled
for release later this fall.

Ross

> _______________________________________________
> Fedora-users mailing list
> Fedora...@comm.nsdl.org
> http://comm.nsdl.org/mailman/listinfo/fedora-users
>
>