How to run H2 database in server mode?

Question

I need to start H2 database in server mode from my application. Having tried the following code:

server = Server.createTcpServer().start();

Here is the properties for the connection:

javabase.jdbc.url = jdbc:h2:tcp://localhost:9092/nio:~/source/db/database/db;AUTO_SERVER=TRUE
javabase.jdbc.driver = org.h2.Driver
javabase.jdbc.username = sa
javabase.jdbc.password =

When I run the program, I got the following error:

client.db.exception.DAOException: org.h2.jdbc.JdbcSQLException: Database may be already in use: "Locked by another process". Possible solutions: close all other connection(s); use the server mode [90020-164]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.store.FileLock.getExceptionAlreadyInUse(FileLock.java:439)
    at org.h2.store.FileLock.lockFile(FileLock.java:336)
    at org.h2.store.FileLock.lock(FileLock.java:128)
    at org.h2.engine.Database.open(Database.java:542)
    at org.h2.engine.Database.openDatabase(Database.java:222)
    at org.h2.engine.Database.<init>(Database.java:217)
    at org.h2.engine.Engine.openSession(Engine.java:56)
    at org.h2.engine.Engine.openSession(Engine.java:159)
    at org.h2.engine.Engine.createSessionAndValidate(Engine.java:138)
    at org.h2.engine.Engine.createSession(Engine.java:121)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:133)
    at java.lang.Thread.run(Thread.java:680)

Shaynashayne

h2database.com/html/tutorial.html#using_server – Centuple

This doesn't work for me – Shaynashayne

OK... I mean - please be more specific. Why this doesn't work? What do you want to achieve? What have you tried so far? – Centuple

Please demonstrate the research, code, work you have done up to now. – Barbuto

Answer 1

As the exception message says, "Database may be already in use". You need to close all other connection(s), to make sure the database is not open in another process concurrently.

By the way, don't use AUTO_SERVER=TRUE and the server mode at the same time. See the documentation for the automatic mixed mode. Use either one.

I guess you are a bit confused about the different connection modes. I suggest to read the documentation about the connection modes, to make sure you understand it.

Ungrudging

The exception message gives the impression that running the database in embedded mode allows only 1 connection, while putting it in server mode will allow multiple connections. Isn't that the case ? – Soembawa

No, that's not the case. See the documentation. – Ungrudging

@Thomas--I've scanned a number of posts, and I keep seeing you say to look at the documentation. Please don't do that. The documentation, especially around startTcpServer, is sparse at best. The server starts, and it can be shutdown remotely, but it refuses all connections. – Stepaniestepbrother

@Stepaniestepbrother I wrote "see the documentation" for the question "embedded mode allows only 1 connection". This is clearly documented. The TcpServer might not be documented well enough, that's true. But this question here is not about the TcpServer. Maybe you should write your own question? – Ungrudging

Answer 2

From command line,

java -jar h2-1.3.160.jar -webAllowOthers -tcpAllowOthers

this will launch an h2 database in server mode:

Web Console server running at http://A.B.C.D:8082 (others can connect)
TCP server running at tcp://A.B.C.D:9092 (others can connect)
PG server running at pg://A.B.C.D:5435 (only local connections)

open a browser to have an admin GUI

Moultrie

When using this approach, how do you increase the maximum number of allowed connections? – Pinhole

This one worked for me. Especially keep attention to the default TCP port: 9092 (can be adjusted using -tcpPort)! – Awl

Answer 3

You can use the following code to run H2 in server mode.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:target/h2/ps;AUTO_SERVER=TRUE" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

You can use SQuirrel SQL client (http://squirrel-sql.sourceforge.net/) to connect to you H2 database and look at the tables.

1. Create new connection.
2. Select H2 in the driver dropdown menu
3. Set url to your project target folder h2 folder (jdbc:h2:C:\projects\workspace\TestProject\target/h2/ps;AUTO_SERVER=true)
4. Enter user name ("sa")
5. Enter password ("")

Cali

org.h2.jdbc.JdbcSQLException: A file path that is implicitly relative to the current working directory is not allowed in the database URL "jdbc:h2:target/h2/ps;AUTO_SERVER=TRUE". Use an absolute path, ~/name, ./name, or the baseDir setting instead. [90011-196] – Oversleep

Answer 4

Close all the applications that using H2 (web console, etc) Then add the AUTO_SERVER=TRUE to the end of the location in h2 console and also in java program (which you already have done)

Vocative

Answer 5

I was getting this error when trying to start H2.
See also http://h2database.com/javadoc/org/h2/tools/Server.html

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Feature not supported: "-~webAllowOthers" [50100-197]

So I followed these steps:

1. make dir mkdir h2db this directory will have your db files.
2. Hit this command : java -cp bin/h2-1.4.197.jar org.h2.tools.Server -web -webAllowOthers -tcp -tcpAllowOthers -baseDir /home/manoj/dev/h2/h2db_6.0
   this command will start h2
3. If you want to run h2 in backend then open vi h2.sh and paste this command in this: nohup java -cp bin/h2-1.4.197.jar org.h2.tools.Server -web -webAllowOthers -tcp -tcpAllowOthers -baseDir /home/manoj/dev/h2/h2db_6.0/ &
4. Now run ./bin.h2.sh.

Dolly

Answer 6

There is one more way. You may define @Configuration bean, e.g.:

@Configuration
public class H2Configuration {

    @Bean
    public void startTCPServer(){
        try {
            Server h2Server = Server.createTcpServer().start();
            if (h2Server.isRunning(true)) {
                System.out.println(h2Server.getStatus());
            } else {
                throw new RuntimeException("Could not start H2 server.");
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to start H2 server: ", e);
        }
    }
}

Julesjuley