OFBiz › OFBiz - User

MySQL connection problem

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

14 messages Options

Hans Holmlund

MySQL connection problem

I have a problem with MySQL connections who fails. I get a
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I found
an explanation for this in an help page from MySQL
(http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):

" MySQL closes connections after 8 hours of inactivity. You either need
to use a connection pool that handles stale connections or use the
"autoReconnect" parameter. /-/ *Note. * Use of the |autoReconnect|
option is not recommended because there is no safe method of
reconnecting to the MySQL server without risking some corruption of the
connection state or database state information. Instead, you should use
a connection pool which will enable your application to connect to the
MySQL server using an available connection from the pool. The
|autoReconnect| facility is deprecated, and may be removed in a future
release."
This (managing stale connection) is not how EntityEngine has done its
implementation, with EE you can only set autoReconnect to true. I wonder
if you have planned to change this. In the above mentioned page there is
some example code, so this should be an easy thing to fix.

Thanks,

Hans Holmlund

org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
configured transaction manager but there was an error getting a database
Connection through Geronimo for the mysql datasource. Please check your
configuration, class path, etc.
java.lang.RuntimeException: Unable to setTransactionIsolation:
Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
at
com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
at
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)

at
org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)

at org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
at
org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)

at
org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)

at
org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)

at
org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)

at
org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)

at
org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
at
org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
at
org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
at
org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
at
org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
at
org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)

at
org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)

at
org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)

Jacques Le Roux

Re: MySQL connection problem

Administrator

Short answer : go to postgres :o)

Jacques

From: "Hans Holmlund" <[hidden email]>

> I have a problem with MySQL connections who fails. I get a
> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I found
> an explanation for this in an help page from MySQL
> (http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):
>
> " MySQL closes connections after 8 hours of inactivity. You either need
> to use a connection pool that handles stale connections or use the
> "autoReconnect" parameter. /-/ *Note. * Use of the |autoReconnect|
> option is not recommended because there is no safe method of
> reconnecting to the MySQL server without risking some corruption of the
> connection state or database state information. Instead, you should use
> a connection pool which will enable your application to connect to the
> MySQL server using an available connection from the pool. The
> |autoReconnect| facility is deprecated, and may be removed in a future
> release."
> This (managing stale connection) is not how EntityEngine has done its
> implementation, with EE you can only set autoReconnect to true. I wonder
> if you have planned to change this. In the above mentioned page there is
> some example code, so this should be an easy thing to fix.
>
> Thanks,
>
> Hans Holmlund
>
>
>
>
> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
> configured transaction manager but there was an error getting a database
> Connection through Geronimo for the mysql datasource. Please check your
> configuration, class path, etc.
> java.lang.RuntimeException: Unable to setTransactionIsolation:
> Communications link failure due to underlying exception:
>
> ** BEGIN NESTED EXCEPTION **
>
> java.io.EOFException
>
> STACKTRACE:
>
> java.io.EOFException
> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
> at
> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
> at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>
> at org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>
> at
> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>
> at
> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>
> at
> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>
> at
> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
>
> at
> org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
> at
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
> at
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
> at
> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
>
> at
> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
>
> at
> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
>
>

cjhowe

Re: MySQL connection problem

Not to start a database war or anything ;) but isn't
that just trading one rather rare, but moderate sized
problem for a bunch of small, but seemingly common
problems (caveat: problems may just 'seem' more common
because more people on the ofbiz list may use
postgres)?

The 8 hour connection life is supposed to be a
"feature" of MySQL that Postgres doesn't have :)

Also The "8 hour life" must be parameterized
somewhere, so changing that might be a possibility.
In addition, why not just schedule a simple service
that will hit the database every hour or so.

--- Jacques Le Roux <[hidden email]>
wrote:

> Short answer : go to postgres :o)
>
> Jacques
>
> From: "Hans Holmlund" <[hidden email]>
> > I have a problem with MySQL connections who fails.
> I get a
> >
>
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException.
> I found
> > an explanation for this in an help page from MySQL
>
> >
>
(http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):

>
> >
> > " MySQL closes connections after 8 hours of
> inactivity. You either need
> > to use a connection pool that handles stale
> connections or use the
> > "autoReconnect" parameter. /-/ *Note. * Use of the
> |autoReconnect|
> > option is not recommended because there is no safe
> method of
> > reconnecting to the MySQL server without risking
> some corruption of the
> > connection state or database state information.
> Instead, you should use
> > a connection pool which will enable your
> application to connect to the
> > MySQL server using an available connection from
> the pool. The
> > |autoReconnect| facility is deprecated, and may be
> removed in a future
> > release."
> > This (managing stale connection) is not how
> EntityEngine has done its
> > implementation, with EE you can only set
> autoReconnect to true. I wonder
> > if you have planned to change this. In the above
> mentioned page there is
> > some example code, so this should be an easy thing
> to fix.
> >
> > Thanks,
> >
> > Hans Holmlund
> >
> >
> >
> >
> > org.ofbiz.geronimo.GeronimoTransactionFactory -
> Geronimo is the
> > configured transaction manager but there was an
> error getting a database
> > Connection through Geronimo for the mysql
> datasource. Please check your
> > configuration, class path, etc.
> > java.lang.RuntimeException: Unable to
> setTransactionIsolation:
> > Communications link failure due to underlying
> exception:
> >
> > ** BEGIN NESTED EXCEPTION **
> >
> > java.io.EOFException
> >
> > STACKTRACE:
> >
> > java.io.EOFException
> > at
> com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
> > at
>

com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
> > at
>
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
> > at
>
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> > at
>
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> > at
>
com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
> > at
> >
>
com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
> > at
> >
>
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>
> >
> > at
> >
>
org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>
> >
> > at
>
org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
> > at
> >
>
org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>
> >
> > at
> >
>
org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>
> >
> > at
> >
>
org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>
> >
> > at
> >
>
org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>
> >
> > at
> >
>
org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
>
> >
> > at
> >
>
org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
> > at
> >
>
org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
> > at
> >
>
org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
> > at
> >
>
org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
> > at
> >
>
org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
> > at
> >
>
org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
>
> >
> > at
> >
>
org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
>
> >
> > at
> >
>
org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
>
> >
> >
>

