Connection using tcp

[Walker]

Hi everyone,

I am new to this and so please pardon me if the question is very
silly.

I have a webapp running under Tomcat from the folder, say, TestApp.
Under this, I have a folder called data in which I create a H2
database called TestDB.

I can access this from a JSP page using a URL like:

jdbc:h2:file:./webapps/TestApp/data/TestDB

and everything works fine.

However, this allows only a single connection. Now, if I start H2 as a
service and run it, I can access the same database from the H2 browser
console on the local machine (the one running both Tomcat and H2).

My question is:

How do I sepcify the URL in JSP to connect to this database using
tcp://...etc. I presume that is the only way to get multiple
connections to the database.

Thanks in advance.

Regards,

Walker

[Thomas Mueller]

> I can access this from a JSP page using a URL like:
>
> jdbc:h2:file:./webapps/TestApp/data/TestDB

This is a relative path. This works, but you need to make sure it is
always started from the same working directory.

> However, this allows only a single connection.

Actually, multiple connections, but all within the same process and
class loader.

> How do I sepcify the URL in JSP to connect to this database using
> tcp://...etc.

Remote connections:
jdbc:h2:tcp://localhost/~/test

(this is using the user home directory).

> I presume that is the only way to get multiple
> connections to the database.

No, you can also use the mixed mode: use the database in embedded mode
in the web application, start a TCP server in the web application and
connect remotely from the browser or another web application. See
also:

Regards,
Thomas

[Thomas Mueller]

Sorry, see also: src/test/org/h2/samples/MixedMode.java at
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/samples/MixedMode.java

There is also org.h2.server.web.WebServlet

Regards,
Thomas

[Thomas Mueller]

Hi Thotheolh,

> If i have two computers and one computer acting as a server runs the
> tcp server. Can I use the other computer to access the server using
> tcp over the network ?

Yes, however this is not allowed for security reasons. To enable it,
start the server with the command line option -tcpAllowOthers as in:

java ... org.h2.tools.Server -tcp -tcpAllowOthers

or in Java code:
Server server = Server.createTcpServer(
new String[] { "-tcpAllowOthers" }).start();

Regards,
Thomas

[Thotheolh]

Thanks. One thing, can I use SSL with the TCP allow others mode ?
Maybe it would become more secure.

[Thomas Mueller]

Hi,

> Thanks. One thing, can I use SSL with the TCP allow others mode ?
> Maybe it would become more secure.

Yes, you can do that.

Regards,
Thomas

[Thom Pischke]

Hi Thomas,

We're now in the process of moving to a dedicated DB server over
remote tcp instead of using the database engine in embedded mode.
I've been able to get it working, but I'm not quite sure what best
practice is for starting and connecting to the Server. What I'm doing
now, which works, is to start the database server from the directory
containing the database itself.

Thus if my database is in /mnt/db/ and is named 'data', I run the
server from after issuing cd /mnt/db.

Then to connect, I use the following url: jdbc:h2:tcp://<server ip>:
9092/data

So, my question is: Is this a good way to do things? How do you
handle this personally?

Thanks for any guidance you can provide,
Thom Pischke

On May 14, 9:01 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

[Thotheolh]

Maybe you might want to use SSL over the TCP too, Pischke ? It's best
to protect a remote db connection.

[Thom Pischke]

Yes, you're right, thanks for the tip.   Will work on switching it to SSL today.  I had problems getting ANY connection working at first, so SSL was one of the first things I tossed out the window when trying to simplify the problem.  I hadn't seen the bit about the different connection url for SSL.

[Thom Pischke]

Tried enabling SSL.  No Dice.  Just fails to connect.   Added -tcpSSL to the server startup command, and replaced 'tcp' with 'ssl' in the connection URL.  What am I missing?

[Thom Pischke]

So starting the server like this from the directory containing the 'data' database files.

java -cp /home/h2/h2/bin/h2.jar org.h2.tools.Server -tcp -tcpAllowOthers -tcpSSL &

and connecting remotely like this:

jdbc:h2:ssl://<server dns>:9092/data

As far as I can tell, the database never is even reached.  Connection just fails.  But it works fine without SSL.

[Thom Pischke]

Ok, checked this one more time.  If I use the exact same setup as with the working vanilla TCP connection, but add -tcpSSL to the server startup, and change tcp to ssl in the client, things just stop working.  So seems there's a bug in the code, or some undocumented requirement for connecting via SSL.

[Thom Pischke]

Might be worth noting that both the servers involved are running on Ubuntu 8.04 Server with the sun-java6-jre package installed.  Both are using the latest version of the h2 engine.

[Thom Pischke]

