Migrate DB from H2 to MySql

Hi to everybody
I can’t well understand the issue in H2 - MySql … I have read



but still have some doubt. I try to ask u so maybe u can clear me what I should do. I will enumerate questions so it’s easier

  • Metabase is hosted in a Centos 7 Server with Cpanel for this installation
  1. I have the files in /home/bi/ exactly I have:
    metabase.jar (117MB) last mod Dicember 2018
    h2.db.mv.db (82.98 MB) last modification today
    h2.db.trace.db (187 byte) last mod February 2019
    I don’t know why those files are not in /home/bi/public_html/ (really I don’t remember how I did in 2018) but actually if u go to our bi.site.com its’ working well …
    I did copy of that files in a subdirectory, is it correct? (in case something goes wrong)

  2. How can I have all in public_html? (I do all this operation beacuse I want upgrate metabase) and make that bi.site.com works well again?

  3. before upgrading, I read a suggestion to migrate from h2 default to MySql … I am not sure this is my case, but seems yes (because of the h2.db.mv.db files), Correct?

  4. So I would like to migrate it. I have found this in the previous web site

  5. Run the Metabase data migration command using the appropriate environment variables for the target database you want to migrate to.

**where should I set the follow things? **
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=
export MB_DB_PASS=
export MB_DB_HOST=localhost

  1. **after that should I only restart metabase with this command as suggested and all should be ok? **
    java -jar metabase.jar

  2. How can I check if all was ok? and what if not? how can I roll back?

Sorry for all the questions, but I have all the company connected to the BI …and I need to be carefull :slight_smile:
Thanks for ur help
M

Hi @mirco_cervi

It’s really difficult to help you, when you don’t know how you’ve setup Metabase.

So to begin with, make sure that you have backups of h2.db.mv.db, since that’s where all the Metabase data is stored.

  1. No, you should absolutely not have the h2.db.mv.db file in a directory that is publicly accessible from your webserver.
  2. Same as 1.
  3. Like the migration guide says, it is not recommended to use H2 in production, so you should migrate to MySQL or Postgres. It also notes that you should not try to migrate and upgrade at the same time.
  4. Is there a question?
  5. Since I don’t know how you’re currently running Metabase, I cannot tell you what to do, but the best thing would be to setup Metabase as a service, then migrate, and then upgrade.
    https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-debian.html
  6. Make sure you have backups of your entire server - I would guess that’s an option in cPanel - so you can roll back in case it doesn’t work.

I would recommend that you hire a server admin that will do everything for you, but on a new server, so everything is clean and up-to-date.

Thanks @flamber got it (unfortunately is a VPS server and I have not full access so I am asking the FarmServer to help me on it …but I need to address them …)
I got that:

  • 1, 2 OK perfect
  • 3 correct, first migration from H2 to MySql …and after … in case I will update
  • Metabase run as a service on Centos Server. Got all docs and could check that metabas.service has this info
    Description=Metabase server
    After=syslog.target
    After=network.target
    [Service]
    User=username_replaced
    Type=simple
    ExecStart=/bin/java -jar /home/directory_replaced/metabase.jar
    Restart=always
    StandardOutput=syslog
    StandardError=syslog
    SyslogIdentifier=metabase
    Environment=MB_DB_TYPE=h2
    Environment=MB_DB_FILE=/home/directory_replaced/h2.db
    [Install]
    WantedBy=multi-user.target

so I am sure I have h2 …the point is …that I dont have the Metabase Config file in /etc/default/metabase in my installation… that’s why I have doubts.

If I create the Metabase Config files and Set it with MySql installation, will the “migration” of h2 default storage run automatically when I restart Metabase Service?

Thanks a lot for ur patience!
M

@mirco_cervi I’m a little confused - you say that you don’t have full access, but you show the service configuration file, which should only be available if accessed as root user.

Anyways, great, now we know it’s setup as a SystemD service.

Let me make this perfectly clear before you start: If you end up with a broken system, then that’s on you - and you should have backups, so you can revert.
If you’re unsure about anything, then stop, and go and hire someone who knows about servers.

To migrate:

  • first you would shutdown Metabase:
    systemctl stop metabase
  • then start the migration process manually - replace all the <...> with the correct references to your MySQL server:
cd /home/directory_replaced
java -DMB_DB_FILE=h2.db -DMB_DB_TYPE=mysql -DMB_DB_HOST=<host> -DMB_DB_PORT=<port> -DMB_DB_DBNAME=<db> -DMB_DB_USER=<user> -DMB_DB_PASS=<pass> -jar metabase.jar load-from-h2
  • then edit the service configuration, add the 6 environment variables that you listed in the first post instead of the existing two Environment lines
  • move the h2.db.mv.db to old-h2.db.mv.db, so you are not accidentally still using it
  • start Metabase service, which should now be using MySQL as the application database
    systemctl start metabase

