Create Database Error - Githubissues

iambudi commented 5 years ago

OSX, Crate 4.0.3 Metabase 0.33.0.0

When pressing SAVE button in Add Database page throws error : No method in multimethod 'connection-details->spec' for dispatch value: :crate I can connect to crate if using database type Postgres, but metabase can not browse the table data.

My question: How to specify user and password?

robd003 commented 4 years ago

I'm seeing the same issue with Metabase version 0.33.3

salsakran commented 4 years ago

@robd003 (and @iambudi )

While this is hosted under the Metabase org, the Crate driver was written by the Crate team, and we're hoping they'll be able to update it. One of the reasons we removed it from the core set of drivers and made it an add-on is that the core team doesn't use Crate and we don't have a lot of experience with it.

Nikos410 commented 4 years ago

We had a similar issue with our Firebird driver. This can possibly be fixed by replacing the function date-interval with date-add similar to these: https://github.com/metabase/metabase/commit/e7100252205e01c16daa431f52539dbf8d26c438#diff-d77ef6d7d60b04c2447263a86bfaeabf

shm-dmitry commented 4 years ago

I fixed this error in branch https://github.com/shm-dmitry/crate-driver Maybe it will be helpfull for somebody.

GregDerderian commented 4 years ago

Thanks @shm-dmitry for your solution. Do you know how I can make a crate driver jar file from your repo ? thanks in advance for your help !

shm-dmitry commented 4 years ago

Hi, @GregDerderian

install lein :)
Download metabase sources execute lein install-for-building-drivers in metabase source folder. This will compile dependency JAR and store it into your local .m2 folder.
Download sources with my patch. execute lein clean DEBUG=1 LEIN_SNAPSHOTS_IN_RELEASE=true lein uberjar

GregDerderian commented 4 years ago

