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
[EMAIL PROTECTED]
