Raspberry PI and H2 1.4.199

[Clint Hyde]

So of course I don’t know if this is a PI problem or a 199 problem.

On a PI, I want to run an H2 server (more for some testing purposes than anything, really).

So I have that working, and I have it set to be a systemctl background task. That’s good too.

Problem is: I literally cannot make a database there, using 199.

I constantly get:

Connect-string is "jdbc:h2:tcp://10.10.0.91:9092//var/h2-databases/foo"

org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database "/var/h2-databases/foo" not found, and IFEXISTS=true, so we cant auto-create it [90146-199]

Locally, on the PI itself, I cannot create a DB either, using the web-page interface. Same message about IFEXISTS=true

So I backed off to H2 1.3.175, and that works ok locally to create a DB. I fixed the Build Path in Eclipse to be the same H2 version—viola, all is ok. I can create a DB.

On my Mac, with version 196, I can create OK…so I’m going to try pushing that over to the PI.

What is going on with 1.4.199 ?

 — clint

[Evgenij Ryazanov]

Hello.

Security fixes were applied and H2 does not allow remote creation of database by default any more because it actually creates a remote security hole on your server.

Unfortunately, a weird error message is generated by the version that you use. 1.4.200 throws an error with better message.

You need to create a new database on the server side:

https://h2database.com/html/tutorial.html#creating_new_databases

You can also enable the remote database creation, but it is dangerous if somebody else can connect to your server. You need to guard that port.

[clint]

OK, I understand that...but the default is to disallow me to create one EVER. Nothing to do with remote, I can't even do it locally. There is no override.

I don't dislike the idea, but I dislike it defaulting to  "I'm not allowed to do anything"

If this needs to be a security thing, then it should be a command-line argument to control it on a per server basis, not the default that prevents me doing anything:

java -cp h2.jar -allowRemoteCreation [true|false]    org.h2.whatever

where false should be the old behavior, and true is the new behavior. And if you need it to default to false, that's fine, but let ME control it going the other way. Right now, I cannot do any such thing.

For another example: suppose it is the case that the server is remote, and I do not have access to the host machine at all. How do I get a DB created? Do I have to submit a request ticket to the sys admin, hope they aren't extra busy, and then wait for them to run some local creation program? They are not DB experts, so I more probably have to visit in person, let them log in, and then I do the create on their command-line. OR I have to receive SSH access. This is really not worth my time--but if I can't do a local create easily 

Also:

The documentation is inconsistent. It says I CAN (first paragraph: "By default, if the database specified in the embedded URL does not yet exist, a new (empty) database is created automatically. ") and then it says I CANNOT a few paragraphs later ("By default remote creation of databases from a TCP connection or a web interface is not allowed."). Reality: NO I CANNOT, and I couldn't find a way where I could.

Doc also says "the system tray" but that doesn't exist on Mac or PI (or other linux, I expect), especially if I start H2 as a background process. I seldom work on Windows. (I do see a menu-bar icon for H2 that allows this creation, apparently, when I started H2 via terminal window. That won't help when I start it at boot or via systemctl, or on a remote system)

I do private work on local/isolated systems, no one except me has either physical access or login access to the machines, or the server, so this limitation isn't helping me at all. I don't do things the way you are thinking I must.

When I am starting a project needing a DB, I tend to create and delete several times an hour FOR DAYS until I have stabilized the table defs (it's simpler to just delete the entire DB and reload the SQL file than to work out what is the functional order of drop and create and truncate).

So please, change this so that I can control it from the command-line that starts the server, rather than lock me out completely. 

I've been using H2 for over 10 years now, many projects, I like it, but this change is not good the way it is.

 -- clint

[Evgenij Ryazanov]

> If this needs to be a security thing, then it should be a command-line argument to control it on a per server basis

There is an unsafe command-line argument that allows remote database creation as it was in older versions, do you really read the whole “Creating New Databases” section? Just don't share that configuration with other people without telling them that if they won't guard the port, they will have a remote security hole.

> Doc also says "the system tray" but that doesn't exist on Mac or PI (or other linux, I expect)

It definitely works on Windows, OS X systems (since some old version), on desktop Linux systems, but builds for PI are different systems, no doubt; and on all Linux systems availability of system tray depends on desktop environment, some Windows systems can be launched without the GUI, and so on. However, this solution for developers' machines, not for servers. For remote servers there is the command-line Shell tool.

I'm not sure why you need to delete the database when you can simply execute DROP ALL OBJECTS.

https://h2database.com/html/commands.html#drop_all_objects