Error migrating from H2 to MySQL


#1

Hi
I have been trying to migrate from the H2 database to my MySQL database however i get the following error when trying to do so:
Command failed with exception: No matching clause: :h2
Below is the complete log for your reference:

java -jar metabase.jar load-from-h2 /home/metabase/metabase.db
06-07 10:26:13 INFO metabase.util :: Loading Metabase...
06-07 10:26:26 INFO util.encryption :: DB details encryption is DISABLED for this Metabase instance. 🔓
06-07 10:26:41 INFO metabase.db :: Verifying h2 Database Connection ...
06-07 10:26:41 DEBUG metabase.driver :: Registered driver :h2 🚚
06-07 10:26:42 INFO metabase.db :: Verify Database Connection ...  ✅
06-07 10:26:42 INFO metabase.db :: Running Database Migrations...
06-07 10:26:42 INFO metabase.db :: Setting up Liquibase...
06-07 10:26:42 INFO metabase.db :: Liquibase is ready.
06-07 10:26:42 INFO metabase.db :: Checking if Database has unrun migrations...
06-07 10:26:49 INFO metabase.db :: Database has unrun migrations. Waiting for migration lock to be cleared...
06-07 10:26:49 INFO metabase.db :: Migration lock is cleared. Running migrations...
06-07 10:28:19 INFO metabase.db :: Database Migrations Current ...  ✅
com.mchange.v2.cfg.DelayedLogItem [ level -> FINE, text -> "The configuration file for resource identifier 'hocon:/reference,/application,/c3p0,/' could not be found. Skipping.", exception -> null]
Temporarily disabling DB constraints...
java.lang.IllegalArgumentException: No matching clause: :h2
        at metabase.cmd.load_from_h2$disable_db_constraints_BANG_.invokeStatic(load_from_h2.clj:170)
        at metabase.cmd.load_from_h2$disable_db_constraints_BANG_.invoke(load_from_h2.clj:168)
        at metabase.cmd.load_from_h2$load_from_h2_BANG_$fn__39488.invoke(load_from_h2.clj:214)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:613)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:629)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:598)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:212)
        at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:205)
        at clojure.lang.Var.invoke(Var.java:379)
        at metabase.core$load_from_h2.invokeStatic(core.clj:210)
        at metabase.core$load_from_h2.invoke(core.clj:203)
        at clojure.lang.AFn.applyToHelper(AFn.java:154)
        at clojure.lang.AFn.applyTo(AFn.java:144)
        at clojure.core$apply.invokeStatic(core.clj:646)
        at clojure.core$apply.invoke(core.clj:641)
        at metabase.core$run_cmd$fn__40258.invoke(core.clj:264)
        at metabase.core$run_cmd.invokeStatic(core.clj:264)
        at metabase.core$run_cmd.doInvoke(core.clj:263)
        at clojure.lang.RestFn.applyTo(RestFn.java:139)
        at clojure.core$apply.invokeStatic(core.clj:648)
        at clojure.core$apply.invoke(core.clj:641)
        at metabase.core$_main.invokeStatic(core.clj:278)
        at metabase.core$_main.doInvoke(core.clj:274)
        at clojure.lang.RestFn.applyTo(RestFn.java:137)
        at metabase.core.main(Unknown Source)
Command failed with exception: No matching clause: :h2

I have rolled back metabase.jar all the way to version 0.20.0 to see if it was an problem that didn’t exist with an older version, but the same error occurred

Any ideas as to what is causing this?


#2

same problem here. anyone?


#3

I have the same problem migrating to postgres. Is this a bug or did anyone find a solution?

