Jar file migration H2 to MySQL in Windows?

mesquest · May 21, 2018, 9:15pm

The following give a general direction at best:
Migrating from using the H2 database to MySQL or Postgres
Operations guide is not enough to migrate to MySQL database on Windows

Between documentation, discourse, and github issues isn’t there a simple, clear, concise “How To” on jar file migration from H2 to MySQL in a Windows environment?

Metabase: 0.29.2
Java: 1.8.0_45
Databases: H2 internal default, Server SQL external (joined in)
Host: Localhost:3000
OS: Microsoft Windows [Version 6.0.6002]
Browser: Firefox 52.8.0

jornh · May 21, 2018, 10:57pm

Apart from that the guide probably could do with a note that export MB_env_key=env_value is equivalent to set in CMD.EXE and $env.MB_env_key = 'env_value' in Powershell for that matter I think it’s short sweet and to the point.

You don’t mention what you have tried - and what error/problem you get -
also what MySQL version are you using - note version 8 got released fairly recently and has issues - that is getting narrowed and nailed as we speak:

edit I just got the impulse that adding the environment variables to be part of the java command line arguments with -D definitions would make it agnostic to the OS, like so:

java -DMB_DB_TYPE=postgres ......<more> ...  .. -jar .\metabase.jar ... <more> ...

If that is the stumbling block?

mesquest · May 21, 2018, 11:56pm

Thanks @jornh, I appreciate the input. MySQL 5.6.24 for the moment, the stumbling block is I’m obviously misunderstanding how to go about this. I’m in the command prompt (as admin) trying one command at a time like so:

c:\Program Files\Metabase>export MB_DB_TYPE=mysql
‘export’ is not recognized as an internal or external command,
operable program or batch file.

Alternatively like so:
c:\Program Files\Metabase>MB_DB_TYPE=mysql
‘MB_DB_TYPE’ is not recognized as an internal or external command,
operable program or batch file.

And the rather dated post cited does end talking about environment variables in Windows being an issue… Looked up Environment Variables and that’s even more confusing…

Honestly, I haven’t a clue at this point, not even the slightest!..

+++++++++++++
Just saw the edit at the end of your response on refresh… So invoke java -d then whatever command…

c:\Program Files\Metabase>java -d MB_DB_TYPE=mysql
Unrecognized option: -d
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

c:\Program Files\Metabase>java -dMB_DB_TYPE=mysql
Unrecognized option: -dMB_DB_TYPE=mysql
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

Nope, I’m still messing something up…

+++++++++++++
Ah Ha! Case sensitive and doesn’t like spaces… Touchy ain’t it! Ignoring all the clutter that pops up after every step for the time being, it appears to have at least tried to do something but failed…

c:\Program Files\Metabase>java -DMB_DB_TYPE=mysql
c:\Program Files\Metabase>java -DMB_DB_DBNAME=metabase
c:\Program Files\Metabase>java -DMB_DB_PORT=3306
c:\Program Files\Metabase>java -DMB_DB_USER=xxxxxxxxxxxxxxx@xxxxx.com
c:\Program Files\Metabase>java -DMB_DB_PASS=xxxxxxxxxx
c:\Program Files\Metabase>java -DMB_DB_HOST=localhost
c:\Program Files\Metabase>java -jar metabase.jar load-from-h2

