New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Metabase H2 Corrupt (Row not found when trying to delete from index) #5695

Closed
zaiddabaeen opened this Issue Aug 7, 2017 · 6 comments

Comments

Projects
None yet
3 participants
@zaiddabaeen

zaiddabaeen commented Aug 7, 2017

I run Metabase using the jar file.

Today morning, Metabase was down with a 502 Gateway Timeout. I attempted to sudo service metabase stop (which kills metabase process), it hung. I attempted to sudo service metabase start (which runs java -jar metabase.jar), it also hung.

I then issued a java -jar metabase.jar

myuser@myIP:~$ java -jar metabase.jar 
08-07 09:12:39 INFO metabase.util :: Loading Metabase...
08-07 09:12:45 INFO util.encryption :: DB details encryption is DISABLED for this Metabase instance. 🔓
08-07 09:12:51 INFO metabase.core :: Starting Metabase in STANDALONE mode
08-07 09:12:51 INFO metabase.core :: Launching Embedded Jetty Webserver with config:
 {:port 3000}

08-07 09:12:51 INFO metabase.core :: Starting Metabase version v0.25.0 (b1316d5 release-0.25.0) ...
08-07 09:12:51 INFO metabase.core :: System timezone is 'Etc/UTC' ...
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :bigquery 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :crate 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :druid 🚚
08-07 09:12:51 WARN metabase.driver :: No -init-driver function found for 'metabase.driver.google'
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :googleanalytics 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :h2 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :mongo 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :mysql 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :postgres 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :presto 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :redshift 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :sqlite 🚚
08-07 09:12:51 DEBUG metabase.driver :: Registered driver :sqlserver 🚚
08-07 09:12:51 INFO metabase.core :: Setting up and migrating Metabase DB. Please sit tight, this may take a minute...
08-07 09:12:51 INFO metabase.db :: Verifying h2 Database Connection ...
08-07 09:12:52 ERROR metabase.driver :: Failed to connect to database: org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index """"".I37: ( /* key:7864 */ X'5256470012572027c82fc5d2bfb855264ab45XXXXXXXXXXccad281d2fe4', 165)" [90112-194]
java.lang.AssertionError: Assert failed: Unable to connect to Metabase h2 DB.
(binding [*allow-potentailly-unsafe-connections* true] (require (quote metabase.driver)) ((resolve (quote metabase.driver/can-connect-with-details?)) engine details))
	at metabase.db$verify_db_connection.invokeStatic(db.clj:334)
	at metabase.db$verify_db_connection.invoke(db.clj:327)
	at metabase.db$verify_db_connection.invokeStatic(db.clj:330)
	at metabase.db$verify_db_connection.invoke(db.clj:327)
	at metabase.db$setup_db_BANG_.invokeStatic(db.clj:382)
	at metabase.db$setup_db_BANG_.doInvoke(db.clj:376)
	at clojure.lang.RestFn.invoke(RestFn.java:421)
	at metabase.core$init_BANG_.invokeStatic(core.clj:103)
	at metabase.core$init_BANG_.invoke(core.clj:82)
	at metabase.core$start_normally.invokeStatic(core.clj:187)
	at metabase.core$start_normally.invoke(core.clj:181)
	at metabase.core$_main.invokeStatic(core.clj:279)
	at metabase.core$_main.doInvoke(core.clj:274)
	at clojure.lang.RestFn.invoke(RestFn.java:397)
	at clojure.lang.AFn.applyToHelper(AFn.java:152)
	at clojure.lang.RestFn.applyTo(RestFn.java:132)
	at metabase.core.main(Unknown Source)
08-07 09:12:52 ERROR metabase.core :: Metabase Initialization FAILED:  Assert failed: Unable to connect to Metabase h2 DB.
(binding [*allow-potentailly-unsafe-connections* true] (require (quote metabase.driver)) ((resolve (quote metabase.driver/can-connect-with-details?)) engine details))
08-07 09:12:52 INFO metabase.core :: Metabase Shutting Down ...
08-07 09:12:52 INFO metabase.core :: Metabase Shutdown COMPLETE

Upgrading to v0.25.1 made no difference. The backup file is 7 months old.

I tried java -cp metabase.jar org.h2.tools.Recover, but that didn't fix the issue.
I tried to migrate to a MySQL database, but the error still shows and it prevents the migration.

Are there any tricks to bring it back? And what has happened over the weekend that it just broke?

  • OS: Ubuntu 16.04 LTS
  • Metabase: Issue started at 0.25.0.
  • Internal DB: H2
  • External DB: MySQL
  • Environment: Jar file on EC2

@zaiddabaeen zaiddabaeen changed the title from Metabase H2 Corrupt to Metabase H2 Corrupt (Row not found when trying to delete from index) Aug 7, 2017

@zaiddabaeen

This comment has been minimized.

zaiddabaeen commented Aug 7, 2017

I cannot run the org.h2.tools.RunScript as the index error will show anyway.

myUser@myIp:~$ java -cp metabase.jar org.h2.tools.RunScript -script whatever.sql -url jdbc:h2:~/metabase.db
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index """"".I37: ( /* key:7864 */ X'5256470012572027c82fc5d2bfb855264ab45f8fec4cf48b0620ccad281d2fe4', 165)" [90112-194]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:233)
	at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:336)
	at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:245)
	at org.h2.table.RegularTable.removeRow(RegularTable.java:391)
	at org.h2.store.PageStore.redo(PageStore.java:1581)
	at org.h2.store.PageStore.redoDelete(PageStore.java:1554)
	at org.h2.store.PageLog.recover(PageLog.java:346)
	at org.h2.store.PageStore.recover(PageStore.java:1407)
	at org.h2.store.PageStore.openExisting(PageStore.java:368)
	at org.h2.store.PageStore.open(PageStore.java:289)
	at org.h2.engine.Database.getPageStore(Database.java:2488)
	at org.h2.engine.Database.open(Database.java:697)
	at org.h2.engine.Database.openDatabase(Database.java:276)
	at org.h2.engine.Database.<init>(Database.java:270)
	at org.h2.engine.Engine.openSession(Engine.java:64)
	at org.h2.engine.Engine.openSession(Engine.java:176)
	at org.h2.engine.Engine.createSessionAndValidate(Engine.java:154)
	at org.h2.engine.Engine.createSession(Engine.java:137)
	at org.h2.engine.Engine.createSession(Engine.java:27)
	at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:354)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:116)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:100)
	at org.h2.Driver.connect(Driver.java:69)
	at java.sql.DriverManager.getConnection(DriverManager.java:571)
	at java.sql.DriverManager.getConnection(DriverManager.java:215)
	at org.h2.tools.RunScript.process(RunScript.java:324)
	at org.h2.tools.RunScript.runTool(RunScript.java:142)
	at org.h2.tools.RunScript.main(RunScript.java:69)