java -jar metabase.jar load-from-h2 backups/metabase.db
04-06 06:08:29 INFO metabase.util :: Loading Metabase...
04-06 06:08:33 INFO util.encryption :: DB details encryption is DISABLED for this Metabase instance. 🔓 
See http://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest for more information.
04-06 06:08:39 INFO metabase.db :: Verifying h2 Database Connection ...
04-06 06:08:40 INFO metabase.db :: Verify Database Connection ...  ✅
04-06 06:08:40 INFO metabase.db :: Running Database Migrations...
04-06 06:08:40 INFO metabase.db :: Setting up Liquibase...
04-06 06:08:40 INFO metabase.db :: Liquibase is ready.
04-06 06:08:40 INFO metabase.db :: Checking if Database has unrun migrations...
04-06 06:08:42 INFO metabase.db :: Database Migrations Current ...  ✅
com.mchange.v2.cfg.DelayedLogItem [ level -> FINE, text -> "The configuration file for resource identifier 'hocon:/reference,/application,/c3p0,/' could not be found. Skipping.", exception -> null]
Temporarily disabling DB constraints...
java.lang.IllegalArgumentException: No matching clause: :h2
	at metabase.cmd.load_from_h2$disable_db_constraints_BANG_.invokeStatic(load_from_h2.clj:179)
	at metabase.cmd.load_from_h2$disable_db_constraints_BANG_.invoke(load_from_h2.clj:177)
	at metabase.cmd.load_from_h2$load_from_h2_BANG_$fn__33254.invoke(load_from_h2.clj:224)
	at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:741)
	at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
	at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:776)
	at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
	at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:724)
	at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
	at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:222)
	at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:215)
	at clojure.lang.Var.invoke(Var.java:379)
	at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:36)
	at metabase.cmd$load_from_h2.invoke(cmd.clj:29)
	at clojure.lang.AFn.applyToHelper(AFn.java:154)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:646)
	at clojure.core$apply.invoke(core.clj:641)
	at metabase.cmd$run_cmd$fn__37709.invoke(cmd.clj:126)
	at metabase.cmd$run_cmd.invokeStatic(cmd.clj:126)
	at metabase.cmd$run_cmd.invoke(cmd.clj:122)
	at clojure.lang.Var.invoke(Var.java:383)
	at metabase.core$run_cmd.invokeStatic(core.clj:269)
	at metabase.core$run_cmd.invoke(core.clj:267)
	at metabase.core$_main.invokeStatic(core.clj:278)
	at metabase.core$_main.doInvoke(core.clj:274)
	at clojure.lang.RestFn.applyTo(RestFn.java:137)
	at metabase.core.main(Unknown Source)
Command failed with exception: No matching clause: :h2

#4

i managed to migrate to mysql on newer version of metabase since my report


#5

I’m getting the same error with the latest version of Metabase. Did it just work for you after the upgrade?

EDIT:

I was able to get past the h2 clause error by properly setting the db variables in the command. java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=meta -DMB_DB_PASS=password metabase.jar load-from-h2 ./metabase.db

Now the error i’m getting is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘admin BIT(1) NOT NULL, user_id INT NOT NULL, organization_id INT NOT NULL, CONST’ at line 1

A few tables are created in sql but there’s no data in them.

image


#6

@erix920 does a manual migration run as suggested here help: https://metabase.com/docs/latest/operations-guide/start.html#running-metabase-database-migrations-manually


#7

Thanks, I was able to get this running after fixing the generated script. Looks like some of the databases have column names that are reserved in mysql. I think they were row and admin, they were marked as errors when pasted in mysql workbench. I had to add the ` character around each of them.

Also these three queries didn’t get qualified with the tablename. For my case needed to add metabase. in front of the table names.

update report_dashboard set public_perms = 2 where public_perms = 1;
update metabase_database set is_sample = true where name = ‘Sample Dataset’;
update metabase_database set is_full_sync = true;

If anyone wants it, I ran this command to get that SQL script to generate: java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=meta -DMB_DB_PASS=password -DMB_DB_AUTOMIGRATE=false metabase.jar


#8

Excellent find - and thanks for sharing back here. Workaround is pretty consistent with this error from MySQL: :wink:

I’m wondering if it’s a restriction that got tightened in MySQL in more recent versions? Edit: Naah, more likely that it’s due to Metabase schema additions over time. (A unit test would be good)

I think you nailed this down to a pretty clear issue. Care to report it as a proper GitHub issue as well?

Would also be good to hear from others wanting to migrate to MySQL if they can reproduce and with what MySQL version.


#9

I wanted to migrate Metabase (0.29.3) to the MariaDB (10.2.10) and I had a similar error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 
server version for the right syntax to use near 
'rows INT NULL, description TEXT NULL, entity_name VARCHAR(254) NULL, entity_type' at line 1

so in metabase.metabase_table, rows column should be surrounded by the ` character.

I added this to the issue:

— Robert