Expand SQL Script with Data Filter function

Hi all im new here

i have a running sql script for my database.
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM
(SELECT
pps_tester_data.Mac Address AS Mac Address,
SUM(IF((pps_tester_data.Test Status = ‘PASS’), 1, 0)) AS passes
FROM
pps_tester_data
GROUP BY pps_tester_data.Mac Address
HAVING (passes = 0)) result

this will run in metabase too.
But for my dashboard i have added a date filter.
now how to combine the SQL script and filter ?
i tryed many ways but without success.

i think the filter must be sonething like this:
WHERE (pps_tester_data.Test Date >= convert_tz(‘2020-01-01 00:00:00.000’, ‘Europe/Berlin’, @@session.time_zone)
AND pps_tester_data.Test Date < convert_tz(‘2025-01-02 00:00:00.000’, ‘Europe/Berlin’, @@session.time_zone))

but where to add ?

Thanks a lot
Markus

Hi @STT_MAE
I would highly recommend that you read these two articles:
https://www.metabase.com/blog/sql-template-variables/index.html
https://www.metabase.com/blog/field-filters/index.html

Ok maybe someone can help me create a question for the problem.

i have a short test table with „Test Status“ (Pass/Fail) and „Mac Address“

FAIL D7:8F:C1:71:E8:BB
FAIL D7:8F:C1:71:E8:BB
PASS D7:8F:C1:71:E8:BB

PASS DA:BF:24:F3:28:96
PASS DA:BF:24:F3:28:96

FAIL F2:24:E6:1F:05:1E
FAIL F2:24:E6:1F:05:1E

FAIL CB:A9:37:9C:FD:08

now i want to make a question to count all data who only have Fail (one or more times) in combination with distinct Mac Address

FAIL D7:8F:C1:71:E8:BB
FAIL D7:8F:C1:71:E8:BB
PASS D7:8F:C1:71:E8:BB
= 0 (because of PASS and FAIL with the same Mac Address)

PASS DA:BF:24:F3:28:96
PASS DA:BF:24:F3:28:96
= 0 (because only PASS)

FAIL F2:24:E6:1F:05:1E
FAIL F2:24:E6:1F:05:1E
= 1 (because same Mac Address)

FAIL CB:A9:37:9C:FD:08
= 1

The problem is when the Mac Address has PASS and FAIL then to count 0.
Counting only passes or only fails are no problem.

can someone help me with this please

Thanks
Markus

@STT_MAE I don’t understand. Can you show a screenshot of how you are trying to use the Notebook editor to create this?

HI

i have this SQL script working in MYSQL Workbench. (This Screenshot is with more datasets)

Now I want to do the same as a question in Metabase and add that question to a dashboard with filters.

Thanks
Markus

Yes i did it!

@flamber thanks for the links !

my result is:
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM
(SELECT
pps_tester_data.Mac Address AS Mac Address,
pps_tester_data.Test Date AS Test Date,
SUM(IF((pps_tester_data.Test Status = ‘PASS’), 1, 0)) AS passes
FROM
pps_tester_data
where pps_tester_data.Mac Address <> ‘’
[[and {{category}}]]

    GROUP BY `pps_tester_data`.`Mac Address`
    HAVING (passes = 0)) result;

thanks
Markus

Hello again

ok this works all well until i enable the Embedding of this Dashboard.
if no Date filter is activated all is OK

But if i set a date range then the Total PCBA failed do not show anything.
this is in the embedded Dashboard only.

hmm maybe i have to do this question with a Metabase question instead of an SQL Script ?

Thanks
Markus

@STT_MAE
Post “Diagnostic Info” from Admin > Troubleshooting.
Post the detailed error, when you see the error on embedding - Admin > Troubleshooting > Logs.
And post the query for the question that is erroring.

@flamber

i only copied the one time segment where i produced the error

it looks like it has something to do with the wrong time zone.
But i think i set it right in the Admin settings.

image

[e8c104c9-78f6-40c4-90a1-c8c5e41507fc] 2020-12-14T13:08:12+01:00 ERROR metabase.driver.sql-jdbc.execute Zeitzone Europe/Berlin konnte nicht für Datenbank :mysql gesetzt werden.

