Unable to connect to H2 using DbVisualizer

[chungonn]

Hi,

I am tried to connect to Play's embedded H2 db using DbVisualizer but
fail. I tried changing Play's H2 settings but didn't help. Connecting
to H2's console is fine though.

Any help on this is greatly appreciated.

Here's my setting in application.conf

%test.jpa.ddl=update
%test.jpa.dialect=org.hibernate.dialect.H2Dialect
%test.db.url=jdbc:h2:mem:play;AUTO_SERVER=TRUE;MODE=MYSQL;LOCK_MODE=0;DB_CLOSE_DELAY=-1
%test.db.driver=org.h2.Driver
%test.db.user=sa
%test.db.pass=

Here's the db.url from DbVisualizer
jdbc:h2:tcp://localhost/mem:play

Below is the error from DbVisualizer
An error occurred while establishing the connection:

Long Message:
Connection is broken: "Connection refused" [90067-149]

Details:
Type: org.h2.jdbc.JdbcSQLException
Error Code: 90067
SQL State: 90067

[chungonn]

I managed to solve the problem after spending many hours on this
issue. Both of you are right and here is my problem. I can only start
and an embedded server db but not in memory. Hence my url should be

%test.db.url=jdbc:h2:play;AUTO_SERVER=TRUE;MODE=MYSQL;LOCK_MODE=0;DB_CLOSE_DELAY=-1

then I need to connect to the db using info from the .lock.db file. In
my case the url is
jdbc:h2:tcp://
172.20.10.2:49434/12ff45a8d5228ea09fa5b848476c84f20625b0e01fc

Though now I can connect to the play db now but DBVisualizer is
complaining about the missing 'public' schema when i select the
tables. Any thoughts?

Regards
chungonn

[chungonn]

I managed to get DbVisualizer working with H2 with MySQL mode. The
trick is to set database type to 'MySQL'.

[Thomas Mueller]

Hi,

Sorry for the delay. You are right, this is a bug. The auto-server
mode can't be combined with an in-memory database, and this invalid
combination wasn't detected so far. The next version will throw an
exception if you try to open a database in this way.

> %test.db.url=jdbc:h2:play;AUTO_SERVER=TRUE;MODE=MYSQL;LOCK_MODE=0;DB_CLOSE_DELAY=-1
>
> then I need to connect to the db using info from the .lock.db file. In
> my case the url is
> jdbc:h2:tcp://
> 172.20.10.2:49434/12ff45a8d5228ea09fa5b848476c84f20625b0e01fc

If you use the full file path in the URL, this is not required:

jdbc:h2:/path/to/database/play;AUTO_SERVER=TRUE;MODE=MYSQL;LOCK_MODE=0;DB_CLOSE_DELAY=-1

> Though now I can connect to the play db now but DBVisualizer is
> complaining about the missing 'public' schema when i select the
> tables.

I guess the problem is lowercase identifiers in the DatabaseMetaData
methods when using the MySQL mode. This is a known problem which I
hope I can soon fix. The workaround is to either not use the
MODE=MySQL when using DbVisualizer, or use the MySQL mode in
DbVisualizer as you did.

Regards,
Thomas