search

evosec / metabase-firebird-driver

A plugin that allows Metabase to connect to FirebirdSQL databases.
https://github.com/metabase/metabase/
MIT License
28 stars 7 forks source link

Compatibility issue with Metabase 0.34.x #3

Closed kumajaya closed 4 years ago

kumajaya commented 4 years ago

Timezone codes update that started from Metabase 3.4.0 make this Firebird driver raised error for time range (before, after, on, between).

Nikos410 commented 4 years ago

Hey there @kumajaya, unfortunately I cannot reproduce your issue with our databases. Can you provide a little more information? (Firebird Database version, what the query looks like, what the column you are filtering looks like, ...)

kumajaya commented 4 years ago

Sorry, I quickly back to Metabase 0.33.7.3 and have no access to the server for now. Using Firebird 2.5.8 on Windows 10, maybe I have to update it to 2.5.9.

Nikos410 commented 4 years ago

Hi @kumajaya , we have never tested this driver with a Firebird 2.x database, we are only using Firebird 3.x. It is possible that this driver is incompatible with Firebird 2.

kumajaya commented 4 years ago

Yeah, Metabase error message "Error converting to object" persists even after upgrading Firebird from 2.5.8 to 2.5.9. I believe Metabase sql command "timestamp with time zone '2020-03-01 00:00:00.000+07:00'" trigger the problem. Any idea how to transform the sql command to "timestamp '2020-03-01 00:00:00.000" in Firebird driver level?

kumajaya commented 4 years ago

I can't change the third party client application but it seems the most possible solution is to upgrade the database to Firebird 3 and enable all Firebird 3 server legacy support.

kumajaya commented 4 years ago

Backup my database using gbak from Firebird 2.5.9 and then restore it using gbak from Firebird 3.0.5, run Firebird 3.0.5 server but still no luck :( Database accessible from application (Firebird 2.x client), FlameRobin, and DBeaver without problem.

Diagnostic Info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "12.0.1+12",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "https://java.oracle.com/",
    "java.version": "12.0.1",
    "java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
    "java.vm.version": "12.0.1+12",
    "os.name": "Windows 10",
    "os.version": "10.0",
    "user.language": "en",
    "user.timezone": "Asia/Bangkok"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "firebird",
      "mysql",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "8.0.16"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.5.1"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-02-25",
      "tag": "v0.34.3",
      "branch": "release-0.34.x",
      "hash": "1a83edb"
    },
    "settings": {
      "report-timezone": "Asia/Bangkok"
    }
  }
}

Logs:

:query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:userland-query? true},
:info
{:executed-by 1,
:context :question,
:card-id 93,
:dashboard-id nil,
:query-hash [-28, 106, 10, -81, -5, 8, 69, -127, -87, -46, -125, -98, -39, 48, 48, -2, 124, 5, 6, -78, 2, -14, -27, 15, -52, -64, -117, 85, 17, 120, 60, 66]},
:query
{:source-table 34,
:filter [:!= [:field-id 247] "N/A" "H2O2"],
:fields
[[:field-id 245]
[:field-id 246]
[:field-id 243]
[:field-id 247]
[:field-id 239]
[:field-id 298]
[:field-id 238]
[:field-id 240]
[:field-id 244]
[:field-id 249]
[:field-id 242]
[:field-id 269]
[:field-id 278]
[:field-id 266]
[:field-id 265]
[:field-id 330]],
:order-by [[:desc [:field-id 247]] [:desc [:field-id 246]] [:desc [:field-id 245]]]},
:parameters [{:type "date/all-options", :target ["dimension" ["field-id" 242]], :value "2020-03-01~"} {:type "category", :target ["dimension" ["field-id" 241]], :value ["BEKASI"]}],
:async? true,
:cache-ttl nil},
:preprocessed
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:userland-query? true},
:info
{:executed-by 1,
:context :question,
:card-id 93,
:query-hash [-28, 106, 10, -81, -5, 8, 69, -127, -87, -46, -125, -98, -39, 48, 48, -2, 124, 5, 6, -78, 2, -14, -27, 15, -52, -64, -117, 85, 17, 120, 60, 66]},
:preprocessing-level 1,
:database 2,
:query
{:source-table 34,
:filter
[:and
[:!= [:field-id 247] [:value "N/A" {:base_type :type/Text, :special_type :type/Product, :database_type "VARCHAR"}]]
[:!= [:field-id 247] [:value "H2O2" {:base_type :type/Text, :special_type :type/Product, :database_type "VARCHAR"}]]
[:>= [:datetime-field [:field-id 242] :default] [:absolute-datetime (t/zoned-date-time "2020-03-02T00:00+07:00[Asia/Bangkok]") :default]]
[:= [:field-id 241] [:value "BEKASI" {:base_type :type/Text, :special_type :type/Category, :database_type "VARCHAR"}]]],
:fields
[[:field-id 245]
[:field-id 246]
[:field-id 243]
[:field-id 247]
[:field-id 239]
[:field-id 298]
[:field-id 238]
[:field-id 240]
[:field-id 244]
[:field-id 249]
[:field-id 242]
[:field-id 269]
[:field-id 278]
[:field-id 266]
[:field-id 265]
[:field-id 330]],
:order-by [[:desc [:field-id 247]] [:desc [:field-id 246]] [:desc [:field-id 245]]],
:limit 2000}},
:native
{:query
"SELECT FIRST 2000 \"TBLWEIGHTSVIEW\".\"TICKET\" AS \"TICKET\", \"TBLWEIGHTSVIEW\".\"TRUCK\" AS \"TRUCK\", \"TBLWEIGHTSVIEW\".\"DRIVER\" AS \"DRIVER\", \"TBLWEIGHTSVIEW\".\"PRODUCT\" AS \"PRODUCT\", \"TBLWEIGHTSVIEW\".\"COMPANY\" AS \"COMPANY\", \"TBLWEIGHTSVIEW\".\"COMPANY2\" AS \"COMPANY2\", \"TBLWEIGHTSVIEW\".\"NETWEIGHT\" AS \"NETWEIGHT\", \"TBLWEIGHTSVIEW\".\"FIRSTWEIGHT\" AS \"FIRSTWEIGHT\", \"TBLWEIGHTSVIEW\".\"SECONDWEIGHT\" AS \"SECONDWEIGHT\", \"TBLWEIGHTSVIEW\".\"FIRSTWEIGHTDATETIME\" AS \"FIRSTWEIGHTDATETIME\", \"TBLWEIGHTSVIEW\".\"SECONDWEIGHTDATETIME\" AS \"SECONDWEIGHTDATETIME\", \"TBLWEIGHTSVIEW\".\"EFFICIENCY\" AS \"EFFICIENCY\", \"TBLWEIGHTSVIEW\".\"ELAPSEDHOURS\" AS \"ELAPSEDHOURS\", \"TBLWEIGHTSVIEW\".\"TAKE\" AS \"TAKE\", \"TBLWEIGHTSVIEW\".\"CLOSED\" AS \"CLOSED\", \"TBLWEIGHTSVIEW\".\"LOSSES\" AS \"LOSSES\" FROM \"TBLWEIGHTSVIEW\" WHERE (\"TBLWEIGHTSVIEW\".\"PRODUCT\" <> ? AND \"TBLWEIGHTSVIEW\".\"PRODUCT\" <> ? AND \"TBLWEIGHTSVIEW\".\"SECONDWEIGHTDATETIME\" >= ? AND \"TBLWEIGHTSVIEW\".\"WAREHOUSE\" = ?) ORDER BY \"TBLWEIGHTSVIEW\".\"PRODUCT\" DESC, \"TBLWEIGHTSVIEW\".\"TRUCK\" DESC, \"TBLWEIGHTSVIEW\".\"TICKET\" DESC ",
:params ("N/A" "H2O2" (t/zoned-date-time "2020-03-02T00:00+07:00[Asia/Bangkok]") "BEKASI")}}