Grate! I have ssh access only read (normal user) but I will forward your notes to admin of Server and ask for help.
Mc

@mirco_cervi You should really consider our hosting solution: Private beta for Hosted Metabase

sure I will
thanx

Hi @flamber , When I was trying to migrate the application database from H2 to My SQL, I came across the below issue. Please help me here.

Metabase version v0.45.3.

2023-09-07 06:46:54,143 ERROR cmd.copy :: BatchUpdateException:
Message: (conn=687) Unknown column 'fields_hash' in 'field list'
SQLState: 42S22
Error Code: 1054

Entre Log:
"C:\Program Files\Java\jdk-11.0.4\bin\java.exe" -DMB_DB_TYPE=mysql -DMB_DB_CONNECTION_URI="jdbc:mysql://ACTUALSERVER:3306/metabase?user=metabase&password=metabase" -jar metabase.jar load-from-h2 metabase.db
Warning: protocol #'java-time.core/Amount is overwriting function abs
WARNING: abs already refers to: #'clojure.core/abs in namespace: java-time.core, being replaced by: #'java-time.core/abs
WARNING: abs already refers to: #'clojure.core/abs in namespace: java-time, being replaced by: #'java-time/abs
2023-09-07 06:45:50,594 INFO metabase.util :: Maximum memory available to JVM: 4.0 GB
2023-09-07 06:45:52,156 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance.
For more information, see Redirecting…
WARNING: abs already refers to: #'clojure.core/abs in namespace: taoensso.encore, being replaced by: #'taoensso.encore/abs
WARNING: abs already refers to: #'clojure.core/abs in namespace: kixi.stats.math, being replaced by: #'kixi.stats.math/abs
WARNING: abs already refers to: #'clojure.core/abs in namespace: kixi.stats.test, being replaced by: #'kixi.stats.math/abs
WARNING: abs already refers to: #'clojure.core/abs in namespace: kixi.stats.distribution, being replaced by: #'kixi.stats.math/abs
2023-09-07 06:46:00,968 INFO driver.impl :: Registered abstract driver :sql
2023-09-07 06:46:00,973 INFO metabase.util :: ? Load driver :sql took 99.5 ms
2023-09-07 06:46:00,979 INFO driver.impl :: Registered abstract driver :sql-jdbc (parents: [:sql])
2023-09-07 06:46:00,983 INFO metabase.util :: Load driver :sql-jdbc took 113.4 ms
2023-09-07 06:46:00,984 INFO driver.impl :: Registered driver :h2 (parents: [:sql-jdbc])
2023-09-07 06:46:01,018 INFO driver.impl :: Registered driver :mysql (parents: [:sql-jdbc])
2023-09-07 06:46:01,048 INFO driver.impl :: Registered driver :postgres (parents: [:sql-jdbc])
2023-09-07 06:46:03,025 INFO metabase.core ::
Metabase v0.45.3 (070f57b release-x.45.x)

Copyright ⌐ 2023 Metabase, Inc.

