Login/Register
Connecting to H2 server from DbVisualizer
Asked Answered
Solved h2dbvisualizer
P

3

9

I have configured my H2 database as follows:

@Configuration
@Profile({ Profiles.DEV })
public class DevDataSourceConfiguration {

    @Bean(initMethod = "start", destroyMethod = "stop")
    public Server h2WebServer() throws SQLException {
        return Server.createWebServer("-web", "-webAllowOthers", "-webPort", "8082");
    }

    @Bean(initMethod = "start", destroyMethod = "stop")
    @DependsOn("h2WebServer")
    public Server h2Server() throws SQLException {
        return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
    }

    @Bean
    @DependsOn("h2Server")
    public DataSource datasource() {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("org.h2.Driver");
        ds.setJdbcUrl("jdbc:h2:tcp://localhost/~/bignibou");
        return ds;
    }
}

I am now trying to access my database from DBVisualizer using the following jdbc URL: jdbc:h2:tcp://localhost/~/bignibou and I get the following error in dbvis logs:

2015-05-17 17:02:24.544 WARN   595 [ExecutorRunner-pool-3-thread-11 - G.ￄﾃ] Could not setAutoCommit(true) for: bignibou
org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
    at org.h2.engine.SessionRemote.setAutoCommitSend(SessionRemote.java:243)
    at org.h2.engine.SessionRemote.setAutoCommit(SessionRemote.java:210)
    at org.h2.jdbc.JdbcConnection.setAutoCommit(JdbcConnection.java:431)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
    at com.onseven.dbvis.h.B.F$A.call(Z:2515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
2015-05-17 17:02:24.546 INFO   595 [ExecutorRunner-pool-3-thread-11 - AbstractFacade.getCatalog] Could not getCatalog for: bignibou
org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcConnection.getCatalog(JdbcConnection.java:598)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
    at com.onseven.dbvis.h.B.F$A.call(Z:2515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

edit: Here is the log output I get after adding ;trace_level_system_out=3 to the connection url:

2015-05-26 12:25:13.601 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/Connection conn1 = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/bignibou;trace_level_system_out=3", "", "");
2015-05-26 12:25:13.602 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/DatabaseMetaData dbMeta1 = conn1.getMetaData();
2015-05-26 12:25:13.602 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.setAutoCommit(true);
2015-05-26 12:25:13.602 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: SESSION_PREPARE_READ_PARAMS 0
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: exception
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush] org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.get(DbException.java:178)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.get(DbException.java:154)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.setAutoCommitSend(SessionRemote.java:243)
2015-05-26 12:25:13.603 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.setAutoCommit(SessionRemote.java:210)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.jdbc.JdbcConnection.setAutoCommit(JdbcConnection.java:431)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at java.lang.reflect.Method.invoke(Method.java:497)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at com.onseven.dbvis.h.B.F$A.call(Z:2515)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
2015-05-26 12:25:13.604 INFO   159 [pool-3-thread-8 - Q.flush]  at java.lang.Thread.run(Thread.java:745)
2015-05-26 12:25:13.604 WARN   159 [ExecutorRunner-pool-2-thread-2 - G.ￄﾃ] Could not setAutoCommit(true) for: bignibou
org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
    at org.h2.engine.SessionRemote.setAutoCommitSend(SessionRemote.java:243)
    at org.h2.engine.SessionRemote.setAutoCommit(SessionRemote.java:210)
    at org.h2.jdbc.JdbcConnection.setAutoCommit(JdbcConnection.java:431)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
    at com.onseven.dbvis.h.B.F$A.call(Z:2515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
2015-05-26 12:25:13.605 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.isClosed();
2015-05-26 12:25:13.605 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.getAutoCommit();
2015-05-26 12:25:13.608 INFO   159 [ExecutorRunner-pool-2-thread-2 - Q.flush] 05-26 12:25:13 jdbc: 
/**/dbMeta1.getSearchStringEscape();
2015-05-26 12:25:13.608 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.isClosed();
2015-05-26 12:25:13.608 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.getAutoCommit();
2015-05-26 12:25:13.608 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.getCatalog();
2015-05-26 12:25:13.608 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: SESSION_PREPARE_READ_PARAMS 1
2015-05-26 12:25:13.608 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: exception
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush] org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.get(DbException.java:178)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.message.DbException.get(DbException.java:154)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at org.h2.jdbc.JdbcConnection.getCatalog(JdbcConnection.java:598)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at java.lang.reflect.Method.invoke(Method.java:497)
2015-05-26 12:25:13.609 INFO   159 [pool-3-thread-8 - Q.flush]  at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
2015-05-26 12:25:13.610 INFO   159 [pool-3-thread-8 - Q.flush]  at com.onseven.dbvis.h.B.F$A.call(Z:2515)
2015-05-26 12:25:13.610 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2015-05-26 12:25:13.610 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
2015-05-26 12:25:13.610 INFO   159 [pool-3-thread-8 - Q.flush]  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
2015-05-26 12:25:13.610 INFO   159 [pool-3-thread-8 - Q.flush]  at java.lang.Thread.run(Thread.java:745)
2015-05-26 12:25:13.610 INFO   159 [ExecutorRunner-pool-2-thread-2 - AbstractFacade.getCatalog] Could not getCatalog for: bignibou
org.h2.jdbc.JdbcSQLException: Connection is broken: "unexpected status 16777216" [90067-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.engine.SessionRemote.done(SessionRemote.java:621)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:69)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:476)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcConnection.getCatalog(JdbcConnection.java:598)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.onseven.dbvis.h.B.C.ￄﾁ(Z:2206)
    at com.onseven.dbvis.h.B.F$A.call(Z:2515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
2015-05-26 12:25:13.611 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.isClosed();
2015-05-26 12:25:13.611 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/conn1.getAutoCommit();
2015-05-26 12:25:13.611 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/Statement stat1 = conn1.createStatement();
2015-05-26 12:25:13.611 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: 
/**/ResultSet rs1 = stat1.executeQuery("call schema()");
2015-05-26 12:25:13.611 INFO   159 [pool-3-thread-8 - Q.flush] 05-26 12:25:13 jdbc: SESSION_PREPARE_READ_PARAMS 2

edit 2: I also noticed this (in the application console logs):

05-26 12:25:13 database: connecting session #14 to /Users/julien/bignibou
05-26 12:25:13 jdbc[14]: 
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
05-26 12:29:04 jdbc[13]: 
/*SQL */ROLLBACK;
05-26 12:29:04 jdbc[14]: 
/*SQL */ROLLBACK;
05-26 12:29:04 database: disconnecting session #14
05-26 12:29:04 database: disconnected session #14
05-26 12:29:04 database: disconnecting session #13
05-26 12:29:04 database: disconnected session #13
Phoenician answered 17/5, 2015 at 15:3 Comment(6)
Appending ";trace_level_system_out=3" temporarily to the database URL may help for debugging purpose – Schaaf
HI AydinK: I have edited my post. – Phoenician
DBVisualizer is implemented in Java. Are you able to connect to the DB via plain old jdbc? – Protolithic
Yes I am connecting to the db by using jdbc. I think that's the only way with dbvis. – Phoenician
Two things I noticed: apparently the exception is thrown when setting the auto_commit connection parameter and you're apparently you're mixing tcp with local access in the connection string. Try changing the connection string to jdbc:h2:tcp://localhost:9092. – Protolithic
What version of DBVisualizer are you using? – Permissive
C
12
  • Latest H2 stable release: 1.3.176.
  • Latest dbVisualizer H2 driver: 1.3.176.

Probably you are using a the beta release of H2 (1.4.x) with some bug in the client/server communication (mismatching versions should not be a problem because the client and server negotiate which protocol version to use) try using the latest beta release from H2 or use the latest stable release of H2.

Coverall answered 26/5, 2015 at 14:47 Comment(1)
That was it: I just had to change from 1.4.xx to 1.3.176 and it works fine now. – Phoenician
R
3

Check the version of h2 JDBC driver you are using in DBVisualizer. Specifically, check if matches the version you are using with your server. While it normally shouldn't matter that the versions match, there were issues with H2 a while back that would cause these sorts of errors when using mismatched versions.

If that does not help, you might want to also report exactly which version of H2 you are using on both client and server.

Radu answered 26/5, 2015 at 14:17 Comment(1)
It's brittle. If the H2 JDBC driver does not match the driver on the server side then you can get the error message "Connection is broken:" "unexpected status 16777216". It's a cryptic error message. – Shortcoming
M
0

I had the same issue. I had H2 installed from http://h2database.com and grails app was throwing:

[localhost-startStop-1] ERROR StackTrace  - Full Stack Trace:
org.h2.jdbc.JdbcSQLException: Connection is broken: "java.net.ConnectException: Connection refused: connect: localhost" [90067-176]

As it turned out my H2 server version was 1.4.196 and I had 1.3.176 loaded as grails dependency. I had update BuildConfig.groovy to make it work:

dependencies {
    ...
    test "org.grails:grails-datastore-test-support:1.0.2-grails-2.4"
    runtime "com.h2database:h2:1.4.196"
}
Mullion answered 20/10, 2018 at 12:28 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.