Intellij embedded H2 database tables do not appear

Question

I'm creating a Spring Boot application and I'm using Intellij's embedded h2 database.

I have added the following lines in my application.properties file:

spring.datasource.url=jdbc:h2:~/testdb;MV_STORE=false;AUTO_SERVER=TRUE

This is my data source configuration

Although the connection is successful and I can query the database using Intellij's query console, the tables do not appear in the Database tab.

Succeeded
DBMS: H2 (ver. 2.1.210 (2022-01-17))
Case sensitivity: plain=upper, delimited=exact
Driver: H2 JDBC Driver (ver. 2.1.210 (2022-01-17), JDBC4.2)
Ping: 16 ms

When I refresh the connection or go to the schemas tab of the data source configuration, I get the following error:

[42S02][42102] org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "INFORMATION_SCHEMA_CATALOG_NAME" not found; SQL statement: select CATALOG_NAME from INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME [42102-210].

Despain

You cannot use the MV_STORE setting in H2 2.0 and later versions, they throw an UnsupportedOperationException when this setting is used. It looks like some old version of H2 (1.4.*) is running and this database file is opened by it and new version tries to connect to that old version. That's why you don't see the UnsupportedOperationException on connection attempt and that's why you don't have INFORMATION_SCHEMA_CATALOG_NAME table, there is no such table in old unsupported versions of H2. – Puissant

@EvgenijRyazanov I changed it to jdbc:h2:~/testdb;AUTO_SERVER=TRUE and the issue persists. I'm still getting exactly the same error. Initially I didn't even have MV_STORE, I added it while trying to solve this error. – Despain

Do you have a complete stack trace? – Puissant

Intellij doesn't provide any more information about the error, the screenshot I've provided above contains all the information given by the program to the best of my knowledge. I don't know if there is some hidden log somewhere. – Despain

Answer 1

By going to the advanced tab of the data source and clicking on expert options, we are presented with a checkbox labeled "Introspect using JDBC metadata"

By checking that box, the tables successfully appear in the Database tab

Regarding why this works, this is taken from the official documentation: https://www.jetbrains.com/help/datagrip/data-sources-and-drivers-dialog.html

Introspect using JDBC metadata

Switch to the JDBC-based introspector. Available for all the databases.

To retrieve information about database objects (DB metadata), DataGrip uses the following introspectors:

A native introspector (might be unavailable for certain DBMS). The native introspector uses DBMS-specific tables and views as a source of metadata. It can retrieve DBMS-specific details and produce a more precise picture of database objects.

A JDBC-based introspector (available for all the DBMS). The JDBC-based introspector uses the metadata provided by the JDBC driver. It can retrieve only standard information about database objects and their properties.

Consider using the JDBC-based intorspector when the native introspector fails or is not available.

The native introspector can fail, when your database server version is older than the minimum version supported by DataGrip.

You can try to switch to the JDBC-based introspector to fix problems with retrieving the database structure information from your database. For example, when the schemas that exist in your database or database objects below the schema level are not shown in the Database tool window.

Despain

Sweet, thanks for that, @pitprok! Do you mind elaborating on why this works? – Gradus

I've added the official documentation to the answer. It seems to be because the native introspector fails for some reason (depends on the case), while the JDBC-based one uses the metadata provided by the JDBC driver and successfully shows the database objects. – Despain

It shows what seems like internal H2 tables and views, but it still doesn't show my tables created by hibernate.hbm2ddl.auto=create-drop. – Vanhorn