Metabase Enterprise Edition extensions are NOT PRESENT.
2023-09-07 06:46:03,094 INFO cmd.copy :: Set up h2 source database and run migrations...
2023-09-07 06:46:03,098 INFO db.setup :: Verifying h2 Database Connection ...
2023-09-07 06:46:03,970 INFO db.setup :: Successfully verified H2 1.4.197 (2018-03-18) application database connection.
2023-09-07 06:46:03,972 INFO db.setup :: Running Database Migrations...
2023-09-07 06:46:03,977 INFO db.setup :: Setting up Liquibase...
2023-09-07 06:46:04,742 INFO db.setup :: Liquibase is ready.
2023-09-07 06:46:04,755 INFO db.liquibase :: Checking if Database has unrun migrations...
2023-09-07 06:46:06,200 INFO db.setup :: Database Migrations Current ...
2023-09-07 06:46:06,202 INFO db.data-migrations :: Running all necessary data migrations, this may take a minute.
2023-09-07 06:46:06,214 INFO db.data-migrations :: Finished running data migrations.
2023-09-07 06:46:06,215 INFO metabase.util :: Database setup took 3.1 s
2023-09-07 06:46:06,216 INFO cmd.copy :: [OK]
2023-09-07 06:46:06,217 INFO cmd.copy :: Set up mysql target database and run migrations...
2023-09-07 06:46:06,218 INFO db.setup :: Verifying mysql Database Connection ...
2023-09-07 06:46:06,371 INFO db.setup :: Successfully verified MySQL 8.1.0 application database connection.
2023-09-07 06:46:06,371 INFO db.setup :: Running Database Migrations...
2023-09-07 06:46:06,376 INFO db.setup :: Setting up Liquibase...
2023-09-07 06:46:06,380 INFO db.setup :: Liquibase is ready.
2023-09-07 06:46:06,380 INFO db.liquibase :: Checking if Database has unrun migrations...
2023-09-07 06:46:07,208 INFO db.liquibase :: Database has unrun migrations. Waiting for migration lock to be cleared...
2023-09-07 06:46:07,445 INFO db.liquibase :: Migration lock is cleared. Running migrations...
2023-09-07 06:46:49,656 INFO db.setup :: Database Migrations Current ...
2023-09-07 06:46:49,659 INFO metabase.util :: Database setup took 43.4 s
2023-09-07 06:46:49,663 INFO cmd.copy :: [OK]
2023-09-07 06:46:49,664 INFO cmd.copy :: Testing if target mysql database is already populated...
2023-09-07 06:46:49,666 INFO cmd.copy :: [OK]
2023-09-07 06:46:49,669 INFO cmd.copy :: Clearing default entries created by Liquibase migrations...
2023-09-07 06:46:49,671 INFO cmd.copy :: Temporarily disabling DB constraints...
2023-09-07 06:46:49,680 INFO cmd.copy :: [OK]
2023-09-07 06:46:52,592 INFO cmd.copy :: Re-enabling DB constraints...
2023-09-07 06:46:52,595 INFO cmd.copy :: [OK]
2023-09-07 06:46:52,596 INFO cmd.copy :: [OK]
2023-09-07 06:46:52,599 INFO cmd.copy :: Temporarily disabling DB constraints...
2023-09-07 06:46:52,600 INFO cmd.copy :: [OK]
2023-09-07 06:46:52,612 INFO cmd.copy :: Copying instances of Database...
2023-09-07 06:46:52,630 INFO cmd.copy :: copied 12 instances.
2023-09-07 06:46:52,647 INFO cmd.copy :: Copying instances of User...
2023-09-07 06:46:52,926 INFO cmd.copy :: copied 743 instances.
2023-09-07 06:46:52,932 INFO cmd.copy :: Copying instances of Setting...
2023-09-07 06:46:52,947 INFO cmd.copy :: copied 35 instances.
2023-09-07 06:46:54,131 INFO cmd.copy :: Copying instances of Table...
2023-09-07 06:46:54,143 ERROR cmd.copy :: BatchUpdateException:
Message: (conn=687) Unknown column 'fields_hash' in 'field list'
SQLState: 42S22
Error Code: 1054