java.sql.SQLTransientConnectionException: (conn=112) Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)

at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)

at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:501)

at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invokeStatic(execute.clj:127)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invoke(execute.clj:112)

at metabase.driver.sql_jdbc.execute$fn__76511.invokeStatic(execute.clj:159)

at metabase.driver.sql_jdbc.execute$fn__76511.invoke(execute.clj:154)

at clojure.lang.MultiFn.invoke(MultiFn.java:239)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:388)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:385)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc$fn__78126.invokeStatic(sql_jdbc.clj:49)

at metabase.driver.sql_jdbc$fn__78126.invoke(sql_jdbc.clj:47)

at clojure.lang.MultiFn.invoke(MultiFn.java:244)

at metabase.query_processor.context$executef.invokeStatic(context.clj:59)

at metabase.query_processor.context$executef.invoke(context.clj:48)

at metabase.query_processor.context.default$default_runf.invokeStatic(default.clj:69)

at metabase.query_processor.context.default$default_runf.invoke(default.clj:67)

at metabase.query_processor.context$runf.invokeStatic(context.clj:45)

at metabase.query_processor.context$runf.invoke(context.clj:39)

at metabase.query_processor.reducible$pivot.invokeStatic(reducible.clj:34)

at metabase.query_processor.reducible$pivot.invoke(reducible.clj:31)

at metabase.query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:26)

at metabase.query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)

at metabase.query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)

at metabase.query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)

at metabase.query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)

at metabase.query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)

at metabase.query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)

at metabase.query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)

at metabase.query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)

at metabase.query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)

at metabase.query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)

at metabase.query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)

at metabase.query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)

at metabase.query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)

at metabase.query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)

at metabase.query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)

at metabase.query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)

at metabase.query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)

at metabase.query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)

at metabase.query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)

at metabase.query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)

at metabase.query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)

at metabase.query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)

at metabase.query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)

at metabase.query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)

at metabase.query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)

at metabase.query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)

at metabase.query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)

at metabase.query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)

at metabase.driver$do_with_driver.invokeStatic(driver.clj:61)

at metabase.driver$do_with_driver.invoke(driver.clj:57)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)

at metabase.query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)

at metabase.query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)

at metabase.query_processor.store$do_with_store.invokeStatic(store.clj:46)

at metabase.query_processor.store$do_with_store.invoke(store.clj:40)

at metabase.query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)

at metabase.query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)

at metabase.query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)

at metabase.query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)

at metabase.query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)

at metabase.query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)

at metabase.query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)

at metabase.query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)

at metabase.query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$fn__39830.invoke(reducible.clj:108)

at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

at clojure.lang.AFn.call(AFn.java:18)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)

… 76 more

Caused by: java.sql.SQLException: Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1688)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1550)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1513)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)

… 77 more

the log goes on but there is too much text for one post.
Do you need more of the log ?

Thanks
Markus

@STT_MAE No, that’s a different error caused by your MySQL database not having tzdata:
https://dba.stackexchange.com/questions/120945/how-do-i-resolve-this-error-error-1298-hy000-unknown-or-incorrect-time-zone

@flamber
OK
if it’s not the timezone error, what is causing my error?
do you need more of the log ?

thanks for your time and effort

Markus

@STT_MAE I need to three things I asked for previously.

@flamber
Oh I missed that…

Diagnostic Info:
{
“browser-info”: {
“language”: “de-AT”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.9+11”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.9”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.9+11”,
“os.name”: “Windows Server 2019”,
“os.version”: “10.0”,
“user.language”: “de”,
“user.timezone”: “Europe/Berlin”
},
“metabase-info”: {
“databases”: [
“h2”,
“mysql”
],
“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”: “2020-11-16”,
“tag”: “v0.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: “Europe/Berlin”
}
}
}

The Query:
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM

    (SELECT 
        `pps_tester_data`.`Mac Address` AS `Mac Address`,
        `pps_tester_data`.`Test Date` AS `Test Date`,
            SUM(IF((`pps_tester_data`.`Test Status` = 'PASS'), 1, 0)) AS `passes`
    FROM
        `pps_tester_data`
        where `pps_tester_data`.`Mac Address` <> ''
        [[and {{category}}]]

    GROUP BY `pps_tester_data`.`Mac Address`
    HAVING (passes = 0)) result;

