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

Machinist

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.

Liverwort

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

Lieberman

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 – Engineman

I don't know why, but dropping the DB file solved the issue for me. – Filmore

Answer 3

First of all you have to keep a copy of your database somewhere safe.

Copy a fresh copy to your project to avoid any corruption, make sure you don't try to open it using inteliJ.

Go to datasources and drivers ->Drivers -> H2 ->Driver files, change it to 1.4.196. Create a new datasource of type h2 and fill in the fields

user: "sa"

password:"" (empty)

url is in this form (path to your database file)

jdbc:h2:file:D:\Downloads\loans\loans\Database

then click on test connection and it should show a green message. Apply and close.

Right click on your database in the database view in the editor, then "Open query console". Run this line (link where you want to dump your database)

SCRIPT TO 'D:\Downloads\loans\loans\db-dump.sql'

Create a new database file (Test.mv.database), follow the previous steps and change the driver version back to 1.4.200. Create a new datasource with the same steps this time linking to your new database

 jdbc:h2:file:D:\Downloads\loans\loans\Test

Test the connection, Apply and close.

Open the query console and copy paste the script from db-dump.sql and run it. Right click on the database in the database view and click "Refresh". You should see the data. Right click again and click "Disconnect".

Make sure your application.properties file is pointing to the correct database(Test) and run your application.

Anstus

Answer 4

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

Schuman

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

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

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

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

Answer 5

I've reproduced the same issue:

java.lang.IllegalStateException: Unable to read the page at position

while connecting to H2 db using Data Source via connection URL:

jdbc:h2:file:./data/testdb;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

using dependency:

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.197</version>
    </dependency>

After some investigations the way how to solve the problem was found.

Solution:

1. So, firstly, I changed version of dependency in POM to:

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.200</version>
    </dependency>
   

2. Secondly, fully recreated Data Source configuration with the same connection URL:

jdbc:h2:file:./data/testdb;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

with removing Data Base files for old driver (it'll be regenerated after new connection):

3. After recreation configuration in Data Source using new driver, checking connection and as result it works for me:

Creepy

Answer 6

If you don't want to alter pom or change h2 version then try deleting the file:

1. userDatabase.mv.db
2. userDatabase.trace.db

You may find these files inside dir "D:/trant/Documents/Java Practice/Order A Car2/res/".
But before delete ensure that there is no active connection to the db from anywhere else. If not sure then delete just after restarting the machine.

After all done, try running the code again. Those files will be re-generated by pom

Impede