Update from 0.37 to .40.5 corrupted database

I run Metabase as a JAR (java -jar metabase.jar) and when I started the new version (0.40.5) it hung on Verifying h2 Database Connection... for over an hour. I canceled out (Ctrl+c) and tried it again. Same thing. I tried running the older version and it gets to Initializing driver :h2 then errors as Timed out after 5.0s and shuts down.

I tried running java -cp metabase.jar org.h2.tools.Recover and it a large SQL file but fails at some point with an error of Unsupported type 17.

I really hope this database is recoverable. I suppose I put off migrating to Postgres for too long. Any help is truly appreciated.

Hi, I don't know where you're running the server but you can increase the timeout with the env var https://www.metabase.com/docs/latest/operations-guide/environment-variables.html#mb_db_connection_timeout_ms

might be an issue about the disk not being able to read the entire file before 5 secs

Thanks for the reply!

I set the MB_DB_CONNECTION_TIMEOUT_MS to 20000 and it's doing the same thing.

ok, can you revert to the previous version?

I tried to launch the older version, but that's when it times out.

If I try to launch the new version, it just stalls at Verifying h2 Database Connection... forever.

can you run ls -l --block-size=M to see how big is the database file?

The database is 43Mb

how much RAM do you have in the server? and how much free RAM for running the migration process? I'm also assuming you stop the Metabase server for doing the migration right?

The server has 16gb and it has 12.5gb free. I don't believe I stopped it from doing the migration.

I tried updating to 0.41 and now I'm running into General error: "java.lang.ClassCastException: class org.h2.value.ValueArray cannot be cast to class org.h2.value.ValueLong (org.h2.value.ValueArray and org.h2.value.ValueLong are in unnamed module of loader 'app')" [50000-197]

I wish I could pull some of the reports I created from this database.

You could try connecting to the H2 database with a client and see if there is anything corrupted at plain sight, unfortunately we can't help more than that :frowning:

I can connect to the DB with RazorSQL. Is there a way of rolling back migrations that 0.40.5 did or tables that I can export and drop into a new DB?

@a9y So you have absolutely no backups of the H2 file? If no, well, lesson learned for the future.
Make sure you have backups now.

Sounds like the H2 has become corrupted.

There's no way of rolling back the migrations. You would have to do that manually, which would be overly complicated, so let's continue on.

Try loading the H2 into whatever tool you prefer (I like DBeaver.io)
It is very important that you use the same H2 driver version as Metabase (1.4.197) otherwise you'll have to go through a lot of other things. At that point, then it's probably easier for you to manually recreate everything in a new Metabase setup.

You can try exporting everything to SQL, so you can see if there's any corruptions, then you delete everything in the H2 and then import everything again.
Run each line by itself, so you validate what's going on - and of course not continue if something errors.

SCRIPT TO 'h2export.sql';
DROP ALL OBJECTS;
RUNSCRIPT FROM 'h2export.sql';

There's no 1-click-fix. If you want to try to recover, it will take a lot of work by you. It's not possible to give you exact steps, since corruptions are almost always unique.

I've got a new error now! The database is read-only and locked by the server. How do you clear the lock?

Correction. The database is read-only and the server throws an error when trying to lock the DB. I've tried googling how to change the DB to read-write, but no luck.

@a9y You need to shutdown processes that are using H2. H2 is a single-process file database.
Check lsof to see which processes might be locking the file.
If you are in the H2 command interface, then you can shut it down with SHUTDOWN COMPACT;

I couldn't find a process that was running. I rebooted the server and it still said it was read only. Somehow my permissions for that folder were reset and I didn't have write access anymore. Changed the permissions and it worked!

I migrated the data into MariaDB. Thank you for all the help!!

@a9y Excellent. And please restart your server again and then validate that it's actually using the MariaDB for the application database by looking at "Diagnostic Info" in Admin > Troubleshooting.
Have seen too many who did the correct setup, but forgot that environment variables are not saved across reboots and then they were continuing to use the H2 by accident.

Also remove the H2 - just have a backup of it somewhere.

I've restarted and the environment variables persisted. All the data is in MariaDB.

I'll move the H2 DB file to a backup directory. Thanks again for all the help!