Sami Siren-2

Re: MySQL connection problem

Chris Howe wrote:
> Also The "8 hour life" must be parameterized
> somewhere, so changing that might be a possibility.
> In addition, why not just schedule a simple service
> that will hit the database every hour or so.

I would also imagine that a connection pool ..inside...
geronimo?? might be of help in such situations
by testing connections before leasing them.

A quick glance at geronimo docs however did not show
such configuration setting.

--
Sami Siren

David E Jones-2

Re: MySQL connection problem

We aren't actually using the connection pool from Geronimo, just the
transaction manager (JTA/JTS implementation).

But, yes, the connection pool really should manage this whenever
there is a problem opening the connection. Off the top of my head I
don't know what the Minerva library that we use does (though of
course we have all of the source and such), but yes that is the part
of things that should handle this, if the JDBC driver can't...

To be honest, based on the explanation from MySQL and their history
with certain things like, it sounds a bit like an excuse... ;)

-David

On Oct 21, 2006, at 11:11 AM, Sami Siren wrote:

> Chris Howe wrote:
>> Also The "8 hour life" must be parameterized
>> somewhere, so changing that might be a possibility. In addition,
>> why not just schedule a simple service
>> that will hit the database every hour or so.
>
> I would also imagine that a connection pool ..inside...
> geronimo?? might be of help in such situations
> by testing connections before leasing them.
>
> A quick glance at geronimo docs however did not show
> such configuration setting.
>
> --
> Sami Siren

Si Chen-2

Re: MySQL connection problem

In reply to this post by Hans Holmlund

The OFBiz JDBC url already has autoReconnect=true.

That was my first contribution ever to ofbiz!

On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:

> I have a problem with MySQL connections who fails. I get a
> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I
> found an explanation for this in an help page from MySQL (http://
> dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-
> troubleshooting.html#qandaitem-24-3-5-3-4):
> " MySQL closes connections after 8 hours of inactivity. You either
> need to use a connection pool that handles stale connections or use
> the "autoReconnect" parameter. /-/ *Note. * Use of the |
> autoReconnect| option is not recommended because there is no safe
> method of reconnecting to the MySQL server without risking some
> corruption of the connection state or database state information.
> Instead, you should use a connection pool which will enable your
> application to connect to the MySQL server using an available
> connection from the pool. The |autoReconnect| facility is
> deprecated, and may be removed in a future release."
> This (managing stale connection) is not how EntityEngine has done
> its implementation, with EE you can only set autoReconnect to true.
> I wonder if you have planned to change this. In the above mentioned
> page there is some example code, so this should be an easy thing to
> fix.
>
> Thanks,
>
> Hans Holmlund
>
>
>
>
> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
> configured transaction manager but there was an error getting a
> database Connection through Geronimo for the mysql datasource.
> Please check your configuration, class path, etc.
> java.lang.RuntimeException: Unable to setTransactionIsolation:
> Communications link failure due to underlying exception:
>
> ** BEGIN NESTED EXCEPTION **
>
> java.io.EOFException
>
> STACKTRACE:
>
> java.io.EOFException
> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
> at com.mysql.jdbc.Connection.setTransactionIsolation
> (Connection.java:5499)
> at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransaction
> Isolation(XAConnectionImpl.java:117)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
> (XAConnectionFactory.java:412)
> at org.ofbiz.minerva.pool.ObjectPool.getObject
> (ObjectPool.java:645)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
> (XAPoolDataSource.java:355)
> at
> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection
> (MinervaConnectionFactory.java:56)
> at org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection
> (GeronimoTransactionFactory.java:94)
> at
> org.ofbiz.entity.transaction.TransactionFactory.getConnection
> (TransactionFactory.java:104)
> at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
> (ConnectionFactory.java:82)
> at org.ofbiz.entity.jdbc.SQLProcessor.getConnection
> (SQLProcessor.java:268)
> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
> (SQLProcessor.java:374)
> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
> (SQLProcessor.java:358)
> at org.ofbiz.entity.datasource.GenericDAO.select
> (GenericDAO.java:539)
> at org.ofbiz.entity.datasource.GenericDAO.select
> (GenericDAO.java:510)
> at
> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey
> (GenericHelperDAO.java:90)
> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
> (GenericDelegator.java:1248)
> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
> (GenericDelegator.java:1304)
>

Best Regards,

Si
[hidden email]

Hans Holmlund

Re: MySQL connection problem

The Newer versions of the MySQL connector doesn't support autoReconnect
anymore (however there is a property enableDeprecatedAutoreconnect,
default is false, which can change that). The reason for deprecating
this feature might be valid (and I don't have any opinion on that) but
it will cause a lot of problem.
Unfortunately so does MySQLs suggested method not work properly either.
This might be something with how the pooling is done in EntityEngine.
When a connection is disconnected by the DB (i.e. after wait_timeout
seconds, a MySQL property that can be changed) a SQLException should be
thrown if that connection is used again. But what I get is the following;

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
at
com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
at
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
at
org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
at org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
at
org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
at
org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
at
org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
at
org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
at
org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:86)

I suppose ConnectionFactory might be a good place to catch the
exceptions, but I am not sure about what to do with the EOFException.
Does anyone has any ideas?
"why not just schedule a simple service that will hit the database every
hour or so." This is not a good idea, unless it is done from inside the
pool.

/ Hans Holmlund

Si Chen skrev:

> The OFBiz JDBC url already has autoReconnect=true.
>
> That was my first contribution ever to ofbiz!
>
> On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:
>
>> I have a problem with MySQL connections who fails. I get a
>> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I
>> found an explanation for this in an help page from MySQL
>> (http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):
>>
>> " MySQL closes connections after 8 hours of inactivity. You either
>> need to use a connection pool that handles stale connections or use
>> the "autoReconnect" parameter. /-/ *Note. * Use of the
>> |autoReconnect| option is not recommended because there is no safe
>> method of reconnecting to the MySQL server without risking some
>> corruption of the connection state or database state information.
>> Instead, you should use a connection pool which will enable your
>> application to connect to the MySQL server using an available
>> connection from the pool. The |autoReconnect| facility is deprecated,
>> and may be removed in a future release."
>> This (managing stale connection) is not how EntityEngine has done its
>> implementation, with EE you can only set autoReconnect to true. I
>> wonder if you have planned to change this. In the above mentioned
>> page there is some example code, so this should be an easy thing to fix.
>>
>> Thanks,
>>
>> Hans Holmlund
>>
>>
>>
>>
>> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
>> configured transaction manager but there was an error getting a
>> database Connection through Geronimo for the mysql datasource. Please
>> check your configuration, class path, etc.
>> java.lang.RuntimeException: Unable to setTransactionIsolation:
>> Communications link failure due to underlying exception:
>>
>> ** BEGIN NESTED EXCEPTION **
>>
>> java.io.EOFException
>>
>> STACKTRACE:
>>
>> java.io.EOFException
>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>> at
>> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>>
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>>
>> at
>> org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>>
>> at
>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>>
>> at
>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>>
>> at
>> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>>
>> at
>> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
>>
>> at
>> org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
>> at
>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
>>
>> at
>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
>>
>> at
>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
>> at
>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
>> at
>> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
>>
>> at
>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
>>
>> at
>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
>>
>>
>
> Best Regards,
>
> Si
> [hidden email]
>
>
>
>

David E Jones-2

Re: MySQL connection problem

This is an interesting stack trace because the problem is happening
when it tries to set the transaction isolation level...

Which isolation level do you have configured? This may not be the
problem since this is one of the first things that happens when a new
connection is created or when an existing connection is re-used. This
might be good to look into though.

-David

On Oct 23, 2006, at 4:41 PM, Hans Holmlund wrote:

> The Newer versions of the MySQL connector doesn't support
> autoReconnect anymore (however there is a property
> enableDeprecatedAutoreconnect, default is false, which can change
> that). The reason for deprecating this feature might be valid (and
> I don't have any opinion on that) but it will cause a lot of problem.
> Unfortunately so does MySQLs suggested method not work properly
> either. This might be something with how the pooling is done in
> EntityEngine. When a connection is disconnected by the DB (i.e.
> after wait_timeout seconds, a MySQL property that can be changed) a
> SQLException should be thrown if that connection is used again. But
> what I get is the following;
>
> java.io.EOFException
> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
> at com.mysql.jdbc.Connection.setTransactionIsolation
> (Connection.java:5499)
> at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransaction
> Isolation(XAConnectionImpl.java:117)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
> (XAConnectionFactory.java:412)
> at org.ofbiz.minerva.pool.ObjectPool.getObject
> (ObjectPool.java:645)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
> (XAPoolDataSource.java:355)
> at
> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection
> (MinervaConnectionFactory.java:56)
> at
> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection
> (GeronimoTransactionFactory.java:94)
> at
> org.ofbiz.entity.transaction.TransactionFactory.getConnection
> (TransactionFactory.java:104)
> at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
> (ConnectionFactory.java:86)
>
> I suppose ConnectionFactory might be a good place to catch the
> exceptions, but I am not sure about what to do with the
> EOFException. Does anyone has any ideas?
> "why not just schedule a simple service that will hit the database
> every hour or so." This is not a good idea, unless it is done from
> inside the pool.
>
> / Hans Holmlund
>
>
> Si Chen skrev:
>> The OFBiz JDBC url already has autoReconnect=true.
>>
>> That was my first contribution ever to ofbiz!
>>
>> On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:
>>
>>> I have a problem with MySQL connections who fails. I get a
>>> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I
>>> found an explanation for this in an help page from MySQL (http://
>>> dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-
>>> troubleshooting.html#qandaitem-24-3-5-3-4):
>>> " MySQL closes connections after 8 hours of inactivity. You
>>> either need to use a connection pool that handles stale
>>> connections or use the "autoReconnect" parameter. /-/ *Note. *
>>> Use of the |autoReconnect| option is not recommended because
>>> there is no safe method of reconnecting to the MySQL server
>>> without risking some corruption of the connection state or
>>> database state information. Instead, you should use a connection
>>> pool which will enable your application to connect to the MySQL
>>> server using an available connection from the pool. The |
>>> autoReconnect| facility is deprecated, and may be removed in a
>>> future release."
>>> This (managing stale connection) is not how EntityEngine has done
>>> its implementation, with EE you can only set autoReconnect to
>>> true. I wonder if you have planned to change this. In the above
>>> mentioned page there is some example code, so this should be an
>>> easy thing to fix.
>>>
>>> Thanks,
>>>
>>> Hans Holmlund
>>>
>>>
>>>
>>>
>>> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
>>> configured transaction manager but there was an error getting a
>>> database Connection through Geronimo for the mysql datasource.
>>> Please check your configuration, class path, etc.
>>> java.lang.RuntimeException: Unable to setTransactionIsolation:
>>> Communications link failure due to underlying exception:
>>>
>>> ** BEGIN NESTED EXCEPTION **
>>>
>>> java.io.EOFException
>>>
>>> STACKTRACE:
>>>
>>> java.io.EOFException
>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
>>> 2304)
>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>> at com.mysql.jdbc.Connection.setTransactionIsolation
>>> (Connection.java:5499)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransacti
>>> onIsolation(XAConnectionImpl.java:117)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
>>> (XAConnectionFactory.java:412)
>>> at org.ofbiz.minerva.pool.ObjectPool.getObject
>>> (ObjectPool.java:645)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
>>> (XAPoolDataSource.java:355)
>>> at
>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(
>>> MinervaConnectionFactory.java:56)
>>> at
>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection
>>> (GeronimoTransactionFactory.java:94)
>>> at
>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection
>>> (TransactionFactory.java:104)
>>> at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
>>> (ConnectionFactory.java:82)
>>> at org.ofbiz.entity.jdbc.SQLProcessor.getConnection
>>> (SQLProcessor.java:268)
>>> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
>>> (SQLProcessor.java:374)
>>> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
>>> (SQLProcessor.java:358)
>>> at org.ofbiz.entity.datasource.GenericDAO.select
>>> (GenericDAO.java:539)
>>> at org.ofbiz.entity.datasource.GenericDAO.select
>>> (GenericDAO.java:510)
>>> at
>>> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey
>>> (GenericHelperDAO.java:90)
>>> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
>>> (GenericDelegator.java:1248)
>>> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
>>> (GenericDelegator.java:1304)
>>>
>>
>> Best Regards,
>>
>> Si
>> [hidden email]
>>
>>
>>
>>
>

Hans Holmlund

Re: MySQL connection problem

I'm using read committed. My guess has been that this, as you points
out, is the first thing done with the connection and therefore the first
thing to fail.
I have looked once again at the code and there is some things that
puzzles me. There are a lot of connection and transaction factories
involved and I am not sure that all of them are working together. I am
using geronimo transaction factory. I thought that the best place to
catch the SQLException was in ConnectionFactory, but then I discovered
that the SQLException was eaten on its way up to that class. An
SQLException is thrown by the Connector package, but it is catched by
the XAConnectionFactory and there it is rethrown as a RuntimeException
(why??).
I have tried to handle this error in XAConnectionFactory and ObjectPool,
like this:
In XAConnectionFactory in the prepareObject-method I changed to this:
try {
((XAConnectionImpl)
con).setTransactionIsolation(transactionIsolation);
} catch (SQLException sex) {
String sqlState = sex.getSQLState();
if ("08S01".equals(sqlState) ||
"40001".equals(sqlState)) {
return null;
}
else {
throw new RuntimeException("Unable to
setTransactionIsolation: " + sex.getMessage());
}
}

In ObjectPool I added this code after the prepareObject-statement:
result = factory.prepareObject(ob);
if (result == null) {
markObjectAsInvalid(ob);
releaseObject(ob);
try {
rec = createNewObject(parameters);
ob = rec.getObject();
} catch (Exception e) {
log.error("Exception in creating new object for
pool", e);
permits.release();
throw e;
}
result = factory.prepareObject(ob);
if (result == null) {
throw new RuntimeException("Unable to
setTransactionIsolation");
}
}

This doesn't work because a new Connection is not created. Perhaps it is
impossible to create a new connection inside the object-pool? But where
can this be handled?
Any suggestions?

/ Hans

David E Jones skrev:

>
> This is an interesting stack trace because the problem is happening
> when it tries to set the transaction isolation level...
>
> Which isolation level do you have configured? This may not be the
> problem since this is one of the first things that happens when a new
> connection is created or when an existing connection is re-used. This
> might be good to look into though.
>
> -David
>
>
> On Oct 23, 2006, at 4:41 PM, Hans Holmlund wrote:
>
>> The Newer versions of the MySQL connector doesn't support
>> autoReconnect anymore (however there is a property
>> enableDeprecatedAutoreconnect, default is false, which can change
>> that). The reason for deprecating this feature might be valid (and I
>> don't have any opinion on that) but it will cause a lot of problem.
>> Unfortunately so does MySQLs suggested method not work properly
>> either. This might be something with how the pooling is done in
>> EntityEngine. When a connection is disconnected by the DB (i.e. after
>> wait_timeout seconds, a MySQL property that can be changed) a
>> SQLException should be thrown if that connection is used again. But
>> what I get is the following;
>>
>> java.io.EOFException
>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>> at
>> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>>
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>>
>> at
>> org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
>> at
>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>>
>> at
>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>>
>> at
>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>>
>> at
>> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>>
>> at
>> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:86)
>>
>>
>> I suppose ConnectionFactory might be a good place to catch the
>> exceptions, but I am not sure about what to do with the EOFException.
>> Does anyone has any ideas?
>> "why not just schedule a simple service that will hit the database
>> every hour or so." This is not a good idea, unless it is done from
>> inside the pool.
>>
>> / Hans Holmlund
>>
>>
>> Si Chen skrev:
>>> The OFBiz JDBC url already has autoReconnect=true.
>>>
>>> That was my first contribution ever to ofbiz!
>>>
>>> On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:
>>>
>>>> I have a problem with MySQL connections who fails. I get a
>>>> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I
>>>> found an explanation for this in an help page from MySQL
>>>> (http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):
>>>>
>>>> " MySQL closes connections after 8 hours of inactivity. You either
>>>> need to use a connection pool that handles stale connections or use
>>>> the "autoReconnect" parameter. /-/ *Note. * Use of the
>>>> |autoReconnect| option is not recommended because there is no safe
>>>> method of reconnecting to the MySQL server without risking some
>>>> corruption of the connection state or database state information.
>>>> Instead, you should use a connection pool which will enable your
>>>> application to connect to the MySQL server using an available
>>>> connection from the pool. The |autoReconnect| facility is
>>>> deprecated, and may be removed in a future release."
>>>> This (managing stale connection) is not how EntityEngine has done
>>>> its implementation, with EE you can only set autoReconnect to true.
>>>> I wonder if you have planned to change this. In the above mentioned
>>>> page there is some example code, so this should be an easy thing to
>>>> fix.
>>>>
>>>> Thanks,
>>>>
>>>> Hans Holmlund
>>>>
>>>>
>>>>
>>>>
>>>> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
>>>> configured transaction manager but there was an error getting a
>>>> database Connection through Geronimo for the mysql datasource.
>>>> Please check your configuration, class path, etc.
>>>> java.lang.RuntimeException: Unable to setTransactionIsolation:
>>>> Communications link failure due to underlying exception:
>>>>
>>>> ** BEGIN NESTED EXCEPTION **
>>>>
>>>> java.io.EOFException
>>>>
>>>> STACKTRACE:
>>>>
>>>> java.io.EOFException
>>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>>> at
>>>> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>>>>
>>>> at
>>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>>>>
>>>> at
>>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>>>>
>>>> at
>>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>>>>
>>>> at
>>>> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
>>>>
>>>> at
>>>> org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
>>>>
>>>> at
>>>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
>>>>
>>>> at
>>>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
>>>>
>>>> at
>>>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
>>>> at
>>>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
>>>> at
>>>> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
>>>>
>>>> at
>>>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
>>>>
>>>> at
>>>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
>>>>
>>>>
>>>
>>> Best Regards,
>>>
>>> Si
>>> [hidden email]
>>>
>>>
>>>
>>>
>>
>
>

David E Jones-2

Re: MySQL connection problem

I really don't know the Minerva code well at all. Still, I'd suspect
that however things are implemented it would be possible to replace a
connection on an error. Possible, but not necessarily easy....

-David

On Oct 24, 2006, at 5:30 AM, Hans Holmlund wrote:

> I'm using read committed. My guess has been that this, as you
> points out, is the first thing done with the connection and
> therefore the first thing to fail.
> I have looked once again at the code and there is some things that
> puzzles me. There are a lot of connection and transaction factories
> involved and I am not sure that all of them are working together. I
> am using geronimo transaction factory. I thought that the best
> place to catch the SQLException was in ConnectionFactory, but then
> I discovered that the SQLException was eaten on its way up to that
> class. An SQLException is thrown by the Connector package, but it
> is catched by the XAConnectionFactory and there it is rethrown as a
> RuntimeException (why??).
> I have tried to handle this error in XAConnectionFactory and
> ObjectPool, like this:
> In XAConnectionFactory in the prepareObject-method I changed to this:
> try {
> ((XAConnectionImpl) con).setTransactionIsolation
> (transactionIsolation);
> } catch (SQLException sex) {
> String sqlState = sex.getSQLState();
> if ("08S01".equals(sqlState) || "40001".equals
> (sqlState)) {
> return null;
> }
> else {
> throw new RuntimeException("Unable to
> setTransactionIsolation: " + sex.getMessage());
> }
> }
>
> In ObjectPool I added this code after the prepareObject-statement:
> result = factory.prepareObject(ob);
> if (result == null) {
> markObjectAsInvalid(ob);
> releaseObject(ob);
> try {
> rec = createNewObject(parameters);
> ob = rec.getObject();
> } catch (Exception e) {
> log.error("Exception in creating new object
> for pool", e);
> permits.release();
> throw e;
> }
> result = factory.prepareObject(ob);
> if (result == null) {
> throw new RuntimeException("Unable to
> setTransactionIsolation");
> }
> }
>
> This doesn't work because a new Connection is not created. Perhaps
> it is impossible to create a new connection inside the object-pool?
> But where can this be handled?
> Any suggestions?
>
> / Hans
>
>
> David E Jones skrev:
>>
>> This is an interesting stack trace because the problem is
>> happening when it tries to set the transaction isolation level...
>>
>> Which isolation level do you have configured? This may not be the
>> problem since this is one of the first things that happens when a
>> new connection is created or when an existing connection is re-
>> used. This might be good to look into though.
>>
>> -David
>>
>>
>> On Oct 23, 2006, at 4:41 PM, Hans Holmlund wrote:
>>
>>> The Newer versions of the MySQL connector doesn't support
>>> autoReconnect anymore (however there is a property
>>> enableDeprecatedAutoreconnect, default is false, which can change
>>> that). The reason for deprecating this feature might be valid
>>> (and I don't have any opinion on that) but it will cause a lot of
>>> problem.
>>> Unfortunately so does MySQLs suggested method not work properly
>>> either. This might be something with how the pooling is done in
>>> EntityEngine. When a connection is disconnected by the DB (i.e.
>>> after wait_timeout seconds, a MySQL property that can be changed)
>>> a SQLException should be thrown if that connection is used again.
>>> But what I get is the following;
>>>
>>> java.io.EOFException
>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
>>> 2304)
>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>> at com.mysql.jdbc.Connection.setTransactionIsolation
>>> (Connection.java:5499)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransacti
>>> onIsolation(XAConnectionImpl.java:117)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
>>> (XAConnectionFactory.java:412)
>>> at org.ofbiz.minerva.pool.ObjectPool.getObject
>>> (ObjectPool.java:645)
>>> at
>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
>>> (XAPoolDataSource.java:355)
>>> at
>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(
>>> MinervaConnectionFactory.java:56)
>>> at
>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection
>>> (GeronimoTransactionFactory.java:94)
>>> at
>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection
>>> (TransactionFactory.java:104)
>>> at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
>>> (ConnectionFactory.java:86)
>>>
>>> I suppose ConnectionFactory might be a good place to catch the
>>> exceptions, but I am not sure about what to do with the
>>> EOFException. Does anyone has any ideas?
>>> "why not just schedule a simple service that will hit the
>>> database every hour or so." This is not a good idea, unless it is
>>> done from inside the pool.
>>>
>>> / Hans Holmlund
>>>
>>>
>>> Si Chen skrev:
>>>> The OFBiz JDBC url already has autoReconnect=true.
>>>>
>>>> That was my first contribution ever to ofbiz!
>>>>
>>>> On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:
>>>>
>>>>> I have a problem with MySQL connections who fails. I get a
>>>>> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException.
>>>>> I found an explanation for this in an help page from MySQL
>>>>> (http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-
>>>>> troubleshooting.html#qandaitem-24-3-5-3-4):
>>>>> " MySQL closes connections after 8 hours of inactivity. You
>>>>> either need to use a connection pool that handles stale
>>>>> connections or use the "autoReconnect" parameter. /-/ *Note. *
>>>>> Use of the |autoReconnect| option is not recommended because
>>>>> there is no safe method of reconnecting to the MySQL server
>>>>> without risking some corruption of the connection state or
>>>>> database state information. Instead, you should use a
>>>>> connection pool which will enable your application to connect
>>>>> to the MySQL server using an available connection from the
>>>>> pool. The |autoReconnect| facility is deprecated, and may be
>>>>> removed in a future release."
>>>>> This (managing stale connection) is not how EntityEngine has
>>>>> done its implementation, with EE you can only set autoReconnect
>>>>> to true. I wonder if you have planned to change this. In the
>>>>> above mentioned page there is some example code, so this should
>>>>> be an easy thing to fix.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Hans Holmlund
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is
>>>>> the configured transaction manager but there was an error
>>>>> getting a database Connection through Geronimo for the mysql
>>>>> datasource. Please check your configuration, class path, etc.
>>>>> java.lang.RuntimeException: Unable to setTransactionIsolation:
>>>>> Communications link failure due to underlying exception:
>>>>>
>>>>> ** BEGIN NESTED EXCEPTION **
>>>>>
>>>>> java.io.EOFException
>>>>>
>>>>> STACKTRACE:
>>>>>
>>>>> java.io.EOFException
>>>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>>>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
>>>>> 2304)
>>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:
>>>>> 2803)
>>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>>>> at com.mysql.jdbc.Connection.setTransactionIsolation
>>>>> (Connection.java:5499)
>>>>> at
>>>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransac
>>>>> tionIsolation(XAConnectionImpl.java:117)
>>>>> at
>>>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
>>>>> (XAConnectionFactory.java:412)
>>>>> at org.ofbiz.minerva.pool.ObjectPool.getObject
>>>>> (ObjectPool.java:645)
>>>>> at
>>>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
>>>>> (XAPoolDataSource.java:355)
>>>>> at
>>>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnectio
>>>>> n(MinervaConnectionFactory.java:56)
>>>>> at
>>>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection
>>>>> (GeronimoTransactionFactory.java:94)
>>>>> at
>>>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection
>>>>> (TransactionFactory.java:104)
>>>>> at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
>>>>> (ConnectionFactory.java:82)
>>>>> at org.ofbiz.entity.jdbc.SQLProcessor.getConnection
>>>>> (SQLProcessor.java:268)
>>>>> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
>>>>> (SQLProcessor.java:374)
>>>>> at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
>>>>> (SQLProcessor.java:358)
>>>>> at org.ofbiz.entity.datasource.GenericDAO.select
>>>>> (GenericDAO.java:539)
>>>>> at org.ofbiz.entity.datasource.GenericDAO.select
>>>>> (GenericDAO.java:510)
>>>>> at
>>>>> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey
>>>>> (GenericHelperDAO.java:90)
>>>>> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
>>>>> (GenericDelegator.java:1248)
>>>>> at org.ofbiz.entity.GenericDelegator.findByPrimaryKey
>>>>> (GenericDelegator.java:1304)
>>>>>
>>>>
>>>> Best Regards,
>>>>
>>>> Si
>>>> [hidden email]
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>

Hans Holmlund

Re: MySQL connection problem

The Trick seems to be to find out where to do this replacement. If I
create a new Connection in the ObjectPool I get this exception:
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAResourceImpl - Could not commit
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
Connection.close() has already been called. Invalid operation in this state.

How is this done for other databases? Does EntityEngine always depends
on the DB to fix this (holding the connections for ever)?

/ Hans

David E Jones skrev:

>
> I really don't know the Minerva code well at all. Still, I'd suspect
> that however things are implemented it would be possible to replace a
> connection on an error. Possible, but not necessarily easy....
>
> -David
>
>
> On Oct 24, 2006, at 5:30 AM, Hans Holmlund wrote:
>
>> I'm using read committed. My guess has been that this, as you points
>> out, is the first thing done with the connection and therefore the
>> first thing to fail.
>> I have looked once again at the code and there is some things that
>> puzzles me. There are a lot of connection and transaction factories
>> involved and I am not sure that all of them are working together. I
>> am using geronimo transaction factory. I thought that the best place
>> to catch the SQLException was in ConnectionFactory, but then I
>> discovered that the SQLException was eaten on its way up to that
>> class. An SQLException is thrown by the Connector package, but it is
>> catched by the XAConnectionFactory and there it is rethrown as a
>> RuntimeException (why??).
>> I have tried to handle this error in XAConnectionFactory and
>> ObjectPool, like this:
>> In XAConnectionFactory in the prepareObject-method I changed to this:
>> try {
>> ((XAConnectionImpl)
>> con).setTransactionIsolation(transactionIsolation);
>> } catch (SQLException sex) {
>> String sqlState = sex.getSQLState();
>> if ("08S01".equals(sqlState) ||
>> "40001".equals(sqlState)) {
>> return null;
>> }
>> else {
>> throw new RuntimeException("Unable to
>> setTransactionIsolation: " + sex.getMessage());
>> }
>> }
>>
>> In ObjectPool I added this code after the prepareObject-statement:
>> result = factory.prepareObject(ob);
>> if (result == null) {
>> markObjectAsInvalid(ob);
>> releaseObject(ob);
>> try {
>> rec = createNewObject(parameters);
>> ob = rec.getObject();
>> } catch (Exception e) {
>> log.error("Exception in creating new object
>> for pool", e);
>> permits.release();
>> throw e;
>> }
>> result = factory.prepareObject(ob);
>> if (result == null) {
>> throw new RuntimeException("Unable to
>> setTransactionIsolation");
>> }
>> }
>>
>> This doesn't work because a new Connection is not created. Perhaps it
>> is impossible to create a new connection inside the object-pool? But
>> where can this be handled?
>> Any suggestions?
>>
>> / Hans
>>
>>
>> David E Jones skrev:
>>>
>>> This is an interesting stack trace because the problem is happening
>>> when it tries to set the transaction isolation level...
>>>
>>> Which isolation level do you have configured? This may not be the
>>> problem since this is one of the first things that happens when a
>>> new connection is created or when an existing connection is re-used.
>>> This might be good to look into though.
>>>
>>> -David
>>>
>>>
>>> On Oct 23, 2006, at 4:41 PM, Hans Holmlund wrote:
>>>
>>>> The Newer versions of the MySQL connector doesn't support
>>>> autoReconnect anymore (however there is a property
>>>> enableDeprecatedAutoreconnect, default is false, which can change
>>>> that). The reason for deprecating this feature might be valid (and
>>>> I don't have any opinion on that) but it will cause a lot of problem.
>>>> Unfortunately so does MySQLs suggested method not work properly
>>>> either. This might be something with how the pooling is done in
>>>> EntityEngine. When a connection is disconnected by the DB (i.e.
>>>> after wait_timeout seconds, a MySQL property that can be changed) a
>>>> SQLException should be thrown if that connection is used again. But
>>>> what I get is the following;
>>>>
>>>> java.io.EOFException
>>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>>> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>>> at
>>>> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>>>>
>>>> at
>>>> org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
>>>> at
>>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>>>>
>>>> at
>>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>>>>
>>>> at
>>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>>>>
>>>> at
>>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>>>>
>>>> at
>>>> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:86)
>>>>
>>>>
>>>> I suppose ConnectionFactory might be a good place to catch the
>>>> exceptions, but I am not sure about what to do with the
>>>> EOFException. Does anyone has any ideas?
>>>> "why not just schedule a simple service that will hit the database
>>>> every hour or so." This is not a good idea, unless it is done from
>>>> inside the pool.
>>>>
>>>> / Hans Holmlund
>>>>
>>>>
>>>> Si Chen skrev:
>>>>> The OFBiz JDBC url already has autoReconnect=true.
>>>>>
>>>>> That was my first contribution ever to ofbiz!
>>>>>
>>>>> On Oct 21, 2006, at 6:38 AM, Hans Holmlund wrote:
>>>>>
>>>>>> I have a problem with MySQL connections who fails. I get a
>>>>>> com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException. I
>>>>>> found an explanation for this in an help page from MySQL
>>>>>> (http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4):
>>>>>>
>>>>>> " MySQL closes connections after 8 hours of inactivity. You
>>>>>> either need to use a connection pool that handles stale
>>>>>> connections or use the "autoReconnect" parameter. /-/ *Note. *
>>>>>> Use of the |autoReconnect| option is not recommended because
>>>>>> there is no safe method of reconnecting to the MySQL server
>>>>>> without risking some corruption of the connection state or
>>>>>> database state information. Instead, you should use a connection
>>>>>> pool which will enable your application to connect to the MySQL
>>>>>> server using an available connection from the pool. The
>>>>>> |autoReconnect| facility is deprecated, and may be removed in a
>>>>>> future release."
>>>>>> This (managing stale connection) is not how EntityEngine has done
>>>>>> its implementation, with EE you can only set autoReconnect to
>>>>>> true. I wonder if you have planned to change this. In the above
>>>>>> mentioned page there is some example code, so this should be an
>>>>>> easy thing to fix.
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Hans Holmlund
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> org.ofbiz.geronimo.GeronimoTransactionFactory - Geronimo is the
>>>>>> configured transaction manager but there was an error getting a
>>>>>> database Connection through Geronimo for the mysql datasource.
>>>>>> Please check your configuration, class path, etc.
>>>>>> java.lang.RuntimeException: Unable to setTransactionIsolation:
>>>>>> Communications link failure due to underlying exception:
>>>>>>
>>>>>> ** BEGIN NESTED EXCEPTION **
>>>>>>
>>>>>> java.io.EOFException
>>>>>>
>>>>>> STACKTRACE:
>>>>>>
>>>>>> java.io.EOFException
>>>>>> at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
>>>>>> at
>>>>>> com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
>>>>>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
>>>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>>>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>>>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
>>>>>> at
>>>>>> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:5499)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
>>>>>> at
>>>>>> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:94)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:539)
>>>>>> at
>>>>>> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:510)
>>>>>> at
>>>>>> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
>>>>>>
>>>>>> at
>>>>>> org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
>>>>>>
>>>>>>
>>>>>
>>>>> Best Regards,
>>>>>
>>>>> Si
>>>>> [hidden email]
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>

Hans Holmlund

Re: MySQL connection problem

In reply to this post by David E Jones-2

Sorry I have to nag about this, I'm a probably the only one having this
problem, but as soon as Ofbiz decide to upgrade the MySQL jdbc driver
the whole community (i.e. those using MySQL) will have the same problem.
I was not able to create a new connection in the Minerva classes.
Instead I made a change so that the SQLException was propagated all the
way up to ConnectionFactory (this involved changes in XAResourceImpl,
XAConnectionFactory, ObjectPool, PoolObjectFactory and
GeronimoTransactionFactory).
In the ConnectionFactory I was able to catch the SQLException and retry.
Unfortunately this was not enough. Usually before getConnection in
ConnectionFactory is called it is preceded by a TransactionUtil.begin().
However that transaction doesn't seem to be valid after the
SQLException. If I do a TransactionUtil.suspend() and a
TransactionUtil.begin() before I try to make a new connection it
actually works.
So now I hope if someone with better insight in the TransactionUtil
could explain why I had to do this suspend and begin with the
transaction, and if it is a proper way of handling transactions this
way? Could this cause other errors?
I actually think this will cause other errors because I had this fault -
"Lock wait timeout exceeded; try restarting transaction" - when doing a
GenericDelegator.removeByAnd.

Cheers,

Hans Holmlund

David E Jones wrote:

Katherine Morgan

Re: MySQL connection problem

I had the same problem and found a solution.

For completeness, here is the exception:

203504578 (invoker-Thread-82) [ ConnectionFactory.java:95 :ERROR]
---- runtime exception report --------------------------------------------------
There was an error getting a Minerva datasource.
Exception: java.lang.RuntimeException
Message: Unable to setTransactionIsolation: 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:1395)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1539)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1930)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1225)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2237)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2218)
at com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:913)
at org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransactionIsolation(XAConnectionImpl.java:117)
at org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject(XAConnectionFactory.java:412)
at org.ofbiz.minerva.pool.ObjectPool.getObject(ObjectPool.java:645)
at org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:355)
at org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection(MinervaConnectionFactory.java:56)
at org.ofbiz.entity.jdbc.ConnectionFactory.tryGenericConnectionSources(ConnectionFactory.java:92)
at org.ofbiz.entity.transaction.JNDIFactory.getConnection(JNDIFactory.java:158)
at org.ofbiz.entity.transaction.TransactionFactory.getConnection(TransactionFactory.java:104)
at org.ofbiz.entity.jdbc.ConnectionFactory.getConnection(ConnectionFactory.java:82)
at org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:268)
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:374)
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:358)
at org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:549)
at org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:520)
at org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:90)
at org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1248)
at org.ofbiz.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:1304)
at org.ofbiz.service.job.PersistedServiceJob.getJob(PersistedServiceJob.java:282)
at org.ofbiz.service.job.PersistedServiceJob.init(PersistedServiceJob.java:129)
at org.ofbiz.service.job.GenericServiceJob.exec(GenericServiceJob.java:74)
at org.ofbiz.service.job.JobInvoker.run(JobInvoker.java:226)
at java.lang.Thread.run(Thread.java:534)

