Configuring metabase with mySQL

Hi,

I’m trying to install the application using MySQL.
I’ve followed the instructions (https://www.metabase.com/docs/latest/operations-guide/start.html#configuring-the-metabase-application-database)

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
java -jar metabase.jar

But when I check, I still have metabase.db.mv.db.
It looks like I’m still using H2 Database.

Thanks in advance

Please include your environment info such as:
Metabase: version
Java: version
Databases: H2 internal default, & whatever else joined in
Host: for example Localhost:3000
OS: type & version
Browser: type & version

Having similar issues and letting folks know the above was key to getting started (my OS is Windows, still working on using MySQL instead of H2)!

Thanks for your answers and sorry for the miss

Metabase: 0.29.3
Java: version : 1.8.0_102
Databases: H2 internal default
Host: Localhost:3000
OS: Linux Centos 7
Browser: FireFox Quantum 60.0.1

THanks in advance

So if you’re on Centos I presume you’re using bash or something else that use export to set environment variables? If not an option is to try sticking the environment vars directly as java arguments like @mesquest and I worked on in Jar file migration H2 to MySQL in Windows?

Can you post the log output from your console? It should contain info on Metabase using MySQL not H2 as application database

What MySQL version are you using?

Thanks for your message and sorry for this late answer but I’m a newbie.
I’ve tried to set the environment variables in the bash_profile but it doesn’t work

export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=root
export MB_DB_PASS=XXXXX
export MB_DB_HOST=localhost

While launching the jar file, I have :

06-04 08:23:12 INFO metabase.util :: Loading Metabase…
06-04 08:23:21 INFO util.encryption :: DB details encryption is DISABLED for this Metabase instance. :unlock:
See http://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest for more information.
06-04 08:23:35 INFO metabase.core :: Starting Metabase in STANDALONE mode
06-04 08:23:35 INFO metabase.core :: Launching Embedded Jetty Webserver with config:
{:port 3000}

06-04 08:23:35 INFO metabase.core :: Starting Metabase version v0.29.3 (0de4585 release-0.29.3) …
06-04 08:23:39 INFO metabase.core :: Setting up and migrating Metabase DB. Please sit tight, this may take a minute…
06-04 08:23:39 INFO metabase.db :: Verifying mysql Database Connection …
06-04 08:23:39 ERROR metabase.driver :: Failed to connect to database: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database ‘metabase’
java.lang.AssertionError: Assert failed: Unable to connect to Metabase mysql DB.
(binding [allow-potentailly-unsafe-connections true] (require (quote metabase.driver)) ((resolve (quote metabase.driver/can-connect-with-details?)) engine details))
at metabase.db$verify_db_connection.invokeStatic(db.clj:349)
at metabase.db$verify_db_connection.invoke(db.clj:342)
at metabase.db$verify_db_connection.invokeStatic(db.clj:345)
at metabase.db$verify_db_connection.invoke(db.clj:342)
at metabase.db$setup_db_BANG_.invokeStatic(db.clj:398)
at metabase.db$setup_db_BANG_.doInvoke(db.clj:392)
at clojure.lang.RestFn.invoke(RestFn.java:421)
at metabase.core$init_BANG_.invokeStatic(core.clj:154)
at metabase.core$init_BANG_.invoke(core.clj:133)
at metabase.core$start_normally.invokeStatic(core.clj:258)
at metabase.core$start_normally.invoke(core.clj:251)
at metabase.core$_main.invokeStatic(core.clj:279)
at metabase.core$_main.doInvoke(core.clj:274)
at clojure.lang.RestFn.invoke(RestFn.java:397)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
at clojure.lang.RestFn.applyTo(RestFn.java:132)
at metabase.core.main(Unknown Source)
06-04 08:23:39 ERROR metabase.core :: Metabase Initialization FAILED: Assert failed: Unable to connect to Metabase mysql DB.
(binding [allow-potentailly-unsafe-connections true] (require (quote metabase.driver)) ((resolve (quote metabase.driver/can-connect-with-details?)) engine details))
06-04 08:23:39 INFO metabase.core :: Metabase Shutting Down …
06-04 08:23:39 INFO metabase.core :: Metabase Shutdown COMPLETE

And I use 5.5.52-MariaDB

Should I first create a database metabase or does the application create it while launching the jar file ?

Thanks in advance

It needs to be working with the default database before you configure MySQL, then you run a migration. Details here:

https://www.metabase.com/docs/latest/operations-guide/start.html#migrating-from-using-the-h2-database-to-mysql-or-postgres

Thanks for your answer.
I add a service for metabase :

[Unit]
Description=Metabase

[Service]
Environment=MB_DB_TYPE=h2
Environment=MB_DB_FILE=/app/metabases
Environment=MB_EMOJI_IN_LOGS=false
ExecStart=/usr/bin/java -jar /app/metabases/metabase.jar
Restart=always
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase

[Install]
WantedBy=multi-user.target

Shoud I replace ExecStart=/usr/bin/java -jar /metabases/metabase.jar by usr/bin/java -jar /metabases/metabase.jar load-from-h2 /metabases/metabase.db

Should I set the environnement variables in the bash_profile ?

Have you got the default install working? If not, just kick off the jar file and let it do its stuff.

I didn’t find the default install. I only found the jar file.

java -jar metabase.jar
(you’ll need to do something with the path)

That will setup the default for you.

sorry, i didn’t get your previous message.
The default install works fine but I have a H2 base.
I will try to launch the migration but I’d like to know what I should do to set the environnement variables :
Should I set them in the bash_profile or each time, should I write in the console
export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase
export MB_DB_PORT=5432
export MB_DB_USER=
export MB_DB_PASS=
export MB_DB_HOST=localhost
java -jar metabase.jar load-from-h2 /path/to/metabase.db

I 've modified my metabase.service file :

[Unit]
Description=Metabase

[Service]
Environment=MB_DB_TYPE=mysql
Environment=MB_DB_DBNAME=metabase
Environment=MB_DB_PORT=3306
Environment=MB_DB_USER=root
Environment=MB_DB_PASS=xxxx
Environment=MB_DB_HOST=localhost
Environment=MB_EMOJI_IN_LOGS=false
ExecStart=/usr/bin/java -jar /app/metabases/metabase.jar
Restart=always
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase

[Install]
WantedBy=multi-user.target

I’ve created a mysql database and I migrate the H2 base by launching the command :

java -jar metabase.jar load-from-h2 /path/to/metabase.db

I got an error :

Message: Field ‘database_type’ doesn’t have a default value
SQLState: HY000
Error Code: 1364
java.sql.BatchUpdateException: Field ‘database_type’ doesn’t have a default value
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:533)
at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:526)
at clojure.java.jdbc$db_do_execute_prepared_statement$fn__1845.invoke(jdbc.clj:960)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:784)
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 clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:959)
at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:945)
at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:983)
at clojure.java.jdbc$db_do_prepared.invoke(jdbc.clj:963)
at clojure.java.jdbc$insert_cols_BANG_.invokeStatic(jdbc.clj:1476)
at clojure.java.jdbc$insert_cols_BANG_.invoke(jdbc.clj:1467)
at clojure.java.jdbc$insert_multi_BANG_.invokeStatic(jdbc.clj:1526)
at clojure.java.jdbc$insert_multi_BANG_.invoke(jdbc.clj:1501)
at metabase.cmd.load_from_h2$insert_entity_BANG_$fn__36711.invoke(load_from_h2.clj:135)
at metabase.cmd.load_from_h2$insert_entity_BANG_.invokeStatic(load_from_h2.clj:134)
at metabase.cmd.load_from_h2$insert_entity_BANG_.invoke(load_from_h2.clj:114)
at metabase.cmd.load_from_h2$load_data_BANG_.invokeStatic(load_from_h2.clj:150)
at metabase.cmd.load_from_h2$load_data_BANG_.invoke(load_from_h2.clj:144)
at metabase.cmd.load_from_h2$load_from_h2_BANG_$fn__36793.invoke(load_from_h2.clj:225)
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__38501.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)
Caused by: java.sql.SQLException: Field ‘database_type’ doesn’t have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
… 48 more
Command failed with exception: Field ‘database_type’ doesn’t have a default value

When I launch the application, it’s like a fresh installation.

Any help please

When I had issues with environmental vars I passed them in the command like this: java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=meta -DMB_DB_PASS=“Password” -DMB_DB_HOST=123.123.123.123 metabase.jar

To execute in the background: nohup java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=meta -DMB_DB_PASS=“Password” -DMB_DB_HOST=123.123.123.123 metabase.jar &

1- I have set the environnmental vars in the .bash_profile

2- I have created the metabase.service :
[Unit]
Description=Metabase

[Service]
Environment=MB_DB_TYPE=mysql
Environment=MB_DB_DBNAME=metabase
Environment=MB_DB_PORT=3306
Environment=MB_DB_USER=
Environment=MB_DB_PASS=
Environment=MB_DB_HOST=localhost
Environment=MB_EMOJI_IN_LOGS=false
ExecStart=/usr/bin/java -jar /metabase/metabase.jar
Restart=always
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase

[Install]
WantedBy=multi-user.target

3- I have created the database metabase on my mysql server

4- I have deleted the H2 bases

5- I’have started the metabase service

It works !!!
Thanks for your help