linked table connection lost

[kimyx]

We're using h2 (-1.2.130.jar) in a jboss server environment, where the
main database is mysql and h2 primarily provides a way to alias
various in-memory artifacts. Also, some of the mysql tables are set up
as linked tables like this:

create linked table
attribute('javax.naming.InitialContext','java:UIDatasource',NULL,NULL,'ATTRIBUTE')
READONLY

This works fine initially. However, if the jdbc connection from the h2
linked table to the jndi datasource is lost, it appears that it will
not reconnect.

I've set the jndi datasource connection parameters to avoid idle-
timeout, but sometimes the connection is lost anyway, leading to the
following 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(WrappedPreparedStatement.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)

Is there a way to automatically reconnect when this happens?

We can set up similar functionality using an alias that makes the
connection every time, but it is slower and doesn't get indexed like a
linked table.

Thanks,
Kim

[Dario Fassi]

Hi,
AFAIK, if external connection goes down, then linked table access throw and DbException thar wrap the original SQLException and not try to do anything more.
If you need more control over this external connection you will need do all the job in an UDF.
You can request a new feature add for auto-reconnect of a linked connection as a or try to patch yourself.

Anyway you have a real access problem to your MySql form org.jboss.resource.adapter.jdbc , I would dig this problem first.

regards,
Dario

El 02/12/10 14:26, kimyx escribi�:

[kimyx]

Thanks Dario. UDFs are working well for us but I like the fact that
the link table has indexes defined when doing joins within h2. I have
a nice h2 UDF that supports doing any mysql query and it is good for
many situations.

We have the following in our jboss datasource, which normally takes
care of providing a new connection when the socket is broken:

<exception-sorter-class-
name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</
exception-sorter-class-name>
<valid-connection-checker-class-
name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</
valid-connection-checker-class-name>

I think h2 circumvents this by keeping a particular connection object
around for the life of the server. I tried setting
h2.shareLinkedConnections to false but it didn't help with this issue.

I'll try fiddling with some mysql parameters. Otherwise, I have the h2
source checked out and compiling, so maybe I'll try patching it.

Regards,
Kim

[Dario Fassi]

Hi,

El 03/12/10 20:56, kimyx escribi�:

> UDFs are working well for us but I like the fact that
> the link table has indexes defined when doing joins within h2.

If your UDF return an ResultSet (or constructed SimpleResultSet class), that can be used as a table too for anything you want.

> We have the following in our jboss datasource, which normally takes
> care of providing a new connection when the socket is broken:
>
> <exception-sorter-class-
> name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</
> exception-sorter-class-name>
> <valid-connection-checker-class-
> name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</
> valid-connection-checker-class-name>
>
> I think h2 circumvents this by keeping a particular connection object
> around for the life of the server. I tried setting
> h2.shareLinkedConnections to false but it didn't help with this issue.

Interesting approach, but that create a new connection that don't replace wrapped connection in linked table.
Linked tables start unconnected until the first access and remain connected for session's life.

So, what's lacking here is something that can detect connection lost,reconnect and renew waped connection , on internal methods of linked tables that make use of external conn. .

> I'll try fiddling with some mysql parameters. Otherwise, I have the h2
> source checked out and compiling, so maybe I'll try patching it

Would be an useful an elegant extension of this feature.

Regards,
Dario

[kimyx]

Hi all. I have a patch relative to 1.2.147 that solves this problem.
I'm going to write an issue and attach the patch on googlecode.