You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Stijn de Witt <st...@bergland-it.nl> on 2005/04/27 15:48:01 UTC
MySQL communication link failure
Hi,
We have developed a web application using OJB. All seems to work fine,
but after some hours of being deployed we run into a problem.
We get the exception shown below (lots of lines snipped for brevity).
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown
Source)
at
org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unk
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQue
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(
... 89 more
Caused by: java.sql.SQLException: Communication link failure:
java.io.EOFException: Underlying cause: null
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1394)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown
.........
at
nl.bergland.shop.impl.ShopServiceImpl.getShop(ShopServiceImpl.java:10
at
nl.bergland.shop.impl.portlets.ShopPortlet.doView(ShopPortlet.java:48
at javax.portlet.GenericPortlet.doDispatch(GenericPortlet.java:247)
.........
at
org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
at java.lang.Thread.run(Thread.java:534)
** END NESTED EXCEPTION **
I have done some investigating on the internet and seen many posts of
people with this exception. It happens with Hibernate too, so it is not
an OJB problem, but maybe you know how to fix it using OJB.
In short, the problem seems to be caused by MySQL dropping idle
connections after eight hours. The OJB connection pool attempts to use
such a dropped connection, causing the exception.
I have seen a number of solutions mentioned:
1) Specify the MySQL autoReconnect="true" property in the connection
string:
jdbc:mysql://localhost:3306/<database name>?autoReconnect=true
( http://212.26.206.162/roller/page/jakkovos/20040815 )
2) Make sure the used pool drops idle connections before MySQL does
3) Make sure the connection is validated before being used with a validation query
4) Disable pooling with ConnectionFactoryNotPooledImpl
5) Use a DataSource managed by the AppServer (Tomcat / JBoss) i.c.w JNDI lookup
Now I am getting a bit lost in all these options, and testing them is
very hard. Does anyone here have experience with this issue and know of
an easy way to fix it? Option 1) with autoReconnect seems most
attractive to me, but how should I set such a connection parameter using
OJB? Is it even possible?
Thanks for any help,
Stijn de Witt
Bergland IT
http://www.bergland-it.nl/
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Stijn de Witt <St...@bergland-it.nl>.
The server has been running with the new settings for a couple of days
now and it looks good so far.
Thanks everyone for your help!
-Stijn
Stijn de Witt wrote:
> Martin Kalén wrote:
>
>> Stijn de Witt wrote:
>>
>>> Could you review these settings for the evictor thread solution? Do
>>> you think these settings are ok?
>>>
>>> validationQuery="select 1"
>>> minEvictableIdleTimeMillis="60000"
>>> timeBetweenEvictionRunsMillis="120000"
>>> numTestsPerEvictionRun="10"
>>> testWhileIdle="true"
>>> testOnBorrow="false"
>>> testOnReturn="false"
>>>
>>> Could I combine these with autoReconnect="true"? Would that be useful?
>>> Which of the two options would you use?
>>
>>
>> This looks like a good setup to me! Personally, I would recommend you
>> to set testOnBorrow="true" and leave the autoReconnect URL paramater
>> out of the equation.
>
>
> Thanks for all the info Martin, I will do as you say, leave
> autoReconnect=true out, use an evictor thread and set
> testOnBorrow="true".
> I will let you know how it worked out.
>
> Greetings,
>
> -Stijn
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Stijn de Witt <st...@bergland-it.nl>.
Martin Kalén wrote:
> Stijn de Witt wrote:
>
>> Could you review these settings for the evictor thread solution? Do
>> you think these settings are ok?
>>
>> validationQuery="select 1"
>> minEvictableIdleTimeMillis="60000"
>> timeBetweenEvictionRunsMillis="120000"
>> numTestsPerEvictionRun="10"
>> testWhileIdle="true"
>> testOnBorrow="false"
>> testOnReturn="false"
>>
>> Could I combine these with autoReconnect="true"? Would that be useful?
>> Which of the two options would you use?
>
>
> This looks like a good setup to me! Personally, I would recommend you
> to set testOnBorrow="true" and leave the autoReconnect URL paramater
> out of the equation.
Thanks for all the info Martin, I will do as you say, leave
autoReconnect=true out, use an evictor thread and set testOnBorrow="true".
I will let you know how it worked out.
Greetings,
-Stijn
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Martin Kalén <mk...@apache.org>.
Stijn de Witt wrote:
>> The advantage of mastering the Commons Pool options is that this will
>> work with all database servers that OJB supports, not just MySQL.
>>
> Do you know if the autoReconnect=true option alone will fix it? I have
> seen mention of autoReconnectForPools option? We are not planning on
> migrating away from MySql anytime soon. As I understand it, I shouldn't
> 'play it save' and use autoReconnect=true in combination with the
> Evictor thread solution?
The MySQL-specific autoReconnectForPools will not apply when using OJB
connection pooling, only if you use a MySQL JDBC-driver configured
connection pool. The Oracle JDBC-driver has a similar feature in recent
versions.
Bascially, what the JDBC-driver specific pooling feature does is saving
you in case the application does not provide pooling.
Since OJB provides connection pooling I would recommend _not_ to use MySQL
driver-controlled pooling to avoid getting a messy configuration.
(Random thought, not verified:
If you activate a MySQL connection pooling together with OJB dito, you
might end up allocating a whole MySQL connection pool for each Connection
that you check out from the OJB pool.)
The MySQL URL-paramater autoReconnect=true and Commons Pool
testOnBorrow=true will both solve your immediated problem and with
just a few msecs apart as to when it happens
(autoReconnect is like "testJustAfterBorrow").
> Could you review these settings for the evictor thread solution? Do you
> think these settings are ok?
>
> validationQuery="select 1"
> minEvictableIdleTimeMillis="60000"
> timeBetweenEvictionRunsMillis="120000"
> numTestsPerEvictionRun="10"
> testWhileIdle="true"
> testOnBorrow="false"
> testOnReturn="false"
>
> Could I combine these with autoReconnect="true"? Would that be useful?
> Which of the two options would you use?
This looks like a good setup to me! Personally, I would recommend you
to set testOnBorrow="true" and leave the autoReconnect URL paramater
out of the equation.
I just like to keep all settings consistent and leave all validation
up to Commons Pool API, without relying on the JDBC-driver to do
additional checks. (But this is just a matter of "taste" and I would
think you can't measure any big performance issues between the two.
I would assume that the driver-internal method would have the chance
of a quicker validation mechanism than issuing a validation query
through JDBC but the big overhead should be TCP/IP socket creation
and network traffic anyway so it probably matters very little).
The benefit of doing what you do: combine an evictor thread with
testing on borrow makes the probability of Connections being broken
on borrow less (since the eviction runs will probably catch some
of those). This should ie raise the probability for faster response-
times on borrow (no need to discard object from pool and create new
Connection if this was done while idle).
I use "probability" just because you can't tell this deterministically
without making some sort of mathematical proof of the whole application
and all external factors. ;)
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Stijn de Witt <st...@bergland-it.nl>.
Martin Kalén wrote:
> So you should never combine autoReconnect for MySQL JDBC URL and
> testOnBorrow
> for a Commons Pool-based Connection pool (like OJB's default- and
> DBCP-based
> pools). If you do, you will just create additional overhead by
> checking the
> same things twice.
>
> The advantage of mastering the Commons Pool options is that this will
> work
> with all database servers that OJB supports, not just MySQL.
>
Do you know if the autoReconnect=true option alone will fix it? I have
seen mention of autoReconnectForPools option? We are not planning on
migrating away from MySql anytime soon. As I understand it, I shouldn't
'play it save' and use autoReconnect=true in combination with the
Evictor thread solution?
Could you review these settings for the evictor thread solution? Do you
think these settings are ok?
validationQuery="select 1"
minEvictableIdleTimeMillis="60000"
timeBetweenEvictionRunsMillis="120000"
numTestsPerEvictionRun="10"
testWhileIdle="true"
testOnBorrow="false"
testOnReturn="false"
Could I combine these with autoReconnect="true"? Would that be useful?
Which of the two options would you use?
>
> Hope this was not too much info and that it did not confuse you more!
> Regards,
> Martin
Definitely not too much info, thank you very much!
-Stijn
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Martin Kalén <mk...@apache.org>.
Stijn de Witt wrote:
> We have developed a web application using OJB. All seems to work fine,
> but after some hours of being deployed we run into a problem.
<snip/>
> java.io.EOFException
> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1394)
<snip/>
> I have seen a number of solutions mentioned:
>
> 1) Specify the MySQL autoReconnect="true" property in the connection
> string:
>
> jdbc:mysql://localhost:3306/<database name>?autoReconnect=true
This is a feature specific to the MySQL JDBC-driver ("MySQL Connector/J"),
since you specify this in the JDBC URL it will work for every JDBC-
client connecting to your MySQL server (including OJB).
To relate to the Commons Pool configuration options, this would be
equivalent to something like "testJustAfterBorrow=true".
> 2) Make sure the used pool drops idle connections before MySQL does
This is the so called evictor thread solution, and is best for optimal
response times when something is about to happen in your application.
You configure this with the following options in Commons Pool:
validationQuery=SELECT 1 (for MySQL, a simple statement that returns 1 row)
testWhileIdle=true (activates the evictor thread)
timeBetweenEvictionRunsMillis=<no of millisecs between eviction runs>
numTestsPerEvictionRun=<no of Connection to check each run>
minEvictableIdleTimeMillis=<min no of passive msecs before eligable for eviction>
When you set testWhilIdle=true and activate the separate evictor thread,
an event will be triggered each [timeBetweenEvictionRunsMillis] millisecons.
When this event ("eviction run") is triggered, the following will happen:
1. the evictor thread grabs [numTestsPerEvictionRun] Connection instances
from the pool
2. for each Connection that has been idle in the pool more than
[minEvictableIdleTimeMillis], the validation query will be performed
3. for each Connection where the validation query failed, discard the
instance from pool and re-evaluate minIdle
4. if idle threshold is now below minIdle, new Connection instances are
fed into the pool
If you don't specify minIdle, step #4 is not performed.
> 3) Make sure the connection is validated before being used with a
> validation query
This is the testOnBorrow=true setting, validationQuery is specified as above.
The exact same procedure as in the idle object evictor thread will happen
when OJB tries to borrow/use a Connection from the pool.
> 4) Disable pooling with ConnectionFactoryNotPooledImpl
Bad! Imagine that your OJB application in "rapid fire" needs to open and
close Connection instances. If, for each borrow of a Connection the TCP/IP
socket + MySQL overhead has to be performed your application performance
will be degraded severely.
Additional to this, when creating new Connection instances the Java virtual
machine has to manage more garbage collection / memory allocation and the JDBC
driver has to perform static initializer code when creating the connections.
Not using connection pooling is more for testing or debugging I would say,
always use some sort of pooling in a production environment.
> 5) Use a DataSource managed by the AppServer (Tomcat / JBoss) i.c.w JNDI
> lookup
This is a re-iteration of your suggestion 1-4, only that you move configuration
responsibility from OJB properties and repository to the AppServer.
The obvious advantage is that several apps can use the same pool, centrally
configured in the AppServer.
> Now I am getting a bit lost in all these options, and testing them is
> very hard. Does anyone here have experience with this issue and know of
> an easy way to fix it? Option 1) with autoReconnect seems most
> attractive to me, but how should I set such a connection parameter using
> OJB? Is it even possible?
Like Danilo pointed out, this is well possible with OJB and is almost
identical to setting:
validationQuery=SELECT 1
testOnBorrow=true
So you should never combine autoReconnect for MySQL JDBC URL and testOnBorrow
for a Commons Pool-based Connection pool (like OJB's default- and DBCP-based
pools). If you do, you will just create additional overhead by checking the
same things twice.
The advantage of mastering the Commons Pool options is that this will work
with all database servers that OJB supports, not just MySQL.
A few general hints to choose between all those different options:
1. You consider Connection management overhead to be a minimal bottleneck
in your application and don't want to care too much. You just want
to guarantee that the Connection instance OJB gets from the pool is
not broken after 8 idle hrs to avoid exceptions.
Use:
testOnBorrow=true testOnReturn=false testWhileIdle=false
2. You are aware that Connection instances will often be idle long enough
for MySQL to drop the connection and you want instant response times
in your application once it "wakes up" again.
Use:
testOnBorrow=true testOnReturn=false testWhileIdle=true
Just don't set the timeBetweenEvictionRunsMillis to low, since the
evictor thread will then become a bottleneck when it issues the
validation query the whole time.
I think the key issue is to set testOnBorrow=true or the MySQL auto-
reconnect so that you don't have to change your Java logic to re-try
OJB operations when connections fail.
If blazing fast response times are a priority and you can live with
the optimistic approach (ie you believe connections will seldom be
dropped) and your program can take the occasional exception, then
you want to turn testOnBorrow off and set testOnReturn=true instead.
Hope this was not too much info and that it did not confuse you more!
Regards,
Martin
P.S. The difference between the default OJB connection pool and the
DBCP-based one is minimal, since both are based on the Jakarta Commons Pool
package for the actual object pool management. DBCP has some additional
configuration for detecting bad programming patterns (connections
not returned to pool, which in the case of OJB means you don't close
your brokers). See the abandoned config stuff at the bottom of:
http://jakarta.apache.org/commons/dbcp/configuration.html
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Stijn de Witt <st...@bergland-it.nl>.
Danilo Tommasina wrote:
>> Now I am getting a bit lost in all these options, and testing them is
>> very hard. Does anyone here have experience with this issue and know
>> of an easy way to fix it? Option 1) with autoReconnect seems most
>> attractive to me, but how should I set such a connection parameter
>> using OJB? Is it even possible?
>
>
> you can do this in OJB by just adding the ?autoReconnect=true at the
> end of the dbalias attribute in the <jdbc-connection-descriptor> tag
> in your repository.xml
>
> bye
> danilo
>
Thanks, I'll try this out, let you know if it helped.
-Stijn
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: MySQL communication link failure
Posted by Danilo Tommasina <dt...@risksys.com>.
Hi,
> (...)
> Now I am getting a bit lost in all these options, and testing them is
> very hard. Does anyone here have experience with this issue and know of
> an easy way to fix it? Option 1) with autoReconnect seems most
> attractive to me, but how should I set such a connection parameter using
> OJB? Is it even possible?
you can do this in OJB by just adding the ?autoReconnect=true at the end of the dbalias attribute in the <jdbc-connection-descriptor> tag in your repository.xml
bye
danilo
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org