You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Hans Holmlund <ha...@excito.se> on 2006/11/01 11:25:16 UTC
Re: MySQL connection problem
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:
>
> 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
>>>>> sichen@opensourcestrategies.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
Re: [2] MySQL connection problem
Posted by "David E. Jones" <jo...@hotwaxmedia.com>.
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.
>
Re: [2] MySQL connection problem
Posted by Katherine Morgan <km...@tenzeng.com>.
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.
--
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.