Metabase h2 fails to load & migration to mysql fails

Get Help
1

Dear all,

I'm pretty lost here, hoping to get some advice quickly. Thanks a lot in advance!

Situation:

  • we had Metabase running on h2 for a couple of years
  • yesterday had to restart, since then it's not coming up anymore.
  • metabase.db.h2.db file is roughly 400 MB in size
  • still running on version 0.33.3 (upgrades to newer releases always failed with various errors).
  • launching metabase using

#!/bin/bash
export MB_DB_TYPE=h2
export MB_JETTY_PORT=xxx
export MB_DB_FILE=/home/ubuntu/metabase/metabase.db
java -jar metabase.jar >> metabase.log 2>&1 &

  • Whenever I try to start I get
02-24 22:24:21 INFO metabase.db :: Verifying h2 Database Connection ...
02-24 22:24:21 INFO metabase.driver :: Initializing driver :sql...
02-24 22:24:21 INFO metabase.driver :: Initializing driver :sql-jdbc...
02-24 22:24:21 INFO metabase.driver :: Initializing driver :h2...
02-24 22:24:26 ERROR driver.util :: Database connection error
java.util.concurrent.TimeoutException: Timed out after 5,000 milliseconds.
  • I have also tried to copy metabase.db.h2.db to a different filename, e.g. metabase_new.db.h2.db
    then launching it with

#!/bin/bash
export MB_DB_TYPE=h2
export MB_JETTY_PORT=xxx
export MB_DB_FILE=/home/ubuntu/metabase/metabase_new.db
java -jar metabase.jar >> metabase.log 2>&1 &

  • also doesn't work.

I have then attempted to FINALLY do the move to mysql:

#!/bin/bash
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=xxx
export MB_DB_USER=xxx_metabase
export MB_DB_PASS=xxx
export MB_DB_HOST=xxx.rds.amazonaws.com
export MB_JETTY_PORT=xxx
export MB_DB_FILE=/home/ubuntu/metabase/metabase_new.db
java -jar metabase.jar load-from-h2 /home/ubuntu/metabase/metabase_new.db

  • BUT (even after upgrading from MySQL 5.6 to 5.7.26) I keep failing at

java.sql.SQLException: Data too long for column ‘result_metadata’

Now:

  • following About save question condition - #4 by flamber I have attempted to change the result_metadata column type to MEDIUMTEXT
  • Yet when attempting to do the migration, I start off with an empty mysql database,
  • then run the load-from-h2 statement, it nicely creates the DB until it crashes at Data too long
  • I then modify the column type and try to re-run the load-from-h2 hoping to complete the migration
  • It fails, however complaining about primary key conflicts and so forth.
  • => I can't seem to find a way to inject the ALTER TABLE statement between table creation and data transfer from h2.

Another attempted approach:

  • I have also tried to switch off auto-migrations using

MB_DB_AUTOMIGRATE=false

  • but the SQL that load-from-h2 spits out doesn't seem to be complete at all.

=> What else can I try ?

Thank you very much for any advice!

Best Regards
Fabian

2

HEUREKA!!!

I decided to simply pound the following statement over and over against my database while the load-from-h2 script was running:

ALTER TABLE metabase.report_card
CHANGE COLUMN result_metadata result_metadata MEDIUMTEXT NULL DEFAULT NULL COMMENT 'Serialized JSON containing metadata about the result columns from running the query.' ;

It managed to get through before the scripts started transferring the actual table contents, so the migration succeeded.

=> I would now like to suggest that result_metadata be always set to MEDIUMTEXT by the tool itself.

Thank you & Best Regards
Fabian

1 Like
3

Hi @SetSails
Great you found a solution! Yes, I think I forgot to file a bug last time, since there were so many different problems.
I’ve now created one: https://github.com/metabase/metabase/issues/11977 - upvote by clicking :+1: on the first post

1 Like