How to fix: Embedded H2 Database "NonTransientError: Unable to read the page at position" error?

h2 database embedded java example
h2 in-memory database spring example
h2 database persistence
h2 create database
h2 database viewer
h2:file database
h2 database download
view h2 database

I am creating a JavaFX program with an embedded H2 database that will be used to handle user logins and passwords. Using Intellij Ultimate, I have a database that I can run from the toolbar. In addition, I am almost certain I have the correct JDBC driver and URL. The database runs fine from Intellij's database console. The error occurs when I try to access the database with Java code. I am using a database class to handle my database connection.

I am receiving a JdbcSQLNonTransientException, General error:

Illegal state exception: unable to read the page at position

Caused by: java.lang.IllegalStateException: Unsupported type 17.

The line of code that is shown in my compiler, causing the error: Connection conn = DriverManager.getConnection(DB_URL, "sa", "");

I have tried finding a similar issue everywhere but cannot find related problems. I have tried simplifying my class as much as possible to isolate the problem and simply establish a connection. I deleted my project and tried to start fresh.

Simplified DatabaseManager class that produces the problem:

public class DatabaseManager {
    static final String JDBC_DRIVER = "org.h2.Driver";
    static final String DB_URL = "jdbc:h2:D:/trant/Documents/Java Practice/Order A Car2/res/userDatabase";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection(DB_URL, "sa", "");
        Statement st = conn.createStatement();
        st.executeUpdate("SELECT * FROM JOBS");
        conn.close();
    }
}

I expect to connect to an H2 database and retrieve data from the table "JOBS". The code is not compiling with the above errors.

edit: If I use version 1.4.199 of H2 rather than 1.4.200, the issue goes away. I found an almost identical problem here: https://github.com/h2database/h2database/issues/2078. This link has an identical stack trace to mine. I have yet to resolve the problem with version 1.4.200

https://github.com/h2database/h2database/issues/2078 you should use the same driver, so reading it with 1.4.200 (current spring data version) is not possible after modification with 1.4.196 (used by IDEA). So mine crash scenario was open db in IDEA with driver 1.4.196 while spring application code used 1.4.200. So it will not start again. You can declare 1.4.196 version in pom.xml for your app but it looks like that you will be bounded to this version and I don't know how you can completely repair your db.

H2 Database: Features and Usage, If there is a problem, you will see an error message in this window. By default, if the database specified in the embedded URL does not yet exist, a new  The embedded database files are stored in your Confluence home directory <confluence-home>/database . The embedded H2 database is only supported while you are evaluating Confluence. You must migrate to a supported external database before using Confluence as a production system. To find out if you are still using the embedded database, go to > General Configuration > Troubleshooting and support tools.

As already noted by @Yura , you need to ensure that all your code base and all your tools use the same version of the driver, be it 1.4.196 or 1.4.200.

Next, if there was nothing valuable in your db, you can safely drop the db file, and it will be re-created again.

If you had some valuable data and you have no backup, then getting the db repaired may become a quest, not necessarily successful...

And if you have a backup, then a procedure of "backup-to-sql-using-196" and "restore-from-sql-using-200" is most likely to do the job for you, see http://www.h2database.com/html/tutorial.html#upgrade_backup_restore ...

H2 Database - Introduction, Clustering can only be used in the server mode (the embedded mode does not support Another problem is that important features are not standardized. Remember an in-memory database is created/initialized when an application starts up; and destroyed when the application shuts down. 1. What is H2 Database? H2 is one of the popular in-memory databases written in Java. It can be embedded in Java applications or run in the client-server mode.

You really should provide a complete stack trace and not just the error message in such questions.

And message that you see is not a compilation error.

Usually such message means that your database is corrupted. If you don't need any data from it, you can simply delete all its files and re-create it from the scratch.

If you can open the database from some tool, but cannot open it from your application, check the versions of H2 that are used in both places and align them. You can export the database in version that works to an SQL script with SCRIPT TO 'filename.sql' command and use that script to populate the data into new database to make sure that it isn't damaged (with the version that you prefer).

Tutorial, In embedded mode, an application opens a database from within the same JVM of other connections will be rolled back (this may not be a problem if you don't  H2 database has an embedded GUI console for browsing the contents of a database and running SQL queries. By default, the H2 console is not enabled in Spring. So to enable it, we need to add the following property to application.properties: spring.h2.console.enabled=true

Advanced, This is not a problem within regions that use the same rules (such as, within USA, is automatically created if it does not yet exist when embedded URL is used. Using H2 in Microsoft .NET. The database can be used from Microsoft .NET even without using Java, by using IKVM.NET. You can access a H2 database on .NET using the JDBC API, or using the ADO.NET interface. Using the ADO.NET API on .NET. An implementation of the ADO.NET interface is available in the open source project H2Sharp. Using the JDBC

Features, The server mode is slower than the embedded mode, because all data is will be rolled back (this may not be a problem if you don't disable autocommit). H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk.

Frequently Asked Questions, H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. H2 Database Engine. Welcome to H2, the free SQL database. The main feature of H2 are: It is free to use for everybody, source code is included Written in Java, but also available as native executable JDBC and (partial) ODBC API Embedded and client/server modes Clustering is supported A web client is included No Javascript

Comments
  • This solved my problem. We use h2 for testing and without data so simple deleting the *.mv.db file in /target/h2/ folder resolved the exception and recreated the db correctly next time the test ran
  • What exactly do you mean by stack trace? Also, how could the database possibly be corrupted? I literally just started a new project, added the Driver to dependencies under project structure, and wrote some code.
  • A stack trace literally, you can google that term by yourself. Take a look on the Run tab or whatever you have in your IDE, you should see Exception …JdbcSQLNonTransientException… and some lines with at org.h2… and so on. I think that work with a database is not the best first choice for a programming education. I assume that you already created a database somehow, if you used a different version of H2 for that it may be an incompatibility between versions, but you can also get a corruption in different ways.
  • I was more asking specifically which part of the stack trace. The whole thing? This is not my first time learning programming, thanks. I think that the error is occurring because of some relationship between the IntelliJ database tool and the embedded code that is connecting with the database. When I delete the database, my DatabaseManager class will run successfully, which is along the lines of the corruption you mentioned.
  • If the tool was able to open the database after your application was unable to open it, I suggest you again to check versions of H2 included in the classpathes of the tool and your application. Old versions of H2 may write and read incorrect pages sometimes.