Reproduce:

The wait_timeout variable on the mysql db is set to 28800 seconds (8 hours) by default.

If you set this to a much smaller amount, eg 10 seconds, then this error can be reproduced much quicker!

set GLOBAL wait_timeout=10;
set SESSION wait_timeout=10;
set GLOBAL interactive_timeout=10;
set SESSION interactive_timeout=10;

Specs:

opentaps-0.9.1-all-platforms
mysql 5.0.22
connector versions: 3.0.17, 5.0.4

Solution:

Have a look at the org.ofbiz.minerva.pool.ObjectPool and PoolGCThread class, the javadoc is fabulous. In particular look at the following methods, and also inspect the default values:
ObjectPool.setGCEnabled(boolean);
ObjectPool.setIdleTimeoutEnabled(boolean);
ObjectPool.setTimestampUsed(boolean);

In MinervaConnectionFactory the pools are created. Add the following lines:

pds.setGCEnabled(true);
pds.setIdleTimeoutEnabled(true);
pds.setTimestampUsed(true);

(I made my code take configuration parameters from entityengine.xml, see the jotmJdbcElement variable).

Debug:

base/config/debug.properties
log4j.logger.org.ofbiz.minerva=DEBUG

You can then see the thread working to recycle the connections.

David E Jones

Re: MySQL connection problem

