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)

OK... I mean - please be more specific. Why this doesn't work? What do you want to achieve? What have you tried so far? — Tomasz Nurkiewicz, Feb 16 '12 at 20:20
Please demonstrate the research, code, work you have done up to now. — Gray, Feb 16 '12 at 21:23

score 33 · Accepted Answer · edited Jun 21 '21 at 11:21

33

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.

edited Jun 21 '21 at 11:21

Aniruddh Parihar

3,072
3
21
39

answered Feb 17 '12 at 13:20

Thomas Mueller

48,905
14
116
132

1

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 ? – bvdb Jul 17 '15 at 23:50
No, that's not the case. See the documentation. – Thomas Mueller Jul 18 '15 at 18:45
11

@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. – Steve11235 Mar 18 '16 at 18:16
1

@Steve11235 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? – Thomas Mueller Mar 19 '16 at 19:20

score 26 · Answer 2 · edited Jun 26 '19 at 08:53

26

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

edited Jun 26 '19 at 08:53

Michael Piefel

18,660
9
81
112

answered Feb 10 '14 at 08:55

ERNESTO ARROYO RON

992
1
9
22

1

When using this approach, how do you increase the maximum number of allowed connections? – Koray Tugay Jul 29 '16 at 18:51
This one worked for me. Especially keep attention to the default TCP port: 9092 (can be adjusted using -tcpPort)! – Lars Apr 24 '17 at 13:01

score 14 · Answer 3 · edited Oct 22 '19 at 12:55

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.

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

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] — Justin.Cooke, Dec 07 '17 at 08:43

score 2 · Answer 4 · answered May 05 '17 at 06:39

2

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)

answered May 05 '17 at 06:39

Nimesha Kalinga

262
4
17

score 2 · Answer 5 · edited Nov 23 '18 at 03:52

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:

make dir mkdir h2db this directory will have your db files.
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
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/ &
Now run ./bin.h2.sh.

score 2 · Answer 6 · answered Jun 21 '21 at 04:53

There is one more way. You may define @Configuration bean, fg.: ' @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);
    }
}

} '

How to run H2 database in server mode?

6 Answers6

Linked

Related