Cannot Login to H2 Console (Fresh Install)

[Donald Fisher]

After installing the H2 application today, I attempted to follow the tutorial and was presented with a login prompt:

Is there a default username and password for this?  The Console terminal looks like:

[Evgenij Ryazanov]

Hello.

You are not connected to H2 Console, this port is used by some other application.

You should have .h2.server.properties file in the home directory of your user. Open it with some text editor, find the line webPort=8082 and change the port number to a some free port. Then restart the H2 Server application.

[Donald Fisher]

Evgenij,

Thank you very much! The file .h2.server.properties did not exist, so I created it with only the following properties:

webAdminPassword=

webPort=

I can now log into the console and create local databases on my computer's hard disk; however, I am having trouble creating in-memory databases. I am trying to create an in-memory database by right-clicking the H2 Database Engine icon in the system try and selecting Create a new database.... For Database path, I input "~/testdb" and then input the password and password confirmation. I receive a confirmation that the database was created successfully:

When I input the database URL into the H2 Console, an error message is displayed:

If the in-memory database was created successfully, why is it that I cannot connect via the H2 Console?

[Evgenij Ryazanov]

Create a new database... command is not designed to create an in-memory database, it is for persistent databases only. Actually you can create an in-memory database with it, but it is removed immediately, because there are no active connections. You can use mem:testdb;DB_CLOSE_DELAY=-1 to prevent it, but usually it isn't reasonable unless you want to connect to that database remotely from other system.

H2 Console opens a browser window during startup, if you closed it, you can open a new one with H2 Console item of the tray icon. In this browser session created by H2 you will be able to create new databases without any additional actions and you will not be needed to specify a password for Preferences and Tools.

If you opened something like http://127.0.0.2:8082/ by yourself, you don't have such permissions in that session.

[Donald Fisher]

Evgenij,

Outstanding thank you! I was able to create an in-memory database and import a *.csv dataset but when I process a SELECT on it, only 1000 rows are returned. Why is this?

[Evgenij Ryazanov]

There is a Max rows combobox on the top.

[Donald Fisher]

Evgenij,

Perfect thank you!  It seems that the H2 Database application may not suit my needs after all; I am receiving an 'out of memory' error when attempting to import ~8,000,000 records (~1.9Gb) of data.

[Evgenij Ryazanov]

1. Databases usually need more space than CSV. You need to use a normal file-based database (jdbc:h2:/path/to/database) instead of in-memory one.

2. Built-in CSVREAD() function in recent versions of H2 reads all data into memory too. If I remember it well, its on-disk buffering was removed due to more critical issues and it isn't yet reimplemented.