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

Question

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

Answer 1

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.

Answer 2

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 ...