This sounds like a good thing to make configurable. You mentioned
some code in your message. Is that something you could contribute to
OFBiz (ie attach to a Jira issue)?

-David

On Feb 21, 2007, at 5:27 PM, Katherine Morgan wrote:

>
> I had the same problem and found a solution.
>
> For completeness, here is the exception:
>
> 203504578 (invoker-Thread-82) [ ConnectionFactory.java:95 :ERROR]
> ---- runtime exception report
> --------------------------------------------------
> There was an error getting a Minerva datasource.
> Exception: java.lang.RuntimeException
> Message: Unable to setTransactionIsolation: 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:1395)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
> 1539)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1930)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
> at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1225)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2278)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2237)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2218)
> at
> com.mysql.jdbc.Connection.setTransactionIsolation(Connection.java:913)
> at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.setTransaction
> Isolation(XAConnectionImpl.java:117)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory.prepareObject
> (XAConnectionFactory.java:412)
> at org.ofbiz.minerva.pool.ObjectPool.getObject
> (ObjectPool.java:645)
> at
> org.ofbiz.minerva.pool.jdbc.xa.XAPoolDataSource.getConnection
> (XAPoolDataSource.java:355)
> at
> org.ofbiz.entity.transaction.MinervaConnectionFactory.getConnection
> (MinervaConnectionFactory.java:56)
> at
> org.ofbiz.entity.jdbc.ConnectionFactory.tryGenericConnectionSources
> (ConnectionFactory.java:92)
> at
> org.ofbiz.entity.transaction.JNDIFactory.getConnection
> (JNDIFactory.java:158)
> at
> org.ofbiz.entity.transaction.TransactionFactory.getConnection
> (TransactionFactory.java:104)
> at
> org.ofbiz.entity.jdbc.ConnectionFactory.getConnection
> (ConnectionFactory.java:82)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:
> 268)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
> (SQLProcessor.java:374)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement
> (SQLProcessor.java:358)
> at
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:549)
> at
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:520)
> at
> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey
> (GenericHelperDAO.java:90)
> at
> org.ofbiz.entity.GenericDelegator.findByPrimaryKey
> (GenericDelegator.java:1248)
> at
> org.ofbiz.entity.GenericDelegator.findByPrimaryKey
> (GenericDelegator.java:1304)
> at
> org.ofbiz.service.job.PersistedServiceJob.getJob
> (PersistedServiceJob.java:282)
> at
> org.ofbiz.service.job.PersistedServiceJob.init
> (PersistedServiceJob.java:129)
> at
> org.ofbiz.service.job.GenericServiceJob.exec(GenericServiceJob.java:
> 74)
> at org.ofbiz.service.job.JobInvoker.run(JobInvoker.java:226)
> at java.lang.Thread.run(Thread.java:534)
>
>
> Reproduce:
>
> The wait_timeout variable on the mysql db is set to 28800 seconds
> (8 hours)
> by default.
>
> If you set this to a much smaller amount, eg 10 seconds, then this
> error can
> be reproduced much quicker!
>
> set GLOBAL wait_timeout=10;
> set SESSION wait_timeout=10;
> set GLOBAL interactive_timeout=10;
> set SESSION interactive_timeout=10;
>
> Specs:
>
> opentaps-0.9.1-all-platforms
> mysql 5.0.22
> connector versions: 3.0.17, 5.0.4
>
> Solution:
>
> Have a look at the org.ofbiz.minerva.pool.ObjectPool and
> PoolGCThread class,
> the javadoc is fabulous. In particular look at the following
> methods, and
> also inspect the default values:
> ObjectPool.setGCEnabled(boolean);
> ObjectPool.setIdleTimeoutEnabled(boolean);
> ObjectPool.setTimestampUsed(boolean);
>
> In MinervaConnectionFactory the pools are created. Add the
> following lines:
>
> pds.setGCEnabled(true);
> pds.setIdleTimeoutEnabled(true);
> pds.setTimestampUsed(true);
>
> (I made my code take configuration parameters from
> entityengine.xml, see the
> jotmJdbcElement variable).
>
> Debug:
>
> base/config/debug.properties
> log4j.logger.org.ofbiz.minerva=DEBUG
>
> You can then see the thread working to recycle the connections.
>
>
> --
> View this message in context: http://www.nabble.com/MySQL-
> connection-problem-tf2486297.html#a9092103
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

smime.p7s (3K) Download Attachment