How can I access metabase own database? I want to access query, query_execution table. I've connected mongodb with metabase but not sure how to access metabase own database. I'm using open source docker image.
Hi @kayumuzzaman
You just connect to the application database just like any other data source in Admin > Databases.
If you are using H2, then you should migrate away from that:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
I'm using mongodb as my application database & also I've connected this mongodb with metabase by data source in Admin > Databases. Now I want to access metabase's own database, If I need to add this database, what will be the default database name, database type, host & port?
@kayumuzzaman You are not using Mongo as the Metabase application database. It's either H2, Postgres or MySQL/MariaDB.
Read this to understand what the Metabase application database is:
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html
And post "Diagnostic Info" from Admin > Troubleshooting.
Here is the "Diagnostic Info" & yes, it's using H2 for metabase application database. what should be the "Connection String" if I want to connect this database?
{
"browser-info": {
"language": "en-US",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.12+7",
"java.vendor": "Eclipse Foundation",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.12",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.12+7",
"os.name": "Linux",
"os.version": "5.13.0-44-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"mongo"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-10-21",
"tag": "v0.41.1",
"branch": "release-x.41.x",
"hash": "76aa4a5"
},
"settings": {
"report-timezone": null
}
}
}- You should upgrade immediately:
https://github.com/metabase/metabase/security/advisories/GHSA-vmm4-cwrm-38rj - You should migrate away from H2 if you are using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
now I'm using it on dev environment, so I want to access the H2 database. how can I access that one? here is my docker-compose file for metabase:
metabase:
image: metabase/metabase
restart: unless-stopped
depends_on:
- mongo
ports:
- '3001:3001'
volumes:
- '/tmp/compose/infrastructure/metabase:/metabase-data'
environment:
- MB_DB_FILE=/metabase-data/metabase.db
- MB_JETTY_HOST=0.0.0.0
- MB_JETTY_PORT=3001
deploy:
replicas: 1@kayumuzzaman Then use /metabase-data/metabase.db for the H2 file path.
thanks, I can access it through metabase. Is there any way to truncate table "query_execution" periodically for open source?
@kayumuzzaman There's a request for that:
https://github.com/metabase/metabase/issues/4155 - upvote by clicking 
on the first post
You can do it manuall via a custom query that truncates it. Example:
java -cp metabase.jar org.h2.tools.RunScript -script truncate-query-script.sql -url jdbc:h2:./metabase.db
If you cannot get that to work, then migrate away from H2. You should not be using H2.
upvoted! one more question, can I access this H2 database locally as it is coming from metabase.db file. if possible, how can I access this locally? also if the "query_execution" table becomes larger, so does the metabase.db file right?
@kayumuzzaman Pretty please. Migrate away from H2. Please. You're not going to use H2 anyway, so just stop using it now, since you're clearly done testing Metabase.
Have a look on the official H2 documentation page. Make sure you use the same driver versions as Metabase, which is currently 1.4.197: http://www.h2database.com/
how can i migrate from h2 to mongo?
@kayumuzzaman I'm pretty sure I've stated a few times that you can only use H2, Postgres or MySQL/MariaDB.
Please read this: https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html
I've migrated from h2 to postgres, now metabase own database will write on this new postgres or on the old h2 again?
@kayumuzzaman If you have setup Metabase to use Postgres as the application database, then Metabase will only communicate with that.
Check "Diagnostic Info" from Admin > Troubleshooting to see which application database it's using.
How to setup metabase to use Postgres as the application database? I didn't do any setup, thats why it's still using H2 database. I want to switch to postgres.
@kayumuzzaman Read this: https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html
At my company , we did the trick of connecting the Postgres of Metabase on Metabase. Message me if you have still this problem.