search

google-code-export / h2database

Automatically exported from code.google.com/p/h2database
0 stars 1 forks source link

Linked table nonfunctional when backside connection lost #265

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
We use h2(-1.2.130.jar or -1.2.147.jar) as a front end for jointly querying 
derived data artifacts and raw mysql data in a jboss server environment. Among 
other things we set up linked tables like this:

create linked table 
attribute('javax.naming.InitialContext','java:UIDatasource',NULL,NULL,'ATTR­IBU
TE') 
READONLY 

where ATTRIBUTE is a mysql table accessible via a jboss jndi datasource.

This works fine initially. The timeout parameters of the jboss datasource and 
mysql connections are set so the jdbc connection between h2 and the mysql 
database never times out. However, if there is any kind of network glitch, this 
backside connection goes down and h2 will never reconnect, resulting in this 
error:

Error accessing linked table with SQL statement "SELECT * FROM 
ATTRIBUTE T", cause: "com.mysql.jdbc.CommunicationsException: 
Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException 
MESSAGE: Connection reset 

STACKTRACE: 

java.net.SocketException: Connection reset 
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96) 
at java.net.SocketOutputStream.write(SocketOutputStream.java:136) 
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java: 
65) 
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) 
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) 
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283) 
at 
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java: 
1332) 
at 
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 
1467) 
at 
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(Wrapp­edP
reparedStatement.java: 
342) 
at org.h2.index.LinkedIndex.find(LinkedIndex.java:127) 
at org.h2.index.IndexCursor.find(IndexCursor.java:127) 
at org.h2.table.TableFilter.next(TableFilter.java:253) 
at org.h2.command.dml.Select.queryFlat(Select.java:488) 
at org.h2.command.dml.Select.queryWithoutCache(Select.java:555) 
at org.h2.command.dml.Query.query(Query.java:233) 
at org.h2.command.CommandContainer.query(CommandContainer.java:80) 
at org.h2.command.Command.executeQuery(Command.java:132) 
at org.h2.server.TcpServerThread.process(TcpServerThread.java:272) 
at org.h2.server.TcpServerThread.run(TcpServerThread.java:131) 
at java.lang.Thread.run(Thread.java:619) 

h2 needs a way to reconnect in this situation without bouncing the server or 
recreating the table.

Original issue reported on code.google.com by kimkokko...@gmail.com on 10 Dec 2010 at 4:50

GoogleCodeExporter commented 9 years ago 
I have a patch that solves this issue, relative to tag 1.2.147. I will attach 
it here shortly. We'd definitely like it to become part of the h2 code going 
forward.

It introduces a new connection-time option RECONNECT_LINKED_TABLES, which 
defaults to false. When set to true and a linked table is created on this 
connection, if the backside jdbc connection later throws an exception, it is 
reported to the client accessing the linked table. At this point the backside 
connection is set to null and when the linked table is accessed again, the 
backside connection is recreated. This ensures the client is aware of a problem 
(in case a multi-step transaction is in progress) but solves the problem for 
next time.

We use this option on the connection that creates the linked tables, which are 
then exposed via the h2 TCPServer.

Original comment by kimkokko...@gmail.com on 10 Dec 2010 at 4:58

GoogleCodeExporter commented 9 years ago 
Attached is a patch file.

I couldn't see a good way to add test cases for this. However, it passes all 
the existing tests with the new option turned on or off. If you have an idea 
for a test case let me know and I'll be happy to help.

Original comment by kimkokko...@gmail.com on 10 Dec 2010 at 5:15

Attachments:

GoogleCodeExporter commented 9 years ago 
P.S. I couldn't get DbSettings to work reliably. It appears that the singleton 
instance is always instantiated with default settings and its values are not 
updated via connection properties. So I captured a new boolean 
reconnectLinkedTables from the ConnectionInfo when the Database is created.

Original comment by kimkokko...@gmail.com on 10 Dec 2010 at 5:19

