Export excel fails

Hello. We are running Metabase in a container with Nginx as reverse proxy on AWS with a load balancer. We are using a large query for listing all orders for a specific customer (around 300000 rows). When trying to export the result as an excel file, it fails after 1.9 min. The Nginx timeouts have been increased to 1800 s. Does anyone have a guess what could be causing this issue?

Metabase logs:

[0eaee756-b9d9-4370-9606-c8cfa0814e02] 2023-05-30T09:59:29+02:00 DEBUG metabase.server.middleware.log POST /api/dashboard/9/dashcard/19/card/19/query/xlsx 202 [ASYNC: canceled] 1.9 mins (21 DB calls) App DB connections: 0/10 Jetty threads: 2/50 (5 idle, 0 queued) (65 total active threads) Queries in flight: 0 (0 queued); mysql DB 2 connections: 0/1 (0 threads blocked)
[0eaee756-b9d9-4370-9606-c8cfa0814e02] 2023-05-30T09:59:31+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Closed
{:database_id 2,
 :started_at #t "2023-05-30T07:57:38.751496Z[GMT]",
 :json_query
 {:constraints nil,
  :type :native,
  :middleware
  {:js-int-to-string? false,
   :ignore-cached-results? true,
   :process-viz-settings? true,
   :skip-results-metadata? true,
   :format-rows? false},
  :native
  {:template-tags
   {"customerId"
    {:id "1eb0bfa4-fca6-5fc3-e5d0-b95c1b5f389a",
     :name "customerId",
     :display-name "Customerid",
     :type :dimension,
     :dimension [:field 1313 nil],
     :widget-type :id,
     :required true},
    "projectName"
    {:id "8502c11c-043b-9376-fa97-e369a39f277b",
     :name "projectName",
     :display-name "Projectname",
     :type :dimension,
     :dimension [:field 442 nil],
     :widget-type :string/=}},
   :query
   "SELECT\r\n    customer.name CustomerName,\r\n    project.name ProjectName,\r\n    project.country ProjectCountry,\r\n    unit.name UnitName,\r\n    product_group.period Period,\r\n    room.name Room,\r\n    product_group.name ProductGroupName,\r\n    CASE\r\n        WHEN pgp.type = 0 THEN '0 - Original'\r\n        WHEN pgp.type = 1 THEN '1 - Free addon'\r\n        WHEN pgp.type = 2 THEN '2 - Addon'\r\n        WHEN pgp.type IS NULL\r\n           AND EXISTS(\r\n                    SELECT *\r\n                    FROM selection_collection_product AS scp\r\n                    WHERE scp.product_id = pp.id\r\n                ) THEN '3 - Design line'\r\n       ELSE '4 - Special order'\r\n    END SelectionTypeName,\r\n    CASE\r\n       WHEN props.type = 0 THEN '0 - Default product'\r\n       WHEN props.type = 1 THEN '1 - Article product'\r\n       WHEN props.type = 2 THEN '2 - Package product'\r\n       WHEN props.type = 4 THEN '4 - Additional order'\r\n       WHEN props.type = 5 THEN '5 - Replacement order'\r\n       WHEN props.type = 6 THEN '6 - Blueprint revision (deprecated)'\r\n       WHEN props.type = 7 THEN '7 - Information product'\r\n    END ProductTypeName,\r\n    props.artnr ArticleNumber,\r\n    props.rsknr RskNumber,\r\n    props.enr eNumber,\r\n    IF(pdesc.name IS NOT NULL, pdesc.name, content.name) ProductName,\r\n    IF(props.unit IS NULL,\r\n           IFNULL(SUM(\r\n                           IFNULL(\r\n                                   IF(\r\n                                           product_group.has_quantity,\r\n                                           IFNULL(urpg.quantity, 1),\r\n                                           NULL\r\n                                       ),\r\n                                   orw.quantity\r\n                               )\r\n                       ),\r\n                   COUNT(DISTINCT orw.id)\r\n               ),\r\n           1\r\n       ) Quantity,\r\n    orw.price Price,\r\n    IF(props.unit IS NULL, SUM(orw.sum), orw.sum) TotalPrice,\r\n    orw.price_net NetPrice,\r\n    IF(props.unit IS NULL, SUM(orw.sum_net), orw.sum_net) TotalNetPrice,\r\n    project.currency Currency,\r\n    supplier.name SupplierName,\r\n    o.created_time OrderDate,\r\n#     (SELECT MAX(o.created_time)\r\n#         FROM `order` o\r\n#         WHERE o.unit_id = unit.id) OrderDate,\r\n    COALESCE(unit.selperiod3_end, unit.selperiod2_end, unit.selperiod1_end) StopDate,\r\n    pmes.depth ProductDepth,\r\n    pmes.height ProductHeight,\r\n    pmes.width ProductWidth,\r\n    pmes.weight ProductWeight,\r\n    SUM(orwc.material_cost) MaterialCost,\r\n    SUM(orwc.material_markup) MaterialMarkup,\r\n    SUM(orwc.material_total) MaterialTotalCost,\r\n    SUM(orwc.labour_cost) LabourCost,\r\n    SUM(orwc.labour_markup) LabourMarkup,\r\n    SUM(orwc.labour_total) LabourTotalCost,\r\n    unit.num_rooms NumberOfRooms,\r\n    unit.story Story,\r\n    IF(props.price_type = 2, SUM(ur.area), 0) Floor,\r\n    SUM(IF(props.price_type = 6, ur.area2, 0)) Floor2,\r\n    SUM(IF(props.price_type = 3, ur.area_walls, 0)) Walls,\r\n    SUM(IF(props.price_type = 5, ur.area_walls2, 0)) Walls2,\r\n    SUM(IF(props.price_type = 4, ur.running_metre, 0)) RunningMeter\r\nFROM order_row AS orw\r\n    INNER JOIN project_product pp ON pp.id = orw.product\r\n    INNER JOIN product_properties props ON props.project_product_id = pp.id\r\n    INNER JOIN product_measurement pmes ON (pmes.project_product_id = pp.id)\r\n    INNER JOIN unit ON unit.id = orw.unit AND selperiod_lock > 0\r\n    INNER JOIN project ON project.id = unit.project\r\n    INNER JOIN customer ON customer.id = project.customer\r\n    LEFT JOIN product_content content ON content.project_product_id = pp.id AND content.language = project.language\r\n    LEFT JOIN unit_room ur ON ur.unit = unit.id AND ur.room = orw.room\r\n    LEFT JOIN unit_room_product_group urpg ON urpg.unit_room = ur.id AND urpg.product_group = orw.product_group\r\n    LEFT JOIN product_group_product pgp ON pgp.product = orw.product AND pgp.group = orw.product_group\r\n    LEFT JOIN product_group ON product_group.id = orw.product_group\r\n    LEFT JOIN product_description pdesc ON pdesc.project_product_id = orw.product AND pdesc.product_group_id = product_group.id AND pdesc.room_id = orw.room\r\n    LEFT JOIN supplier ON supplier.id = props.supplier\r\n    LEFT JOIN room ON room.id = orw.room\r\n    LEFT JOIN order_row_cost AS orwc ON orwc.order_row_id = orw.id\r\n    LEFT JOIN `order` o on o.unit_id = unit.id and o.period = product_group.period and o.deleted_time is null\r\nWHERE\r\n    project.status = 'ACTIVE'\r\n    AND project.production=1\r\n    AND project.is_template=0\r\n    AND unit.production=1\r\n    AND orw.is_valid = 1\r\n    AND orw.deleted_time IS NULL\r\n    AND (pgp.type IN (0, 1, 2)\r\n         OR (pgp.type IS NULL\r\n             AND EXISTS(SELECT scp.*\r\n                        FROM selection_collection_product AS scp\r\n                        WHERE scp.product_id = pp.id)\r\n            )\r\n        )\r\n    AND (product_group.period <= selperiod_lock\r\n         OR (product_group.period IS NULL\r\n             AND orw.room IS NULL\r\n             AND orw.product_group IS NULL\r\n             AND (props.selection_period IS NULL\r\n                  OR props.selection_period <= selperiod_lock)\r\n             )\r\n        )\r\n    [[AND {{customerId}}]]\r\n--    [[AND {{projectName}}]]\r\nGROUP BY pp.id,\r\n         orw.unit,\r\n         room.id,\r\n         orw.product_group,\r\n         orw.price,\r\n         orw.price_net,\r\n         pgp.type,\r\n         orw.data,\r\n         product_group.period\r\nUNION\r\n-- Handles special orders\r\nSELECT\r\n    customer.name CustomerName,\r\n    project.name ProjectName,\r\n    project.country ProjectCountry,\r\n    unit.name UnitName,\r\n    product_group.period Period,\r\n    room.name Room,\r\n    product_group.name ProductGroupName,\r\n    CASE\r\n        WHEN pgp.type = 0 THEN '0 - Original'\r\n        WHEN pgp.type = 1 THEN '1 - Free addon'\r\n        WHEN pgp.type = 2 THEN '2 - Addon'\r\n        WHEN pgp.type IS NULL\r\n           AND EXISTS(\r\n                    SELECT *\r\n                    FROM selection_collection_product AS scp\r\n                    WHERE scp.product_id = pp.id\r\n                ) THEN '3 - Design line'\r\n       ELSE '4 - Special order'\r\n    END SelectionTypeName,\r\n    CASE\r\n       WHEN props.type = 0 THEN '0 - Default product'\r\n       WHEN props.type = 1 THEN '1 - Article product'\r\n       WHEN props.type = 2 THEN '2 - Package product'\r\n       WHEN props.type = 4 THEN '4 - Additional order'\r\n       WHEN props.type = 5 THEN '5 - Replacement order'\r\n       WHEN props.type = 6 THEN '6 - Blueprint revision (deprecated)'\r\n       WHEN props.type = 7 THEN '7 - Information product'\r\n    END ProductTypeName,\r\n    props.artnr ArticleNumber,\r\n    props.rsknr RskNumber,\r\n    props.enr eNumber,\r\n    IF(pdesc.name IS NOT NULL, pdesc.name, content.name) ProductName,\r\n    IF(props.unit IS NULL,\r\n           IFNULL(SUM(\r\n                           IFNULL(\r\n                                   IF(\r\n                                           product_group.has_quantity,\r\n                                           IFNULL(urpg.quantity, 1),\r\n                                           NULL\r\n                                       ),\r\n                                   orw.quantity\r\n                               )\r\n                       ),\r\n                   COUNT(DISTINCT orw.id)\r\n               ),\r\n           1\r\n       ) Quantity,\r\n    orw.price Price,\r\n    IF(props.unit IS NULL, SUM(orw.sum), orw.sum) TotalPrice,\r\n    orw.price_net NetPrice,\r\n    IF(props.unit IS NULL, SUM(orw.sum_net), orw.sum_net) TotalNetPrice,\r\n    project.currency Currency,\r\n    supplier.name SupplierName,\r\n    o.created_time OrderDate,\r\n#     (SELECT MAX(o.created_time)\r\n#         FROM `order` o\r\n#         WHERE o.unit_id = unit.id) OrderDate,\r\n    COALESCE(unit.selperiod3_end, unit.selperiod2_end, unit.selperiod1_end) StopDate,\r\n    pmes.depth ProductDepth,\r\n    pmes.height ProductHeight,\r\n    pmes.width ProductWidth,\r\n    pmes.weight ProductWeight,\r\n    SUM(orwc.material_cost) MaterialCost,\r\n    SUM(orwc.material_markup) MaterialMarkup,\r\n    SUM(orwc.material_total) MaterialTotalCost,\r\n    SUM(orwc.labour_cost) LabourCost,\r\n    SUM(orwc.labour_markup) LabourMarkup,\r\n    SUM(orwc.labour_total) LabourTotalCost,\r\n    unit.num_rooms NumberOfRooms,\r\n    unit.story Story,\r\n    IF(props.price_type = 2, SUM(ur.area), 0) Floor,\r\n    SUM(IF(props.price_type = 6, ur.area2, 0)) Floor2,\r\n    SUM(IF(props.price_type = 3, ur.area_walls, 0)) Walls,\r\n    SUM(IF(props.price_type = 5, ur.area_walls2, 0)) Walls2,\r\n    SUM(IF(props.price_type = 4, ur.running_metre, 0)) RunningMeter\r\nFROM order_row AS orw\r\n    INNER JOIN project_product pp ON pp.id = orw.product\r\n    INNER JOIN product_properties props ON props.project_product_id = pp.id\r\n    INNER JOIN product_measurement pmes ON (pmes.project_product_id = pp.id)\r\n    INNER JOIN unit ON unit.id = orw.unit AND selperiod_lock > 0\r\n    INNER JOIN project ON project.id = unit.project\r\n    INNER JOIN customer ON customer.id = project.customer\r\n    LEFT JOIN product_content content ON content.project_product_id = pp.id AND content.language = project.language\r\n    LEFT JOIN unit_room ur ON ur.unit = unit.id AND ur.room = orw.room\r\n    LEFT JOIN unit_room_product_group urpg ON urpg.unit_room = ur.id AND urpg.product_group = orw.product_group\r\n    LEFT JOIN product_group_product pgp ON pgp.product = orw.product AND pgp.group = orw.product_group\r\n    LEFT JOIN product_group ON product_group.id = orw.product_group\r\n    LEFT JOIN product_description pdesc ON pdesc.project_product_id = orw.product AND pdesc.product_group_id = product_group.id AND pdesc.room_id = orw.room\r\n    LEFT JOIN supplier ON supplier.id = props.supplier\r\n    LEFT JOIN room ON room.id = orw.room\r\n    LEFT JOIN order_row_cost AS orwc ON orwc.order_row_id = orw.id\r\n    LEFT JOIN `order` o on o.unit_id = unit.id and o.period = product_group.period and o.deleted_time is null\r\nWHERE\r\n    project.status = 'ACTIVE'\r\n    AND project.production=1\r\n    AND project.is_template=0\r\n    AND unit.production=1\r\n    AND orw.is_valid = 1\r\n    AND orw.deleted_time IS NULL\r\n    AND pgp.type IS NULL\r\n    AND NOT EXISTS(SELECT scp.*\r\n                  FROM selection_collection_product AS scp\r\n                  WHERE scp.product_id = pp.id\r\n                  )\r\n    AND (product_group.period <= selperiod_lock\r\n         OR (product_group.period IS NULL\r\n             AND orw.room IS NULL\r\n             AND orw.product_group IS NULL\r\n             AND (props.selection_period IS NULL\r\n                  OR props.selection_period <= selperiod_lock)\r\n             )\r\n        )\r\n    [[AND {{customerId}}]]\r\n--    [[AND {{projectName}}]]\r\nGROUP BY pp.id,\r\n         orw.unit,\r\n         -- Special orders can't group by room or product group\r\n         -- Will cause multiple rows\r\n         -- room.id,\r\n         -- orw.product_group,\r\n         orw.price,\r\n         orw.price_net,\r\n         pgp.type,\r\n         orw.data,\r\n         product_group.period"},
  :database 2,
  :parameters [{:type :id, :value [36], :id "a518e319", :target [:dimension [:template-tag "customerId"]]}],
  :async? true,
  :cache-ttl 1015},
 :status :failed,
 :class org.eclipse.jetty.io.EofException,
 :stacktrace
 ["org.eclipse.jetty.server.HttpOutput.checkWritable(HttpOutput.java:750)"
  "org.eclipse.jetty.server.HttpOutput.write(HttpOutput.java:780)"
  "java.base/java.util.zip.GZIPOutputStream.finish(GZIPOutputStream.java:170)"
  "java.base/java.util.zip.DeflaterOutputStream.close(DeflaterOutputStream.java:245)"
  "--> async.streaming_response$delay_output_stream$fn__36948.invoke(streaming_response.clj:121)"
  "async.streaming_response.proxy$java.io.OutputStream$ff19274a.close(Unknown Source)"
  "query_processor.streaming.xlsx$fn$reify__54199.finish_BANG_(xlsx.clj:524)"
  "query_processor.streaming$streaming_reducedf$fn__54294.invoke(streaming.clj:132)"
  "query_processor.context$reducedf.invokeStatic(context.clj:78)"
  "query_processor.context$reducedf.invoke(context.clj:73)"
  "query_processor.context.default$default_reducef.invokeStatic(default.clj:64)"
  "query_processor.context.default$default_reducef.invoke(default.clj:49)"
  "query_processor.middleware.cache$run_query_with_cache$reducef_SINGLEQUOTE___69252$fn__69253.invoke(cache.clj:196)"
  "query_processor.middleware.cache.impl$do_with_serialization.invokeStatic(impl.clj:83)"
  "query_processor.middleware.cache.impl$do_with_serialization.invoke(impl.clj:54)"
  "query_processor.middleware.cache.impl$do_with_serialization.invokeStatic(impl.clj:73)"
  "query_processor.middleware.cache.impl$do_with_serialization.invoke(impl.clj:54)"
  "query_processor.middleware.cache$run_query_with_cache$reducef_SINGLEQUOTE___69252.invoke(cache.clj:192)"
  "query_processor.context$reducef.invokeStatic(context.clj:70)"
  "query_processor.context$reducef.invoke(context.clj:63)"
  "query_processor.context.default$default_runf$respond_STAR___53372.invoke(default.clj:69)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:513)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
  "driver.sql_jdbc$fn__105125.invokeStatic(sql_jdbc.clj:63)"
  "driver.sql_jdbc$fn__105125.invoke(sql_jdbc.clj:61)"
  "query_processor.context$executef.invokeStatic(context.clj:60)"
  "query_processor.context$executef.invoke(context.clj:49)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
  "query_processor.context.default$default_runf.invoke(default.clj:66)"
  "query_processor.context$runf.invokeStatic(context.clj:46)"
  "query_processor.context$runf.invoke(context.clj:40)"
  "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
  "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
  "query_processor.middleware.cache$run_query_with_cache.invokeStatic(cache.clj:197)"
  "query_processor.middleware.cache$run_query_with_cache.invoke(cache.clj:182)"
  "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69265.invoke(cache.clj:223)"
  "query_processor.middleware.permissions$check_query_permissions$fn__64737.invoke(permissions.clj:126)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68156.invoke(mbql_to_native.clj:24)"
  "query_processor$fn__70784$combined_post_process__70789$combined_post_process_STAR___70790.invoke(query_processor.clj:243)"
  "query_processor$fn__70784$combined_pre_process__70785$combined_pre_process_STAR___70786.invoke(query_processor.clj:240)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69176$fn__69181.invoke(resolve_database_and_driver.clj:36)"
  "driver$do_with_driver.invokeStatic(driver.clj:90)"
  "driver$do_with_driver.invoke(driver.clj:86)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69176.invoke(resolve_database_and_driver.clj:35)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__65046.invoke(fetch_source_query.clj:310)"
  "query_processor.middleware.store$initialize_store$fn__65224$fn__65225.invoke(store.clj:12)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:47)"
  "query_processor.store$do_with_store.invoke(store.clj:41)"
  "query_processor.middleware.store$initialize_store$fn__65224.invoke(store.clj:11)"
  "query_processor.middleware.normalize_query$normalize$fn__69465.invoke(normalize_query.clj:25)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__66402.invoke(constraints.clj:54)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__69401.invoke(process_userland_query.clj:151)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__69778.invoke(catch_exceptions.clj:171)"
  "query_processor.reducible$async_qp$qp_STAR___59546$thunk__59548.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___59546$fn__59550.invoke(reducible.clj:108)"],
 :card_id 19,
 :context :dashboard,
 :error "Closed",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}

Metabase Diagnostic Info:

{
  "browser-info": {
    "language": "sv-SE",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.18+10-post-Ubuntu-0ubuntu122.04",
    "java.vendor": "Ubuntu",
    "java.vendor.url": "https://ubuntu.com/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10-post-Ubuntu-0ubuntu122.04",
    "os.name": "Linux",
    "os.version": "5.10.179-166.674.amzn2.aarch64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mysql"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "8.0.26"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.7.6"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-05-24",
      "tag": "v0.46.4",
      "branch": "release-x.46.x",
      "hash": "f858476"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

What about MySQL timeout?

Hello Luiggi! The wait_timeout variable in MySQL is set to 28800s, so that shouldn't affect it. Also, I have tried running the query SELECT SLEEP(300) which runs without a timeout.