I’ve been over the migration documentation, as well as the export of H2 db, and how to run some commands in docker from the host. What I’m not certain of is how to migrate from the docker db to my locally hosted mysql db? Do I need to run the migrate command in the docker container and map access out to the mysql local instance?
Hi @bmcgonag
All the documentation is referring to a hostname for the database - no matter if that’s another Docker, the host, or another server. It’s difficult to have an example for every single possible setup that people might be using, so the documentation expects that you have knowledge about your setup.
If you’re running with the H2 inside of the container - meaning you would lose all information if you removed (or upgraded) the container - then you should make sure you have a backup as soon as possible. And then change that, so it’s stored outside of the container, or preferably use another database instead of H2.
The H2 is stored in /metabase.db/metabase.db.mv.db by default.
The example in the documentation shows the H2 hosted on a volume outside the container and migration to Postgres. You just need to make a few tweaks to that.
https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html#migrating-from-h2-to-postgres-as-the-metabase-application-database
You would first need to get your H2 from the existing container, so it’s stored outside, and then adjust the volume and MB_DB_FILE accordingly. And of course MB_DB_TYPE=mysql
1 Like
Thanks for the quick reply. I do have the H2 db backed up now, and it is outside the container.
I just downloaded the metabase.jar to try and do the migration outside the container and keep things simple for now, and have hit a new snag where it errors out saying it couldn’t login to mysql (i’m using mariadb) with password = NO. I have my password env variable set, and the other env vars stated in the documentation, so not sure why it’s trying to login using password=No.
It’s mariadb version 15.1 on Ubuntu 19.10 if that matters.
I found an answer on here about mysql specifically, and that the newest version of mysql uses sha256 passwrods, while mariadb doesn’t and metabase doesn’t, but still not sure how that would affect me if i’m using mariadb. Still digging.
@bmcgonag
Make sure that you’re using the same version of Metabase, since upgrading and migrating at the same time can result in errors if the schema has changed.
There’s no such MariaDB version. Latest is 10.4.x. Just make sure that you’re using mysql_native_password for logins on MariaDB, so Metabase is not having issues with one of the newer authentication plugins.
Post your commands (remember to add ticks (`) around, so it preserves formatting) - it makes it much easier to help.
mariadb Ver 15.1 Distrib 10.3.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Sorry, guess I was reading that first number after doing mariadb --version. But I suppose it’s 10.3.20.
Let me see if I can change the password to be using mysql_native_password
@bmcgonag
I’m fairly sure that 10.3 uses mysql_native_password by default - if I remember correctly, there were a lot of changes in 10.4 about security and logins.
But post the java ... command that you’re using to execute the migration of Metabase.
java -jar ~/Downloads/metabase.jar load-from-h2 ~/metabase/metabase.db
In that command metabase.db is actually metabase.db.mv.db, but doing as the documentation suggests.
Ouput is like this:
01-05 18:30:41 DEBUG plugins.classloader :: Using NEWLY CREATED classloader as shared context classloader: clojure.lang.DynamicClassLoader@78010562
01-05 18:30:42 INFO metabase.util :: Loading Metabase...
01-05 18:30:42 INFO metabase.util :: Maximum memory available to JVM: 3.9 GB
01-05 18:30:45 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance. 🔓
For more information, see https://metabase.com/docs/latest/operations-guide/encrypting-database-details-at-rest.html
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.bouncycastle.jcajce.provider.drbg.DRBG (file:/home/brian/Downloads/metabase.jar) to constructor sun.security.provider.Sun()
WARNING: Please consider reporting this to the maintainers of org.bouncycastle.jcajce.provider.drbg.DRBG
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
01-05 18:30:49 INFO metabase.db :: Verifying mysql Database Connection ...
01-05 18:30:50 INFO driver.impl :: Registered abstract driver :sql 🚚
Load driver :sql took 69.8 ms
01-05 18:30:50 INFO driver.impl :: Registered abstract driver :sql-jdbc (parents: [:sql]) 🚚
Load driver :sql-jdbc took 84.0 ms
01-05 18:30:50 INFO driver.impl :: Registered driver :mysql (parents: [:sql-jdbc]) 🚚
Load driver :mysql took 165.7 ms
01-05 18:30:50 INFO driver.impl :: Initializing driver :sql...
01-05 18:30:50 INFO driver.impl :: Initializing driver :sql-jdbc...
01-05 18:30:50 INFO driver.impl :: Initializing driver :mysql...
01-05 18:30:50 ERROR driver.util :: Database connection error
java.sql.SQLInvalidAuthorizationSpecException: Could not connect to address=(host=localhost)(port=3306)(type=master) : Access denied for user 'root'@'localhost' (using password: NO)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:239)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1241)
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:610)
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:142)
at org.mariadb.jdbc.Driver.connect(Driver.java:86)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
at clojure.java.jdbc$get_driver_connection.invokeStatic(jdbc.clj:271)
at clojure.java.jdbc$get_driver_connection.invoke(jdbc.clj:250)
at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:411)
at clojure.java.jdbc$get_connection.invoke(jdbc.clj:274)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1093)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1142)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invokeStatic(connection.clj:159)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invoke(connection.clj:154)
at metabase.driver.sql_jdbc$fn__69874.invokeStatic(sql_jdbc.clj:35)
at metabase.driver.sql_jdbc$fn__69874.invoke(sql_jdbc.clj:34)
at clojure.lang.MultiFn.invoke(MultiFn.java:234)
at metabase.driver.util$can_connect_with_details_QMARK_$fn__21110.invoke(util.clj:32)
at metabase.util$do_with_timeout$fn__13131.invoke(util.clj:302)
at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
at clojure.lang.AFn.call(AFn.java:18)
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:830)
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authenticationHandler(AbstractConnectProtocol.java:729)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:507)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1236)
... 28 more
java.lang.Exception: Could not connect to address=(host=localhost)(port=3306)(type=master) : Access denied for user 'root'@'localhost' (using password: NO)
at metabase.driver.util$can_connect_with_details_QMARK_.invokeStatic(util.clj:37)
at metabase.driver.util$can_connect_with_details_QMARK_.doInvoke(util.clj:20)
at clojure.lang.RestFn.invoke(RestFn.java:442)
at clojure.lang.Var.invoke(Var.java:393)
at metabase.db$fn__19649$verify_db_connection__19658$fn__19661$fn__19662.invoke(db.clj:412)
at metabase.db$fn__19649$verify_db_connection__19658$fn__19661.invoke(db.clj:410)
at metabase.db$fn__19649$verify_db_connection__19658.invoke(db.clj:403)
at metabase.db$fn__19649$verify_db_connection__19658$fn__19659.invoke(db.clj:406)
at metabase.db$fn__19649$verify_db_connection__19658.invoke(db.clj:403)
at metabase.db$setup_db_BANG__STAR_$fn__19694.invoke(db.clj:474)
at metabase.util$do_with_us_locale.invokeStatic(util.clj:664)
at metabase.util$do_with_us_locale.invoke(util.clj:650)
at metabase.db$setup_db_BANG__STAR_.invokeStatic(db.clj:473)
at metabase.db$setup_db_BANG__STAR_.invoke(db.clj:469)
at metabase.db$setup_db_from_env_BANG__STAR_.invokeStatic(db.clj:483)
at metabase.db$setup_db_from_env_BANG__STAR_.invoke(db.clj:480)
at metabase.db$fn__19699$fn__19700.invoke(db.clj:490)
at clojure.lang.Delay.deref(Delay.java:42)
at clojure.core$deref.invokeStatic(core.clj:2320)
at clojure.core$deref.invoke(core.clj:2306)
at clojure.core$partial$fn__5839.invoke(core.clj:2623)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:240)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:234)
at clojure.lang.Var.invoke(Var.java:384)
at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:37)
at metabase.cmd$load_from_h2.invoke(cmd.clj:30)
at clojure.lang.AFn.applyToHelper(AFn.java:154)
at clojure.lang.AFn.applyTo(AFn.java:144)
at clojure.core$apply.invokeStatic(core.clj:665)
at clojure.core$apply.invoke(core.clj:660)
at metabase.cmd$run_cmd$fn__60190.invoke(cmd.clj:127)
at metabase.cmd$run_cmd.invokeStatic(cmd.clj:127)
at metabase.cmd$run_cmd.invoke(cmd.clj:123)
at clojure.lang.Var.invoke(Var.java:388)
at metabase.core$run_cmd.invokeStatic(core.clj:132)
at metabase.core$run_cmd.invoke(core.clj:130)
at metabase.core$_main.invokeStatic(core.clj:141)
at metabase.core$_main.doInvoke(core.clj:137)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at metabase.core.main(Unknown Source)
Caused by: java.sql.SQLInvalidAuthorizationSpecException: Could not connect to address=(host=localhost)(port=3306)(type=master) : Access denied for user 'root'@'localhost' (using password: NO)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:239)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1241)
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:610)
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:142)
at org.mariadb.jdbc.Driver.connect(Driver.java:86)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
at clojure.java.jdbc$get_driver_connection.invokeStatic(jdbc.clj:271)
at clojure.java.jdbc$get_driver_connection.invoke(jdbc.clj:250)
at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:411)
at clojure.java.jdbc$get_connection.invoke(jdbc.clj:274)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1093)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1142)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invokeStatic(connection.clj:159)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invoke(connection.clj:154)
at metabase.driver.sql_jdbc$fn__69874.invokeStatic(sql_jdbc.clj:35)
at metabase.driver.sql_jdbc$fn__69874.invoke(sql_jdbc.clj:34)
at clojure.lang.MultiFn.invoke(MultiFn.java:234)
at metabase.driver.util$can_connect_with_details_QMARK_$fn__21110.invoke(util.clj:32)
at metabase.util$do_with_timeout$fn__13131.invoke(util.clj:302)
at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
at clojure.lang.AFn.call(AFn.java:18)
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:830)
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authenticationHandler(AbstractConnectProtocol.java:729)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:507)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1236)
... 28 more
Command failed with exception: Could not connect to address=(host=localhost)(port=3306)(type=master) : Access denied for user 'root'@'localhost' (using password: NO)
And again, I truly appreciate all of your help on this. Seriously, this is what makes me love open source projects like Metabase. Thank you.
Try connecting with MariaDBs client:
mysql --host=localhost --user=root --password=YourPassword
Maybe it’s setup to only allow keys, or perhaps not allowing on localhost, but only a socket, which Metabase currently doesn’t support.
Generally when migrating, I prefer not to use global environment variables, but simply push them together in the command, like so (makes everything more visible, less chance of a mistake):
java -DMB_DB_TYPE=mysql -DMB_DB_HOST=localhost -DMB_DB_PORT=3306 -DMB_DB_USER=root -DMB_DB_PASS=YourPassword -DMB_DB_DBNAME=metabase -jar ~/Downloads/metabase.jar load-from-h2 ~/metabase/metabase.db
I would recommend that you create a separate user on MariaDB instead of using root, which is bad practice.
Remember to enclose the password value in quotes if it contains special characters, like -DMB_DB_PASS="long-cryptic-password"