the log is very big.
do you need more?
[e8c104c9-78f6-40c4-90a1-c8c5e41507fc] 2020-12-14T13:08:12+01:00 ERROR metabase.driver.sql-jdbc.execute Zeitzone Europe/Berlin konnte nicht für Datenbank :mysql gesetzt werden.

java.sql.SQLTransientConnectionException: (conn=112) Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)

at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)

at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:501)

at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invokeStatic(execute.clj:127)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invoke(execute.clj:112)

at metabase.driver.sql_jdbc.execute$fn__76511.invokeStatic(execute.clj:159)

at metabase.driver.sql_jdbc.execute$fn__76511.invoke(execute.clj:154)

at clojure.lang.MultiFn.invoke(MultiFn.java:239)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:388)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:385)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc$fn__78126.invokeStatic(sql_jdbc.clj:49)

at metabase.driver.sql_jdbc$fn__78126.invoke(sql_jdbc.clj:47)

at clojure.lang.MultiFn.invoke(MultiFn.java:244)

at metabase.query_processor.context$executef.invokeStatic(context.clj:59)

at metabase.query_processor.context$executef.invoke(context.clj:48)

at metabase.query_processor.context.default$default_runf.invokeStatic(default.clj:69)

at metabase.query_processor.context.default$default_runf.invoke(default.clj:67)

at metabase.query_processor.context$runf.invokeStatic(context.clj:45)

at metabase.query_processor.context$runf.invoke(context.clj:39)

at metabase.query_processor.reducible$pivot.invokeStatic(reducible.clj:34)

at metabase.query_processor.reducible$pivot.invoke(reducible.clj:31)

at metabase.query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:26)

at metabase.query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)

at metabase.query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)

at metabase.query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)

at metabase.query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)

at metabase.query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)

at metabase.query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)

at metabase.query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)

at metabase.query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)

at metabase.query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)

at metabase.query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)

at metabase.query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)

at metabase.query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)

at metabase.query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)

at metabase.query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)

at metabase.query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)

at metabase.query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)

at metabase.query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)

at metabase.query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)

at metabase.query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)

at metabase.query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)

at metabase.query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)

at metabase.query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)

at metabase.query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)

at metabase.query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)

at metabase.query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)

at metabase.query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)

at metabase.query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)

at metabase.query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)

at metabase.driver$do_with_driver.invokeStatic(driver.clj:61)

at metabase.driver$do_with_driver.invoke(driver.clj:57)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)

at metabase.query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)

at metabase.query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)

at metabase.query_processor.store$do_with_store.invokeStatic(store.clj:46)

at metabase.query_processor.store$do_with_store.invoke(store.clj:40)

at metabase.query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)

at metabase.query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)

at metabase.query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)

at metabase.query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)

at metabase.query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)

at metabase.query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)

at metabase.query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)

at metabase.query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)

at metabase.query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$fn__39830.invoke(reducible.clj:108)

at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

at clojure.lang.AFn.call(AFn.java:18)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)

… 76 more

Caused by: java.sql.SQLException: Unknown or incorrect time zone: ‘Europe/Berlin’

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1688)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1550)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1513)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)

… 77 more

thanks
Markus

@STT_MAE

  1. Migrate away from H2 if you are using Metabase in production:
    https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
  2. I don’t see any date filter variables in your query.
  3. I’m very sure that the timezone error is completely unrelated to this problem. I’m sure you see that error for all your queries until you have added tzdata to your MySQL setup.
    Try removing filters on the dashboard, then save the dashboard, then add the filters again and connect them to the cards, and save the dashboard.

@flamber

but it’s weird that the dashboard in Metabase works perfectly.
It just doesn’t work when I use the embedded link

http://st01app:3000/public/dashboard/5b617fc7-b6fa-4b36-b203-da6b2dfe6e51

Data is in august / September / october

i use the MYSQL Database

@STT_MAE You are not providing enough information to be able to help you - have a look here:
https://github.com/metabase/metabase/issues/14058

@flamber

Ok after deleting the filter in the dashboard and then recreating it.
it worked in Metabase and in the embeded Dashboard

Thanks
Markus