Ah, probably also worth noting that the connection is being established by Hibernate, which in turn uses the c3p0 connection pool.  Maybe there's some issue with using SSL connections with Hibernate/c3p0...

Here's the exception I get:

WARN[2008-07-11 02:35:29,390] - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@11fc4b8 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30).
WARN[2008-07-11 02:35:29,423] - Could not obtain connection metadatajava.sql.SQLException: Connections could not be acquired from the underlying database!
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:104)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:236)
        at com.mchange.v2.c3p0.PoolBackedDataSource.getConnection(PoolBackedDataSource.java:94)
        at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:56)
        at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)
        at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2073)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1298)
        at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:915)
        at sb.util.db.HibernateUtil.<clinit>(HibernateUtil.java:34)
        at sb.dao.DaoManager.isTransactionStarted(DaoManager.java:65)
        at sb.dao.DaoManager.startTransaction(DaoManager.java:49)
        at com.shellbook.web.util.KeyReminder.checkKeys(KeyReminder.java:55)
        at com.shellbook.web.util.KeyReminder.run(KeyReminder.java:38)
        at java.lang.Thread.run(Thread.java:619)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAcquire(BasicResourcePool.java:970)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:208)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:232)
        ... 12 more

[Thotheolh]

Hmmm.. your stack trace doesn't seem to indicate anything about H2.
Maybe Thomas Mueller would be able to looked through that one and
comment.

Have you ever tried to establish an SSL using just the plain H2 Shell
on two machines ?

[Thotheolh]

I am not sure if SSL does accept non-localhost connections since I do
most access of SSL from localhost.

[Thom Pischke]

Why bother with SSL if you're not going out over the network?

[Thom Pischke]

Okay, tested this using vanilla JDBC connection. SSL works just fine
if I create a connection using straight up JDBC, meaning the problem
with SSL likely lies somewhere within the hibernate framework.
Sigh... this is going to take some digging.

On Jul 12, 12:40 pm, "Thom Pischke" <thom.pisc...@gmail.com> wrote:
> Why bother with SSL if you're not going out over the network?
>

[Thom Pischke]

Just documenting my findings here, as I continue investigating this.
Aren't many threads on the topic, so this might help someone someday.

So after verifying that remote SSL connections work fine from JDBC, I
decided to try testing local tcp connections. I find that things work
fine, even with SSL, if I run the DB server daemon and our hibernate
webapp on the same physical server, and access the db via localhost.
This points to some issue in the network itself, assuming that no
certificate setup is needed to get a basic connection working.

On Jul 15, 3:04 pm, Thom Pischke <thom.pisc...@gmail.com> wrote:
> Okay, tested this using vanilla JDBC connection.  SSLworks just fine

> if I create a connection using straight up JDBC, meaning the problem

> withSSLlikely lies somewhere within the hibernate framework.

> Sigh...  this is going to take some digging.
>
> On Jul 12, 12:40 pm, "Thom Pischke" <thom.pisc...@gmail.com> wrote:
>

> > Why bother withSSLif you're not going out over the network?

>
> > On Fri, Jul 11, 2008 at 3:19 PM, Thotheolh <twzger...@gmail.com> wrote:
>

> > > I am not sure ifSSLdoes accept non-localhost connections since I do
> > > most access ofSSLfrom localhost.

[Thom Pischke]

Okay, solved this issue. Here's what was going on.

When trying to make an SSL connection, h2 needs to create (or modify)
a keystore in the user's home directory. Since our app runs in
tomcat, as a user called 'tomcat', h2 was trying to create a keystore
in the home directory for tomcat, which in my case was a system
directory on the server with root ownership. Since the keystore could
not be created, the connection failed, with unhelpful log messages to
the effect of 'connection could not be established.'

So the biggest issue here is probably simply the uselessness of the
log/exception produced by c3p0 when it failed to establish the
connection. It doesn't even propagate the error information received
from the h2 driver, unfortunately.

Anyway, I now have ssl working fine remotely from a tomcat web server
to a remote db server, both running on ubuntu.

[Thomas Mueller]

Hi,

> When trying to make an SSL connection, h2 needs to create (or modify)
> a keystore in the user's home directory. Since our app runs in
> tomcat, as a user called 'tomcat', h2 was trying to create a keystore
> in the home directory for tomcat, which in my case was a system
> directory on the server with root ownership. Since the keystore could
> not be created, the connection failed, with unhelpful log messages to
> the effect of 'connection could not be established.'

The error message in this case is really bad: H2 just swallows the
real exception. I will change the code so that analyzing such problems
will be much simpler in the next release.

Thanks a lot for your help!

Regards,
Thomas