thanks @shm-dmitry ! but it is still not working for me :( (sorry, it may not be the best place for that)

what do you mean by download metabase sources ?

I downloaded metabase.jar and run your first lein command on it but it raised an error that it is not a task.

I also tried to execute the 2nd command on your patch, but I also got an error :

Compiling metabase.driver.crate 
Syntax error compiling at (metabase/pulse/render.clj:151:12).
Syntax error compiling at (metabase/pulse/render.clj:151:12). 
No such var: mbql.u/datetime-field?

thanks again in advance !!

shm-dmitry commented 4 years ago

@GregDerderian , full instruction:

clone metabase repository:

:~/metabase$ git clone https://github.com/metabase/metabase.git
..... (skipped standard git output) .....
Done

Build dependency:

:~/metabase$ cd metabase/
:~/metabase/metabase$ lein install-for-building-drivers
Retrieving medley/medley/1.3.0/medley-1.3.0.pom from clojars
.......... (skipped very many lines from lein) .........
Compiling metabase.mbql.util.match
[include-drivers middleware] Attempting to include these drivers: #{}
[include-drivers middleware] including these drivers: #{}
Created /.../metabase/metabase/target/install-for-building-drivers+install/metabase-core-1.0.0-SNAPSHOT.jar
Wrote /.../metabase/pom.xml
Installed jar and pom into local repo.

clone my repo:

:~/metabase/metabase$ cd ..
:~/metabase$ git clone https://github.com/shm-dmitry/crate-driver.git
..... (skipped standard git output) .....
Done

build driver:

:~/metabase$ cd crate-driver
:~/metabase/crate-driver$ lein clean
:~/metabase/crate-driver$ DEBUG=1 LEIN_SNAPSHOTS_IN_RELEASE=true lein uberjar
Leiningen's classpath: :/usr/share/java/leiningen-2.8.1.jar
Applying task uberjar to []
Applying task javac to nil
Running javac with [-target 1.8 -source 1.8 @/tmp/.leiningen-cmdline2354202930242699216.tmp]
Applying task compile to nil
Compiling metabase.driver.crate
03-26 12:46:00 DEBUG plugins.classloader :: Using NEWLY CREATED classloader as shared context classloader: clojure.lang.DynamicClassLoader@58496dc
Created /.../metabase/crate-driver/target/uberjar+provided/crate-driver-1.0.1-crate-jdbc-2.3.0.jar
Including crate-driver-1.0.1-crate-jdbc-2.3.0.jar
Including org.osgi.enterprise-4.2.0.jar
Including guava-18.0.jar
Including jackson-annotations-2.5.0.jar
Including waffle-jna-1.7.5.jar
Including jna-4.2.1.jar
Including crate-jdbc-2.3.0.jar
Including jcl-over-slf4j-1.7.12.jar
Including jna-platform-4.2.1.jar
Including org.osgi.core-4.3.1.jar
Dropping LICENSE
Dropping OSGI-OPT/bnd.bnd
Dropping about.html
Including jackson-databind-2.5.3.jar
Created /.../metabase/crate-driver/target/uberjar/crate.metabase-driver.jar

GregDerderian commented 4 years ago

sorry @shm-dmitry, I'm confused but it is still not working. I know nothing about java which may explain why...

My behavior so far was to wget metabase.jar and to run java -jar metabase.jar, and then to add the crate driver jar file into metabase/plugins.

With your full instruction, I have the full source for metabase, and I have the crate driver jar updated with your fix, but I still don't know how to run metabase and I don't see any "plugins" directory where to copy the crate driver jar file...

thanks again for your help !

shm-dmitry commented 4 years ago

@GregDerderian

Hm..

I run metabase in docker, I mount local plugins folder to /plugins folder in docker, and its works (as described here).

I put 2 files in plugins folder:

official crate JDBC driver: crate-jdbc-2.6.0.jar
builded metabase driver: crate.metabase-driver.jar

In your case (bases on sample) you can try here:

create metabase folder:
```
cd /opt
mkdir metabase
cd metabase
```
put metabase.jar into this folder:
```
cp ......../metabase.jar .
```

create plugins folder and save drivers into it

mkdir plugins
cp ........./crate-jdbc-2.6.0.jar plugins/
cp ......../crate.metabase-driver.jar plugins/

At this moment you have this files/folders on disk:

/opt/metabase/
/opt/metabase/metabase.jar
/opt/metabase/plugins/
/opt/metabase/plugins/crate-jdbc-2.6.0.jar
/opt/metabase/plugins/crate.metabase-driver.jar

Go to root folder and start metabase

cd /opt/metabase/
java -jar metabase.jar

Can you copy-paste metabase output log here?

GregDerderian commented 4 years ago

Thanks @shm-dmitry

Actually I didn't use the crate-jdbc-2.6.0.jar file before. To do so, I downloaded crate-jdbc-standalone-2.6.0.jar file from here and I renamed it crate-jdbc-2.6.0.jar

Then, I created this files/folders :

/metabase/metabase/metabase.jar
/metabase/metabase/plugins/crate-jdbc-2.6.0.jar
/metabase/metabase/plugins/crate.metabase-driver.jar
/metabase/crate-driver/ <your repo>
/metabase/crate-jdbc/<where I downloaded crate-jdbc-standalone and renamed it>

From your repo, I just cloned it, then I ran the 2 lein commands, from your comment above.

I am pasting below the output log of runningjava -jar metabase.jar in /metabase/metabase/.

After running metabase, I can access it through its portal and I can select "crateDB" in the Database option, but when I test the connexion, I got an error No suitable driver found for jdbc:crate://<my_cratedb_ip>:<port>

~/projects/metabase/metabase$ java -jar metabase.jar
03-26 14:20:34 DEBUG plugins.classloader :: Using NEWLY CREATED classloader as shared context classloader: clojure.lang.DynamicClassLoader@55caeb35
03-26 14:20:34 INFO metabase.util :: Loading Metabase...
03-26 14:20:34 INFO metabase.util :: Maximum memory available to JVM: 439.5 MB
03-26 14:20:52 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
03-26 14:21:00 INFO metabase.core :: Starting Metabase in STANDALONE mode
03-26 14:21:00 INFO metabase.server :: Launching Embedded Jetty Webserver with config:
 {:port 3000}

03-26 14:21:00 INFO metabase.core :: Starting Metabase version v0.34.3 (1a83edb release-0.34.x) ...
03-26 14:21:00 INFO metabase.core :: System info:
 {"java.runtime.name" "OpenJDK Runtime Environment",
 "java.runtime.version" "1.8.0_121-b15",
 "java.vendor" "Azul Systems, Inc.",
 "java.vendor.url" "http://www.azulsystems.com/",
 "java.version" "1.8.0_121",
 "java.vm.name" "OpenJDK 64-Bit Server VM",
 "java.vm.version" "25.121-b15",
 "os.name" "Linux",
 "os.version" "4.4.0-1104-aws",
 "user.language" "en",
 "user.timezone" "Etc/UTC"}

03-26 14:21:00 INFO metabase.plugins :: Loading plugins in /home/ubuntu/projects/metabase/metabase/plugins...
(... iextracting plugins file from modules ...)
03-26 14:21:03 INFO plugins.classloader :: Added URL file:/home/ubuntu/projects/metabase/metabase/plugins/crate-jdbc-2.6.0.jar to classpath
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :presto...
03-26 14:21:03 INFO driver.impl :: Registered abstract driver :sql  🚚
Load driver :sql took 176.3 ms
03-26 14:21:03 INFO driver.impl :: Registered driver :presto (parents: [:sql]) 🚚
03-26 14:21:03 INFO plugins.dependencies :: Plugin 'Metabase BigQuery Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
03-26 14:21:03 INFO plugins.dependencies :: Metabase BigQuery Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? false
03-26 14:21:03 INFO plugins.dependencies :: Plugins with unsatisfied deps: ["Metabase BigQuery Driver"]
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :sqlite...
03-26 14:21:03 INFO driver.impl :: Registered abstract driver :sql-jdbc (parents: [:sql]) 🚚
Load driver :sql-jdbc took 66.2 ms
03-26 14:21:03 INFO driver.impl :: Registered driver :sqlite (parents: [:sql-jdbc]) 🚚
03-26 14:21:03 INFO plugins.dependencies :: Metabase cannot initialize plugin Metabase Oracle Driver due to required dependencies. Metabase requires the Oracle JDBC driver in order to connect to Oracle databases, but we can't ship
 it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/oracle.html for more details.

03-26 14:21:03 INFO plugins.dependencies :: Metabase Oracle Driver dependency {:class oracle.jdbc.OracleDriver} satisfied? false
03-26 14:21:03 INFO plugins.dependencies :: Plugins with unsatisfied deps: ["Metabase BigQuery Driver" "Metabase Oracle Driver"]
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :redshift...
03-26 14:21:03 INFO driver.impl :: Registered driver :postgres (parents: [:sql-jdbc]) 🚚
Load driver :postgres took 30.5 ms
03-26 14:21:03 INFO driver.impl :: Registered driver :redshift (parents: [:postgres]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :hive-like...
03-26 14:21:03 INFO driver.impl :: Registered abstract driver :hive-like (parents: [:sql-jdbc]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :sparksql...
03-26 14:21:03 INFO driver.impl :: Registered driver :sparksql (parents: [:hive-like]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :snowflake...
03-26 14:21:03 INFO driver.impl :: Registered driver :snowflake (parents: [:sql-jdbc]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :sqlserver...
03-26 14:21:03 INFO driver.impl :: Registered driver :sqlserver (parents: [:sql-jdbc]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :crate...
03-26 14:21:03 INFO driver.impl :: Registered driver :crate (parents: [:sql-jdbc]) 🚚
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :mongo...
03-26 14:21:03 INFO driver.impl :: Registered driver :mongo  🚚
03-26 14:21:03 INFO plugins.dependencies :: Plugin 'Metabase Google Analytics Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
03-26 14:21:03 INFO plugins.dependencies :: Metabase Google Analytics Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? false
03-26 14:21:03 INFO plugins.dependencies :: Plugins with unsatisfied deps: ["Metabase Google Analytics Driver" "Metabase BigQuery Driver" "Metabase Oracle Driver"]
03-26 14:21:03 INFO plugins.dependencies :: Metabase cannot initialize plugin Metabase Vertica Driver due to required dependencies. Metabase requires the Vertica JDBC driver in order to connect to Vertica databases, but we can't s
hip it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/vertica.html for more details.

03-26 14:21:03 INFO plugins.dependencies :: Metabase Vertica Driver dependency {:class com.vertica.jdbc.Driver} satisfied? false
03-26 14:21:03 INFO plugins.dependencies :: Plugins with unsatisfied deps: ["Metabase Google Analytics Driver" "Metabase Vertica Driver" "Metabase BigQuery Driver" "Metabase Oracle Driver"]
03-26 14:21:03 DEBUG plugins.lazy-loaded-driver :: Registering lazy loading driver :druid...
03-26 14:21:03 INFO driver.impl :: Registered driver :druid  🚚
(... other database driver installation ...)
03-26 14:21:03 INFO metabase.core :: Setting up and migrating Metabase DB. Please sit tight, this may take a minute...
03-26 14:21:03 WARN metabase.db :: WARNING: Using Metabase with an H2 application database is not recommended for production deployments. For production deployments, we highly recommend using Postgres, MySQL, or MariaDB instead. I
f you decide to continue to use H2, please be sure to back up the database file regularly. For more information, seehttps://metabase.com/docs/latest/operations-guide/migrating-from-h2.html
03-26 14:21:03 INFO metabase.db :: Verifying h2 Database Connection ...
03-26 14:21:03 INFO driver.impl :: Initializing driver :sql...
03-26 14:21:03 INFO driver.impl :: Initializing driver :sql-jdbc...
03-26 14:21:03 INFO driver.impl :: Initializing driver :h2...
03-26 14:21:03 INFO metabase.db :: Successfully verified H2 1.4.197 (2018-03-18) application database connection. ✅
03-26 14:21:03 INFO metabase.db :: Running Database Migrations...
03-26 14:21:03 INFO metabase.db :: Setting up Liquibase...
03-26 14:21:04 INFO metabase.db :: Liquibase is ready.
03-26 14:21:04 INFO db.liquibase :: Checking if Database has unrun migrations...
03-26 14:21:07 INFO db.liquibase :: Database has unrun migrations. Waiting for migration lock to be cleared...
03-26 14:21:07 INFO db.liquibase :: Migration lock is cleared. Running migrations...
03-26 14:21:09 INFO metabase.db :: Database Migrations Current ...  ✅
03-26 14:21:09 INFO db.migrations :: Running all necessary data migrations, this may take a minute.
03-26 14:21:09 INFO db.migrations :: Running data migration 'add-users-to-default-permissions-groups'...
03-26 14:21:09 INFO models.permissions-group :: Created magic permissions group 'All Users' (ID = 1)
03-26 14:21:09 INFO models.permissions-group :: Created magic permissions group 'Administrators' (ID = 2)
03-26 14:21:09 INFO db.migrations :: Running data migration 'add-admin-group-root-entry'...
03-26 14:21:09 INFO models.permissions-group :: Created magic permissions group 'MetaBot' (ID = 3)
03-26 14:21:09 INFO db.migrations :: Running data migration 'add-databases-to-magic-permissions-groups'...
03-26 14:21:09 INFO db.migrations :: Running data migration 'migrate-field-types'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'fix-invalid-field-types'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'copy-site-url-setting-and-remove-trailing-slashes'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'drop-old-query-execution-table'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'ensure-protocol-specified-in-site-url'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'populate-card-database-id'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'migrate-humanization-setting'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'mark-category-fields-as-list'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'add-legacy-sql-directive-to-bigquery-sql-cards'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'clear-ldap-user-local-passwords'...
03-26 14:21:10 INFO db.migrations :: Running data migration 'add-migrated-collections'...
03-26 14:21:10 INFO db.migrations :: Finished running data migrations.
Database setup took 6.5 s
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.activity-feed 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.dependencies 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.driver-notifications 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.last-login 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.metabot-lifecycle 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.notifications 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.revision 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.sync-database 👂
03-26 14:21:10 INFO metabase.events :: Starting events listener: metabase.events.view-log 👂
03-26 14:21:10 INFO metabase.task :: Initializing task SyncDatabases 📆
03-26 14:21:10 INFO metabase.task :: Initializing task CheckForNewVersions 📆
03-26 14:21:10 INFO metabase.task :: Initializing task SendAnonymousUsageStats 📆
03-26 14:21:10 INFO metabase.task :: Initializing task SendAbandomentEmails 📆
03-26 14:21:10 INFO metabase.task :: Initializing task SendPulses 📆
03-26 14:21:10 INFO metabase.task :: Initializing task SendFollowUpEmails 📆
03-26 14:21:10 INFO metabase.task :: Initializing task TaskHistoryCleanup 📆
03-26 14:21:10 INFO metabase.core :: Looks like this is a new installation ... preparing setup wizard
03-26 14:21:10 INFO metabase.core :: Please use the following URL to setup your Metabase installation:

http://localhost:3000/setup/

03-26 14:21:10 INFO metabase.sample-data :: Loading sample dataset...
(... install sample data ...)

shm-dmitry commented 4 years ago

@GregDerderian

execute this:

test@test:/opt$ mkdir metabase
test@test:/opt$ cd metabase/
test@test:/opt/metabase$ wget https://downloads.metabase.com/v0.34.3/metabase.jar
--2020-03-27 09:48:12--  https://downloads.metabase.com/v0.34.3/metabase.jar
......
2020-03-27 09:48:20 (24,2 MB/s) - «metabase.jar» сохранён [194468850/194468850]
test@test:/opt/metabase$ mkdir plugins
test@test:/opt/metabase$ cd plugins/
test@test:/opt/metabase/plugins$ wget https://dl.bintray.com/crate/crate/io/crate/crate-jdbc/2.6.0/crate-jdbc-2.6.0.jar
--2020-03-27 09:49:04--  https://dl.bintray.com/crate/crate/io/crate/crate-jdbc/2.6.0/crate-jdbc-2.6.0.jar
........
2020-03-27 09:49:06 (848 KB/s) - «crate-jdbc-2.6.0.jar» сохранён [766401/766401]                                                                                                                                                                   
test@test:/opt/metabase/plugins$ cp /home/test/metabase/crate-driver/target/uberjar/crate.metabase-driver.jar .                                                                                                                      
test@test:/opt/metabase/plugins$ cd ..                                                                                                                                                                                                   
test@test:/opt/metabase$ java -jar metabase.jar 
03-27 09:49:44 DEBUG plugins.classloader :: Использование НОВОГО СОЗДАННОГО загрузчика классов как общего загрузчика классов: clojure.lang.DynamicClassLoader@2cd62003                                                                             
......
03-27 09:50:05 INFO metabase.core :: Пожалуйста используйте данную ссылку для установки вашей инсталляции Metabase:

http://localhost:3000/setup/                                                                                                                                                                                                                       

03-27 09:50:05 INFO metabase.sample-data :: Загрузка тестовых данных...
.....

After metabase starts - add new database. Database type: CrateDB Database Host: YOUR_HOST:5432/ (WARNING! / at the end of string is mandatory!)

GregDerderian commented 4 years ago

@shm-dmitry Unfortunately, the final step is not working. FYI, my cratedb is hosted on an EC2 instance (ie the host is <ec2_ip>:<custom_port>)/).

When I enter this host and I click on save, I got an error : TimeOut after 5sec. If I check This is a large database, so let me choose when Metabase syncs and scans. and I click on next, I got another error : Unable to connect to the database. Please check the connection settings..

If I try to connect to crate admin panel from my internet browser, through http://<ec2_ip>:<customer_port>/, it asked me for my crendetials (login/password), and I got access to the panel. Also, if I tried to access it through the terminal with something like curl <ec2_ip>:<customer_port> I got an answer (saying that password authentication failed for user "crate" which mean that the connection is working)...

I don't know if the problem is the connexion to cratedb or if it is the installation of metabase and crate driver... :(

once again, thanks a lot for your time here !!

GregDerderian commented 4 years ago

@shm-dmitry is the possible issue that it is not a local database but a distant one, with username and password ? CrateDB plugins doesn't allow to add username and password right now

shm-dmitry commented 4 years ago

@GregDerderian , I dont know, how to pass password in URL Here no information about it: https://crate.io/docs/clients/jdbc/en/latest/connect.html#database-connection-urls

GregDerderian commented 4 years ago

@shm-dmitry usually, you can pass it into the url like in psql (username:password@host:port) but for crate it is not working.

GregDerderian commented 4 years ago

We found a solution (not the right one but it works), and I posted a related issue #6 when a password is needed.

Thanks a lot @shm-dmitry for your intense help !

metabase / crate-driver

Create Database Error #5