I am evaluating Metabase and created an instance on Windows Server 2022 using the default H2 database. It is running as a windows service created using WinSW.
I attempted to change the service user and since then have been unable to launch successfully - displays a chart animation and never gets back to the login screen.
Interestingly, the error in the log is …
… Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "C:/Apps/Metabase/metabase.db.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-214] …
There are no java processes running and I have rebooted and nothing has helped
If there is no Metabase instance running, then what happened was that a previous instance crashed or exited unexpectedly and H2 wasn’t able to unlock the data file. You will need to run a recover & reload to replace the broken file with a new one.
The short version is something like this (not tested, especially the last command, adapt as needed):
It is critically important to always shut down Metabase cleanly when using the H2 app database. (Metabase’s version of) H2 is quite fragile and does not tolerate system crashes/bluescreens/abrupt program exits. If you are running Metabase in a container, disable health checks so the container monitor doesn’t kill it unnecessarily.
Instructions for UNIX shells is here for reference.
Unfortunately didn’t get very far (or it would appear so)
"C:\Program Files\Eclipse Adoptium\jdk-25.0.2.10-hotspot\bin\java.exe" --add-opens java.base/java.nio=ALL-UNNAMED -cp metabase.jar org.h2.tools.Recover -dir . -db metabase.db
(metabase.db.h2.sql created)
C:\Apps\Metabase>rename metabase.db.mv.db metabase-old.db.mv.db
C:\Apps\Metabase>"C:\Program Files\Eclipse Adoptium\jdk-25.0.2.10-hotspot\bin\java.exe" --add-opens java.base/java.nio=ALL-UNNAMED -cp metabase.jar org.h2.tools.RunScript -script metabase.db.h2.sql -url "jdbc:h2:./metabase.db"
Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Generated column "PUBLIC.METABASE_FIELD.UNIQUE_FIELD_HELPER" cannot be assigned; SQL statement:
INSERT INTO "PUBLIC"."METABASE_FIELD" SELECT * FROM O_5 [90154-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:632)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.table.Table.convertInsertRow(Table.java:920)
at org.h2.command.dml.Insert.addRow(Insert.java:229)
at org.h2.command.dml.Insert.insertRows(Insert.java:203)
at org.h2.command.dml.Insert.update(Insert.java:135)
at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74)
at org.h2.command.CommandContainer.update(CommandContainer.java:169)
at org.h2.command.Command.executeUpdate(Command.java:252)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.h2.tools.RunScript.process(RunScript.java:255)
at org.h2.tools.RunScript.process(RunScript.java:186)
at org.h2.tools.RunScript.process(RunScript.java:313)
at org.h2.tools.RunScript.runTool(RunScript.java:139)
at org.h2.tools.RunScript.main(RunScript.java:66)
Before we get to addressing the recovery issues, two very important points.
While H2 is fine for demos, stop using it for the app database at the earliest possible opportunity.
Metabase only supports Java 21. You will experience strange issues running it on Java 25 that aren’t supported.
Looks like there are issues with the recovery process. The dump needs to be edited to fix the problems. This is easier to do if you are comfortable with reading/writing DDL in your SQL database of choice.
If you are not comfortable with editing SQL, and there is no data in the app data of value, then just delete the metabase.db.mv.db file and start Metabase. It will generate a fresh app database.
I haven’t done both of these to completion and successfully imported the database yet, but these are the issues I ran into.
The restore process inserts data into a temporary table before inserting it into the final table. You will need to match the temp table with the real one. Look for the INSERT INTO "PUBLIC"."TABLENAME" SELECT * FROM O_XXXlines near the bottom to find the mapping.
Problem 1: The version of Recover in Metabase dumps the contents of generated columns, which won’t restore because H2 won’t allow you to insert data into a generated column.
To solve this, the INSERT statement that copies the data from the temporary table to the final table needs to be edited to list all the source and destination columns instead of using *, skipping the generated column. This affects the columns metabase_field.unique_field_helper and metabase_table.unique_table_helper. This is tedious work as they are large tables. You should end up with a command that looks like INSERT INTO “PUBLIC”.”METABASE_FIELD” (ID,CREATED_AT,...) SELECT C1,C2,C3,... FROM O_xxx. The order of columns between the temp table and the real one are the same.
Problem 2: The restore process copies data into a temporary table that uses all VARCHAR columns, but qrtz_triggers.job_data is a BINARY LARGE OBJECT and isn’t castable from VARCHAR.
The data type of the corresponding column in the temporary table needs to be changed to VARBINARY to allow it to be inserted into the final table. (It should be the last one, C15).
Thanks for the steer @dwhitemv. I’m still only at demo stage but have some queries and dashboards that I have included that showcase the capabilities. I was actually trying out changing the service user, to be followed by a backup before trying out transfer to Postgres, but clearly did not check well enough to understand the state of the processes at the time of stopping the service, which must have led to the lock.
I still need to have a go at restoring what I had, so thanks, I will give your steer above a whirl … starting with using Java 21 instead …
I’m making some progress … Managed to restore and launch Metabase. Next thing appears I have to resolve some table references (which puts some cards in view only mode) and a message similar to that below is then displayed in the UI and console on accessing the question
Failed to fetch :metadata/table 1898: either it does not exist, or it belongs to a different Database
… rebuilding MBQL-centric questions from the json metadata in the h2 generated sql file. If anyone has any better/fast-track suggestions, keen to hear. TIA.
So far, merely ensuring that table and field ids (in the json structure for the questions) are aligned with those in the active Metabase db seems to be doing the trick …