Card visible after multiple reloads

The bar chart does not become visible on the first run and it gives me error data truncated to 200 rows. What is the fix?

Hi @Anum

  1. Post "Diagnostic Info" from Admin > Troubleshooting
  2. Post the full stacktrace from Admin > Troubleshooting > Logs

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/90.0.4430.85 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9-LTS",
"java.vendor": "Amazon.com Inc.",
"java.vendor.url": "https://aws.amazon.com/corretto/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9-LTS",
"os.name": "Linux",
"os.version": "4.14.225-169.362.amzn2.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.34-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"date": "2020-12-03",
"tag": "v0.37.3",
"branch": "release-x.37.x",
"hash": "2f1e783"
},
"settings": {
"report-timezone": null
}
}
}

[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:00+05:00 DEBUG metabase.middleware.log POST /api/card/11/query 202 [ASYNC: completed] 341.2 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 4 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:00+05:00 DEBUG metabase.middleware.log POST /api/card/12/query 202 [ASYNC: completed] 316.9 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 3 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/16/query 202 [ASYNC: completed] 355.2 ms (6 DB calls) App DB connections: 2/15 Jetty threads: 4/50 (3 idle, 0 queued) (115 total active threads) Queries in flight: 2 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/13/query 202 [ASYNC: completed] 318.5 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 4/50 (3 idle, 0 queued) (115 total active threads) Queries in flight: 1 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/17/query 202 [ASYNC: completed] 326.4 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 3/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log GET /api/card/43 200 3.5 ms (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 3 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/42/query 202 [ASYNC: completed] 272.0 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 2 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/41/query 202 [ASYNC: completed] 473.9 ms (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 1 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:01+05:00 DEBUG metabase.middleware.log POST /api/card/15/query 202 [ASYNC: completed] 620.7 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 1 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:46:02+05:00 DEBUG metabase.middleware.log POST /api/card/43/query 202 [ASYNC: completed] 425.6 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (115 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:36+05:00 DEBUG metabase.middleware.log GET /api/database 200 2.4 ms (3 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (108 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:37+05:00 DEBUG metabase.middleware.log GET /api/automagic-dashboards/database/2/candidates 200 118.6 ms (7 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (110 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:39+05:00 DEBUG metabase.middleware.log GET /api/collection/3 200 2.3 ms (3 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (110 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:43+05:00 DEBUG metabase.middleware.log GET /api/dashboard/2 200 17.9 ms (23 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (110 total active threads) Queries in flight: 0 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:44+05:00 DEBUG metabase.middleware.log POST /api/card/22/query 202 [ASYNC: completed] 260.7 ms (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (114 total active threads) Queries in flight: 5 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:44+05:00 DEBUG metabase.middleware.log POST /api/card/21/query 202 [ASYNC: completed] 274.5 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (114 total active threads) Queries in flight: 4 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/26/query 202 [ASYNC: canceled] 6.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 11 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/18/query 202 [ASYNC: canceled] 6.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 10 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/19/query 202 [ASYNC: completed] 242.4 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 3/50 (3 idle, 0 queued) (121 total active threads) Queries in flight: 9 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/20/query 202 [ASYNC: canceled] 7.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (121 total active threads) Queries in flight: 9 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/24/query 202 [ASYNC: canceled] 7.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 8 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/23/query 202 [ASYNC: canceled] 7.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 7 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:50+05:00 DEBUG metabase.middleware.log POST /api/card/25/query 202 [ASYNC: canceled] 7.0 s (6 DB calls) App DB connections: 0/15 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 6 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:51+05:00 DEBUG metabase.middleware.log POST /api/card/21/query 202 [ASYNC: completed] 724.6 ms (6 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (4 idle, 0 queued) (121 total active threads) Queries in flight: 5 (0 queued)
[d4d6d3c3-dab7-426f-a1b5-c95467447405] 2021-04-29T13:47:51+05:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 2,
:started_at #t "2021-04-29T08:47:44.381863Z[UTC]",
:state "70100",
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :native,
:middleware {:js-int-to-string? true},
:native
{:query
"# Top 5 best selling designs in a country\r\nSELECT country as Country, name as Name, type as Type, currency_code as Currency Code, total_orders as Total Orders\r\n FROM\r\n ( SELECT type, country, name, currency_code, total_orders, created_at,\r\n @country_rank := IF(@current_country = country , @country_rank + 1, 1) AS country_rank,\r\n @current_country := country \r\n \t FROM (Select sales_order_item_info.type , so.created_at, soa.country_id as country, name, so.base_currency_code as currency_code, max(price) as price, sum(qty_ordered) as total_orders\r\n From sales_order_item_info join sales_order so on sales_order_item_info.order_id=so.entity_id join sales_order_address soa on soa.parent_id=so.entity_id \r\n Where (so.status='complete' or so.status='processing' or so.status='pending') and (soa.address_type='shipping')[[and date(DATE_ADD(created_at, INTERVAL 5 HOUR))>={{start_date}} and date(DATE_ADD(created_at, INTERVAL 5 HOUR))<={{end_date}} #]] and date(DATE_ADD(created_at, INTERVAL 5 HOUR))>=(DATE(NOW()) + INTERVAL -7 DAY) and date(DATE_ADD(created_at, INTERVAL 5 HOUR))<=DATE(NOW())\r\n [[and {{category}}]] \r\n Group by soa.country_id, name\r\n Order by total_orders desc) as top_selling_design_country\r\n \t ORDER BY country asc, total_orders DESC\r\n ) ranked\r\nWHERE country_rank <=5 ",
:template-tags
{"category" {:id "6594c55a-7557-e346-cac4-0437dc0148fc", :name "category", :display-name "Category", :type :dimension, :default nil, :dimension [:field-id 642], :widget-type :category},
"start_date" {:id "ead260f2-670c-53d6-fcd3-4df1167027a3", :name "start_date", :display-name "Start date", :type :date, :default nil},
"end_date" {:id "d8e7ae9d-09fc-92f1-8a74-1c0f8b1e4cd2", :name "end_date", :display-name "End date", :type :date, :default nil}}},
:database 2,
:parameters [],
:async? true,
:cache-ttl nil},
:status :failed,
:class java.sql.SQLTimeoutException,
:stacktrace
["org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:47)"
"org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)"
"org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)"
"org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)"
"org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)"
"org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:163)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
"--> driver.sql_jdbc.execute$fn__76636.invokeStatic(execute.clj:266)"
"driver.sql_jdbc.execute$fn__76636.invoke(execute.clj:264)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:391)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:385)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)"
"driver.sql_jdbc$fn__78141.invokeStatic(sql_jdbc.clj:49)"
"driver.sql_jdbc$fn__78141.invoke(sql_jdbc.clj:47)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:69)"
"query_processor.context.default$default_runf.invoke(default.clj:67)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
"query_processor.reducible$pivot.invoke(reducible.clj:31)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:26)"
"query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)"
"query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)"
"query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)"
"query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)"
"query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)"
"query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)"
"query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)"
"query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:316)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)"
"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)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)"
"query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)"
"query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)"
"driver$do_with_driver.invokeStatic(driver.clj:61)"
"driver$do_with_driver.invoke(driver.clj:57)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)"
"query_processor.middleware.store$initialize_store$fn__49067$fn__49068.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__49067.invoke(store.clj:10)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)"
"query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)"
"query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___39827$fn__39830.invoke(reducible.clj:108)"],
:context :question,
:error "(conn=1977087) Query execution was interrupted",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}

@Anum From the error, Metabase is getting a timeout back from MySQL, so you need to check your MySQL server logs - perhaps consider rewriting the query, so it's more optimized with indexes.

Hi @flamber, how do you suggest to add indices to it?

SELECT country as Country, name as Name, type as Type, total_orders as Total Orders
FROM
( SELECT type, country, name, currency_code, total_orders, created_at,
@country_rank := IF(@current_country = country , @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM (Select sales_order_item_info.type , so.created_at, soa.country_id as country, name, so.base_currency_code as currency_code, max(price) as price, sum(qty_ordered) as total_orders
From sales_order_item_info join sales_order so on sales_order_item_info.order_id=so.entity_id join sales_order_address soa on soa.parent_id=so.entity_id
Where (so.status='complete' or so.status='processing' or so.status='pending') and (soa.address_type='shipping')[[and date(DATE_ADD(created_at, INTERVAL 5 HOUR))>={{start_date}} and date(DATE_ADD(created_at, INTERVAL 5 HOUR))<={{end_date}} #]] and date(DATE_ADD(created_at, INTERVAL 5 HOUR))>=(DATE(NOW()) + INTERVAL -6 DAY) and date(DATE_ADD(created_at, INTERVAL 5 HOUR))<=DATE(NOW())
[[and {{category}}]]
Group by soa.country_id, name
Order by total_orders desc) as top_selling_design_country
ORDER BY country asc, total_orders DESC
) ranked
WHERE country_rank <=5