[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:34+07:00 DEBUG metabase.middleware.log POST /api/card/105/query 202 [ASYNC: completed] 898.6 ms (39 DB calls) Jetty threads: 3/50 (5 idle, 0 queued) (87 total active threads) Queries in flight: 2
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:34+07:00 DEBUG metabase.middleware.log POST /api/card/95/query 202 [ASYNC: completed] 1.2 s (38 DB calls) Jetty threads: 3/50 (5 idle, 0 queued) (87 total active threads) Queries in flight: 1
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:34+07:00 DEBUG metabase.middleware.log POST /api/card/93/query 202 [ASYNC: completed] 1.2 s (35 DB calls) Jetty threads: 3/50 (5 idle, 0 queued) (87 total active threads) Queries in flight: 0
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:40+07:00 DEBUG metabase.middleware.log GET /api/card/93 200 15.5 ms (6 DB calls) Jetty threads: 5/50 (5 idle, 0 queued) (87 total active threads) Queries in flight: 0
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:40+07:00 DEBUG metabase.middleware.log GET /api/database 200 64.3 ms (8 DB calls) Jetty threads: 4/50 (5 idle, 0 queued) (87 total active threads) Queries in flight: 0
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:41+07:00 ERROR metabase.driver.sql-jdbc.execute nil
[ef099566-64b9-4190-8f0b-1cfd53906402] 2020-03-13T13:02:41+07:00 WARN metabase.query-processor.middleware.process-userland-query Query failure {:status :failed,
:class org.firebirdsql.jdbc.field.TypeConversionException,
:error "Error converting to object.",
:stacktrace
("org.firebirdsql.jdbc.field.FBField.setObject(FBField.java:671)"
"org.firebirdsql.jdbc.AbstractPreparedStatement.setObject(AbstractPreparedStatement.java:393)"
"org.firebirdsql.jdbc.AbstractPreparedStatement.setObject(AbstractPreparedStatement.java:712)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:1034)"
"--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:140)"
"driver.sql_jdbc.execute$set_object.invoke(execute.clj:132)"
"driver.sql_jdbc.execute$fn__69345.invokeStatic(execute.clj:164)"
"driver.sql_jdbc.execute$fn__69345.invoke(execute.clj:162)"
"driver.sql_jdbc.execute$fn__69351.invokeStatic(execute.clj:179)"
"driver.sql_jdbc.execute$fn__69351.invoke(execute.clj:177)"
"driver.sql_jdbc.execute$set_parameters.invokeStatic(execute.clj:191)"
"driver.sql_jdbc.execute$set_parameters.invoke(execute.clj:181)"
"driver.sql_jdbc.execute$cancelable_run_query.invokeStatic(execute.clj:209)"
"driver.sql_jdbc.execute$cancelable_run_query.invoke(execute.clj:198)"
"driver.sql_jdbc.execute$run_query.invokeStatic(execute.clj:223)"
"driver.sql_jdbc.execute$run_query.invoke(execute.clj:219)"
"driver.sql_jdbc.execute$do_with_auto_commit_disabled.invokeStatic(execute.clj:272)"
"driver.sql_jdbc.execute$do_with_auto_commit_disabled.invoke(execute.clj:260)"
"driver.sql_jdbc.execute$do_in_transaction$fn__69387.invoke(execute.clj:277)"
"driver.sql_jdbc.execute$do_in_transaction.invokeStatic(execute.clj:276)"
"driver.sql_jdbc.execute$do_in_transaction.invoke(execute.clj:275)"
"driver.sql_jdbc.execute$run_query_without_timezone.invokeStatic(execute.clj:300)"
"driver.sql_jdbc.execute$run_query_without_timezone.invoke(execute.clj:299)"
"driver.sql_jdbc.execute$execute_query$fn__69410.invoke(execute.clj:338)"
"driver.sql_jdbc.execute$do_with_try_catch.invokeStatic(execute.clj:252)"
"driver.sql_jdbc.execute$do_with_try_catch.invoke(execute.clj:246)"
"driver.sql_jdbc.execute$execute_query.invokeStatic(execute.clj:332)"
"driver.sql_jdbc.execute$execute_query.invoke(execute.clj:325)"
"driver.sql_jdbc$fn__71316.invokeStatic(sql_jdbc.clj:49)"
"driver.sql_jdbc$fn__71316.invoke(sql_jdbc.clj:47)"
"query_processor$fn__46335$execute_query__46340$fn__46341.invoke(query_processor.clj:69)"
"query_processor$fn__46335$execute_query__46340.invoke(query_processor.clj:64)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__37455.invoke(mbql_to_native.clj:40)"
"query_processor.middleware.annotate$result_rows_maps__GT_vectors$fn__40562.invoke(annotate.clj:541)"
"query_processor.middleware.annotate$add_column_info$fn__40468.invoke(annotate.clj:485)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__41503.invoke(cumulative_aggregations.clj:57)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__43430.invoke(resolve_joins.clj:184)"
"query_processor.middleware.limit$limit$fn__42138.invoke(limit.clj:19)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__46195.invoke(results_metadata.clj:87)"
"query_processor.middleware.format_rows$format_rows$fn__42126.invoke(format_rows.clj:76)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__38215.invoke(add_dimension_projections.clj:234)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__38866.invoke(add_source_metadata.clj:107)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__43480.invoke(resolve_source_table.clj:46)"
"query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__38703.invoke(add_row_count_and_status.clj:16)"
"query_processor.middleware.driver_specific$process_query_in_context$fn__41578.invoke(driver_specific.clj:12)"
"query_processor.middleware.resolve_driver$resolve_driver$fn__43094.invoke(resolve_driver.clj:22)"
"query_processor.middleware.store$initialize_store$fn__46220$fn__46221.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:46)"
"query_processor.store$do_with_store.invoke(store.clj:40)"
"query_processor.middleware.store$initialize_store$fn__46220.invoke(store.clj:10)"
"query_processor.middleware.async$async__GT_sync$fn__37366.invoke(async.clj:23)"
"query_processor.middleware.async_wait$runnable$fn__40619.invoke(async_wait.clj:89)"),
Nikos410 commented 4 years ago

Thanks for your PR! Fixed in e620b3ced25641109d1a7839940a53ad41ae625b