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
- Post "Diagnostic Info" from Admin > Troubleshooting
- 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.
@Anum Have a look at this article, which tries to go through various optimizations you can do:
https://www.metabase.com/learn/building-analytics/sql-templates/sql-best-practices.html
@flamber when the value is downloaded it is correct. But the visualization card does not load correctly.
It should be 5 items in each chart, But it shows many of them.
@Anum
Check the request response to figure out what might be causing the problem. It is impossible for me to know, since I don't have your dataset.
Try the latest release - 0.39.1 - make sure to backup before upgrading in case you want to downgrade.
What do you mean by request response?
Basically I have to find the top n categories within a subcategory using mysql. The only solution that I find for ranking is @ annotation and it makes the query execution time is way too long. I'm not finding any optimal solution.
When the card is opened and query is run, it runs fine. But does not load in the dashboard.
@Anum I don't know why it isn't working, but since I don't have your data or logs, then it's impossible for me to figure out what's going on. You will have to debug. Check the MySQL server log to see if it provides any insights. Check the browser developer Network-tab to see requests - both what is being sent and the response.
Try using a sub-select instead of variables, just like when cumulative results are needed:
https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql
Or CTE - still without variables:
https://www.metabase.com/learn/building-analytics/sql-templates/sql-cte.html
Get this error while vieweing the network tab
app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5 Error: This chart type doesn't support more than 100 series of data.
at app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5
at H (app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5)
at Object.t.barRenderer [as renderer] (app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5)
at t.value (app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5)
at t.value (app-embed.bundle.js?cc8a7d79bfbfb5bb5275:5)
at e.notifyAll (vendor.bundle.js?cc8a7d79bfbfb5bb5275:406)
at d.close (vendor.bundle.js?cc8a7d79bfbfb5bb5275:436)
at d.closeAll (vendor.bundle.js?cc8a7d79bfbfb5bb5275:98)
at d.perform (vendor.bundle.js?cc8a7d79bfbfb5bb5275:98)
at v.perform (vendor.bundle.js?cc8a7d79bfbfb5bb5275:98)
So the dashboard is integrated in a web app. The card doesn't load. It is empty. However, if i download it has 20 rows.
- Upgrade to latest release - remember to backup first.
- Click the requests in the browser developer Network-tab to see the response data, which should likely indicate what is going wrong.
- Amount of rows doesn't have anything to do with series.
- My current version is v0.37.3 and latest is v0.39.1. And you are saying that upgrading would resolve the issue?
- I have sent you a screenshot. Am I checking the response correctly?
- What is series then?
- I have no idea. Try reproducing the problem with Sample Dataset.
- No. https://developer.chrome.com/docs/devtools/network/#details
- Series can either be columns or breakouts of values in rows.
@Anum I don't know. You'll need to post the entire API response from the card, so it can be inspected if there's an error somewhere there. But even better, upgrade and if it still fails, then try reproducing with Sample Dataset.