05-21 21:26:36 ←[1mINFO metabase.util←[0m :: Loading Metabase…
05-21 21:26:50 ←[1mINFO util.encryption←[0m :: DB details encryption is DISABLED
for this Metabase instance.
See http://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-y
our-database-connection-details-at-rest for more information.
java.io.FileNotFoundException: C:\Program%20Files\Metabase\metabase.jar (The sys
tem cannot find the path specified)
05-21 21:27:40 ←[1mINFO metabase.db←[0m :: Verifying h2 Database Connection …
05-21 21:27:42 ←[1mINFO metabase.db←[0m :: Verify Database Connection …
05-21 21:27:42 ←[1mINFO metabase.db←[0m :: Running Database Migrations…
05-21 21:27:42 ←[1mINFO metabase.db←[0m :: Setting up Liquibase…
05-21 21:27:42 ←[1mINFO metabase.db←[0m :: Liquibase is ready.
05-21 21:27:42 ←[1mINFO metabase.db←[0m :: Checking if Database has unrun migrat
ions…
05-21 21:27:56 ←[1mINFO metabase.db←[0m :: Database Migrations Current …
com.mchange.v2.cfg.DelayedLogItem [ level -> FINE, text -> “The configuration fi
le 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(l
oad_from_h2.clj:179)
at metabase.cmd.load_from_h2$disable_db_constraints_BANG_.invoke(load_fr
om_h2.clj:177)
at metabase.cmd.load_from_h2$load_from_h2_BANG_$fn__36792.invoke(load_fr
om_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_h
2.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 metabase.cmd$load_from_h2.invokeStatic(cmd.clj:32)
at metabase.cmd$load_from_h2.invoke(cmd.clj:29)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
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__38500.invoke(cmd.clj:132)
at metabase.cmd$run_cmd.invokeStatic(cmd.clj:132)
at metabase.cmd$run_cmd.invoke(cmd.clj:128)
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

Now I really am stuck!

jornh · May 22, 2018, 5:32am

Even more so. If you are going the route with Java defines they won’t live on after that specific Java run, so they all have to go into the same execution call on one single line:

c:\Program Files\Metabase> java -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=xxxxxxxxxxxxxxx@xxxxx.com -DMB_DB_PASS=xxxxxxxxxx -DMB_DB_HOST=localhost -jar metabase.jar load-from-h2
c:\Program Files\Metabase>

NOTE: In the example above everything is in one line without any line breaks! So safest way is probably to copy from above everything between java and load-from-h2 including those two words

I could have broken it up with line continuation characters, but they all vary between command prompts so don’t even get me started on that topic . And with WSL on Windows 10 I have (and depending on the situation also use) all three of them: Bash, cmd.exe and Powershell…

Myth0S · May 22, 2018, 11:55am

I was having the same issue as you so I’ve installed a bash emulator on Windows to do this. The bash emulator is called win-bash and can be found here http://win-bash.sourceforge.net/

Hope this helps.

mesquest · May 23, 2018, 2:04am

Thanks @jornh, everything in a single command line did the trick!

There’s quite a few ‘tricks’ when it comes to Metabase on Windows. As noted in this post previously cited the ‘export’ part shown in the migration documentation is ignored. And for development proposes on one’s own machine there may be no password associated with the user so that part can also be omitted. In the end once one creates a new MySQL db and verifies user and db attributes that must be stated on the right side of each equality statement the command line should look something like this:

java -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=root -DMB_DB_HOST=localhost -jar metabase.jar load-from-h2

And to top it all off, at least in my case, to get the Metabase signin page on ‘localhost:3000’ to show up it further requires using Windows Explorer to navigate to the Metabase jar file location and double click on the jar file before quickly switching back to the command prompt to issue a lock release like so: java -jar metabase.jar migrate release-locks

Upgrading is the same deal, double clicking on the jar file before issuing the ‘java -jar metabase.jar’ command and after, once again, the whole lock release thing from the top (otherwise you’ll get stuck in a repeating loop like I did recently)!

Windows has it’s eccentricities and the Metabase documentation was probably written from a developers’ Unix point of view. For those of us with significantly less knowledge and fluency the vocabulary alone is a huge challenge. That’s what makes this forum and the one on github so valuable, nothing replaces boots on the ground experiences shared!

So thanks again @jornh, I owe you one!

mesquest · May 23, 2018, 4:08pm

Thanks @Myth0S, that’s a option for local development for sure!

mesquest · May 25, 2018, 2:47pm

@jornh What’s causing the exception error using the following command?

java -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=xxxxxxx@gmail.com -DMB_DB_PASS=xxxxx -DMB_DB_HOST=localhost -jar metabase.jar load-from-h2 C:\xampplite\mysql\data\metabase\db.opt

org.h2.jdbc.JdbcSQLException: Database “C:/xampplite/mysql/data/metabase/db.opt” not found [90013-194]

Command failed with exception: Database “C:/xampplite/mysql/data/metabase/db.opt” not found [90013-194]

An empty mysql db was created to receive the h2 migration using PHPMyAdmin - MySQL Administration. The db name is metabase and it creates a folder named as such with a single db.opt file containing the db characteristics (very little):
default-character-set=latin1
default-collation=latin1_swedish_ci

As the migration proceeds the .frm and .ibd files appear but the .opt file is the only thing to point to when starting out. Omitting the path at the end of the command migrates the data but results in a static db as I soon discovered (my jubilation was very short lived).

How is the magic done of actually getting the Metabase application db based on mysql so as to log into it via http://localhost:3000/auth/login?

Metabase: 0.29.2
MySQL: 5.6.24
Databases: H2 internal default, Server SQL external (joined in)
Host: Localhost:3000
OS: Microsoft Windows [Version 6.0.6002]
Browser: Firefox 52.8.0

jornh · May 25, 2018, 5:03pm

When doing the migration the guide tells you to point to the metabase.db. Seems you are pointing to the MySQL opt file.

The error message then tells you that when it tries to parse the opt file it can’t read it as a metabase.db …

mesquest · May 25, 2018, 5:40pm

Thanks @jornh, read that
… load-from-h2 /path/to/metabase.db
and
It is expected that you will run the command against a brand-new (empty!) database;

That’s just it, there is no such thing as a *.db file in mysql.

diegombt · November 13, 2019, 6:06am

Using set instead of export in windows worked perfectly for me, thanks!