2023-09-07 06:46:54,147 INFO cmd.copy :: Re-enabling DB constraints...
2023-09-07 06:46:54,147 INFO cmd.copy :: [OK]
clojure.lang.ExceptionInfo: Error copying instances of Table {:entity "Table"}
at metabase.cmd.copy$copy_data_BANG_$fn__85581$fn__85582.invoke(copy.clj:185)
at metabase.cmd.copy$copy_data_BANG_$fn__85581.invoke(copy.clj:182)
at clojure.core$partition_all$fn__8625$fn__8626.invoke(core.clj:7332)
at clojure.java.jdbc$init_reduce_rs.invokeStatic(jdbc.clj:1205)
at clojure.java.jdbc$init_reduce_rs.invoke(jdbc.clj:1197)
at clojure.java.jdbc$reducible_result_set_STAR_$reify__21603.reduce(jdbc.clj:1227)
at clojure.core$reduce.invokeStatic(core.clj:6885)
at clojure.core$reduce.invoke(core.clj:6868)
at clojure.java.jdbc$query_reducer$fn__21609$fn__21612.invoke(jdbc.clj:1262)
at clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1091)
at clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1084)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1106)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093)
at clojure.java.jdbc$reducible_query$reify__21626.reduce(jdbc.clj:1357)
at clojure.core$transduce.invokeStatic(core.clj:6946)
at clojure.core$transduce.invoke(core.clj:6933)
at metabase.cmd.copy$copy_data_BANG_.invokeStatic(copy.clj:170)
at metabase.cmd.copy$copy_data_BANG_.invoke(copy.clj:162)
at metabase.cmd.copy$fn__85686$copy_BANG___85691$fn__85692$fn__85701$fn__85702$fn__85703.invoke(copy.clj:357)
at metabase.cmd.copy$do_with_disabled_db_constraints.invokeStatic(copy.clj:262)
at metabase.cmd.copy$do_with_disabled_db_constraints.invoke(copy.clj:258)
at metabase.cmd.copy$fn__85686$copy_BANG___85691$fn__85692$fn__85701$fn__85702.invoke(copy.clj:356)
at metabase.cmd.copy$do_with_connection_rollback_only.invokeStatic(copy.clj:204)
at metabase.cmd.copy$do_with_connection_rollback_only.invoke(copy.clj:202)
at metabase.cmd.copy$fn__85686$copy_BANG___85691$fn__85692$fn__85701.invoke(copy.clj:354)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:807)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:776)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:852)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:776)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:789)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:776)
at metabase.cmd.copy$fn__85686$copy_BANG___85691$fn__85692.invoke(copy.clj:351)
at metabase.cmd.copy$fn__85686$copy_BANG___85691.invoke(copy.clj:329)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:35)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:25)
at clojure.lang.Var.invoke(Var.java:384)
at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:45)
at metabase.cmd$load_from_h2.invoke(cmd.clj:39)
at clojure.lang.AFn.applyToHelper(AFn.java:154)
at clojure.lang.AFn.applyTo(AFn.java:144)
at clojure.core$apply.invokeStatic(core.clj:667)
at clojure.core$apply.invoke(core.clj:662)
at metabase.cmd$run_cmd$fn__86478.invoke(cmd.clj:236)
at metabase.cmd$run_cmd.invokeStatic(cmd.clj:236)
at metabase.cmd$run_cmd.invoke(cmd.clj:227)
at clojure.lang.Var.invoke(Var.java:388)
at metabase.core$run_cmd.invokeStatic(core.clj:166)
at metabase.core$run_cmd.invoke(core.clj:164)
at metabase.core$main.invokeStatic(core.clj:188)
at metabase.core$main.doInvoke(core.clj:183)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.lang.Var.applyTo(Var.java:705)
at clojure.core$apply.invokeStatic(core.clj:667)
at clojure.core$apply.invoke(core.clj:662)
at metabase.bootstrap$main.invokeStatic(bootstrap.clj:25)
at metabase.bootstrap$main.doInvoke(bootstrap.clj:22)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at metabase.bootstrap.main(Unknown Source)
Caused by: java.sql.BatchUpdateException: (conn=687) Unknown column 'fields_hash' in 'field list'
at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:323)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeBatch(ClientSidePreparedStatement.java:299)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544)
at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:598)
at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:591)
at clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:1058)
at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:1042)
at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:1080)
at clojure.java.jdbc$db_do_prepared.invoke(jdbc.clj:1060)
at clojure.java.jdbc$insert_cols_BANG
.invokeStatic(jdbc.clj:1594)
at clojure.java.jdbc$insert_cols_BANG
.invoke(jdbc.clj:1585)
at clojure.java.jdbc$insert_multi_BANG
.invokeStatic(jdbc.clj:1653)
at clojure.java.jdbc$insert_multi_BANG
.invoke(jdbc.clj:1619)
at metabase.cmd.copy$insert_chunk_BANG_.invokeStatic(copy.clj:153)
at metabase.cmd.copy$insert_chunk_BANG_.invoke(copy.clj:147)
at metabase.cmd.copy$copy_data_BANG_$fn__85581$fn__85582.invoke(copy.clj:183)
... 57 more
Caused by: java.sql.SQLSyntaxErrorException: (conn=687) Unknown column 'fields_hash' in 'field list'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:158)
at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:319)
... 72 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Unknown column 'fields_hash' in 'field list'
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.access$000(AbstractQueryProtocol.java:107)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol$1.handleResultException(AbstractQueryProtocol.java:692)
at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:141)
at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:67)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLException: Unknown column 'fields_hash' in 'field list'
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1695)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1557)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520)
at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:132)
... 5 more
Command failed with exception: Error copying instances of Table

@flamber , The Issue is resolved. I created the missing CLOB column "fields_hash" in metabase_table in MySQL.
Later, a couple of minor (UNIQUE KEY) hiccups occurred due to H2's case-sensitive nature and MySQL's case-insensitive nature.
Dropped the idx_uniq_table_db_id_schema_name UNIQUE KEY constraint on metabase_table, cleaned up the duplicate data, and added back the UNIQUE KEY.

Finally, we are on MySQL Application DB, and Metabase is super fast now.