GoogleCodeExporter commented 9 years ago 
Here's another patch file. I've run the installer build (in pieces because I 
don't have jdk 1.5 installed) and fixed some spelling/formatting issues.

Original comment by kimkokko...@gmail.com on 10 Dec 2010 at 8:45

Attachments:

GoogleCodeExporter commented 9 years ago 
Sorry, I didn't have a look at the patch yet... But it seems wrong to 'fix' H2 
if the problem is really MySQL losing the connection. What about solving the 
original problem? I found this:

http://stackoverflow.com/questions/2548137/auto-reconnect-of-database-connection

Original comment by thomas.t...@gmail.com on 14 Dec 2010 at 8:44

GoogleCodeExporter commented 9 years ago 
We can't use the autoReconnect option for all our mysql connections. This is 
generally not recommended anyway. See the autoReconnect property here:

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-prope
rties.html

I've tried all the other options I can find and none helped to solve this 
problem. Our direct JPA/Hibernate/jdbc connections to mysql recover fine after 
brief connection losses, but H2 link tables do not.

Thanks for taking a look.

Original comment by kimkokko...@gmail.com on 15 Dec 2010 at 3:08

GoogleCodeExporter commented 9 years ago 
P.S. We use the standard connection tests in our jndi data source so that each 
connection is tested before it is handed out from the jboss pool. I think the 
issue with the H2 link table is that it keeps the same connection for as long 
as the server is up instead of closing it and thus returning it to the jboss 
pool. The patch simply gets a new connection whenever it finds the old one has 
failed.

Original comment by kimkokko...@gmail.com on 15 Dec 2010 at 3:18

GoogleCodeExporter commented 9 years ago 
More thoughts on this? I still believe it deserves a patch.

Original comment by kimkokko...@gmail.com on 22 Jan 2011 at 5:47

GoogleCodeExporter commented 9 years ago 
Hi,

As far as I understand the patch, it will throw an exception when there is a 
problem, but re-connect the next time? Wouldn't it be better if the statement 
is re-run?

Also, the patch seems to re-connect on any kind of exception. I believe it 
should only do that if the connection is really broken. Does anybody know what 
is the error code, SQL state, and error message on a broken connection? 

I wonder if there is a way to solve this problem without a new connection 
property... 

I guess each call would have to be wrapped so that the connection is re-opened 
if necessary (re-run the statement in this case). So basically, each call would 
be a loop. There are actually not _that_ many calls so this should be possible 
(well instead of a loop, some kind of a 'closure' should be used).

Original comment by thomas.t...@gmail.com on 27 Jan 2011 at 8:00

GoogleCodeExporter commented 9 years ago 
Hi Thomas,

Yes, the patch allows the initial exception to escape. This is so that the 
client knows that an error has occurred and can take appropriate steps before 
retrying. This is especially important if the client is in the midst of a 
transaction including writes to other tables.

I agree it would be nicer to reconnect only for a connection-lost exception but 
I wasn't sure of all the types that might occur. The SocketException is the 
only one we've seen but we are always using a tcp/ip mysql connection through 
jboss pooling. Does it hurt to rebuild the connection every time an error 
occurs?

This might become the standard behavior without any property to enable it. I 
don't see any downside to having it be the standard but I didn't want to change 
the default behavior without discussion.

I think the retry loop should be the responsibility of the client, as long as 
the h2 backside connection can be recovered without restarting h2. What if the 
mysql database actually went down? The h2 code shouldn't have to make decisions 
about how long to retry and the client shouldn't wait indefinitely for its 
request to return.

Original comment by kimkokko...@gmail.com on 29 Jan 2011 at 4:39

GoogleCodeExporter commented 9 years ago 
Patch_265

Original comment by CrazyBea...@gmail.com on 9 Sep 2011 at 9:48

GoogleCodeExporter commented 9 years ago 
Hi,

I think yo wanted to attach a new patch, but I only see a comment "Patch_265". 
It looks like uploading didn't work somehow. Could you re-upload it please?

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 12 Oct 2011 at 5:18

GoogleCodeExporter commented 9 years ago 
I will reopen if you upload the patch.

Original comment by thomas.t...@gmail.com on 17 Feb 2012 at 1:41

GoogleCodeExporter commented 9 years ago 
You fixed this in a different way with your patch_265 last September. It solved 
the problem for us. Thanks!

Original comment by kimkokko...@gmail.com on 17 Feb 2012 at 5:02

GoogleCodeExporter commented 9 years ago 
Thanks! Good to know the problem is solved for your.

Original comment by thomas.t...@gmail.com on 17 Feb 2012 at 6:00