@salsakran

This comment has been minimized.

Contributor

salsakran commented Aug 7, 2017

Is the underlying storage of the instance EBS?

@zaiddabaeen

This comment has been minimized.

zaiddabaeen commented Aug 8, 2017

No, it is not EBS-Optimized

@zaiddabaeen

This comment has been minimized.

zaiddabaeen commented Aug 8, 2017

Well I got that sorted out from tips from here #3560

Basically:

java -cp metabase.jar org.h2.tools.Recover
mv metabase.db.h2.db metabase.old.db
touch metabase.db.h2.db
java -cp metabase.jar org.h2.tools.RunScript -script metabase.db.h2.sql -url jdbc:h2:/path/to/metabase.db;MV_STORE=FALSE

And you'd have a new metabase database with the old contents working.

Note: Make sure metabase.db.mv.db does not exist.

@zaiddabaeen

This comment has been minimized.

zaiddabaeen commented Aug 8, 2017

I'd recommend that anyone who got to this point would immediately move to a production grade database.

I'd also recommend that you'd show in Metabase Admin Panel a red warning that the internal database used is H2 and it is not for production grade.

@beanHead

This comment has been minimized.

beanHead commented Oct 31, 2018

Our database got corrupted and while trying to restart metabase we got following error

Exception in thread “main” org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from index “”"""

Followed above instructions but getting column mismatch error

INSERT INTO O_149 VALUES(‘5170510’, ‘0’, ‘0’, ‘1225505296’, ‘254’) [21002-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:178)
at org.h2.message.DbException.get(DbException.java:154)
at org.h2.message.DbException.get(DbException.java:143)
at org.h2.command.dml.Insert.prepare(Insert.java:266)
at org.h2.command.Parser.prepareCommand(Parser.java:248)
at org.h2.engine.Session.prepareLocal(Session.java:442)
at org.h2.engine.Session.prepareCommand(Session.java:384)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)
at org.h2.tools.RunScript.process(RunScript.java:261)
at org.h2.tools.RunScript.process(RunScript.java:191)
at org.h2.tools.RunScript.process(RunScript.java:329)
at org.h2.tools.RunScript.runTool(RunScript.java:142)
at org.h2.tools.RunScript.main(RunScript.java:69)

when I opened sql file, I found table having some records with five columns and others with 3…

If somebody can help us on this, for now we went back to another backup and got it running, but its quite old.
@zaiddabaeen

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment