title: MySQL 1114 Error, Table /tmp/#sql is full
body: <p>I am trying to run TPC-H queries in MySQL server.
  Currently, I am using 8.0.23 version (The error did not appear when I used 8.0.19 somehow).
  Benchmark size of TPC-H is 10 GB.</p>
  <p>I get this error (1114 Error, The table /tmp/#sql~~~ is full) when I submit multiple queries at the same time using 8 threads. There is a chance that identical or similar queries submitted at the same time using different threads.</p>
  <p>However, when I just submit a single query in MySQL shell, then it returns a result without the error.</p>
  <p>This is the query (there are other queries that raise the same error.)</p>
  <pre><code>with year_total as ( select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total , 's' sale_type from tpcds1.customer , tpcds1.store_sales , tpcds1.date_dim where c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year union all select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total , 'c' sale_type from tpcds1.customer , tpcds1.catalog_sales , tpcds1.date_dim where c_customer_sk = cs_bill_customer_sk and cs_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year union all select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total , 'w' sale_type from tpcds1.customer , tpcds1.web_sales , tpcds1.date_dim where c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year ) select t_s_secyear.customer_id , t_s_secyear.customer_first_name , t_s_secyear.customer_last_name , t_s_secyear.customer_login from year_total t_s_firstyear , year_total t_s_secyear , year_total t_c_firstyear , year_total t_c_secyear , year_total t_w_firstyear , year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id and t_s_firstyear.customer_id = t_c_secyear.customer_id and t_s_firstyear.customer_id = t_c_firstyear.customer_id and t_s_firstyear.customer_id = t_w_firstyear.customer_id and t_s_firstyear.customer_id = t_w_secyear.customer_id and t_s_firstyear.sale_type = 's' and t_c_firstyear.sale_type = 'c' and t_w_firstyear.sale_type = 'w' and t_s_secyear.sale_type = 's' and t_c_secyear.sale_type = 'c' and t_w_secyear.sale_type = 'w' and t_s_firstyear.dyear = 1999 and t_s_secyear.dyear = 1999+1 and t_c_firstyear.dyear = 1999 and t_c_secyear.dyear = 1999+1 and t_w_firstyear.dyear = 1999 and t_w_secyear.dyear = 1999+1 and t_s_firstyear.year_total &gt; 0 and t_c_firstyear.year_total &gt; 0 and t_w_firstyear.year_total &gt; 0 and case when t_c_firstyear.year_total &gt; 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end &gt; case when t_s_firstyear.year_total &gt; 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end and case when t_c_firstyear.year_total &gt; 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end &gt; case when t_w_firstyear.year_total &gt; 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end order by t_s_secyear.customer_id , t_s_secyear.customer_first_name , t_s_secyear.customer_last_name , t_s_secyear.customer_login limit 100
  </code></pre>
  <p>I checked this thread,
  <a href="https://stackoverflow.com/questions/19639343/error-while-executing-sql-file-error-1114-mysql-table-is-full">error while executing sql file (ERROR 1114 mysql table is full)</a></p>
  <p>However, It does not help. Here is the list that I checked from the link above.</p>
  <ol>
  <li>I have enough disk space.</li>
  <li>I increased the size of max_heap_table_size and tmp_table_size as below.</li>
  <li>I restarted MySQL server.</li>
  <li>innodb_data_file_path is set with autoextend. So, I expect it to extend automatically.</li>
  </ol>
  <p>I also checked that my tmpdir is set properly and there is enough disk space.</p>
  <p>Here are my MySQL configurations.</p>
  <pre><code>innodb_buffer_pool_size=16GB
  select @@innodb_buffer_pool_size; -&gt; 17179869184
  
  select @@max_heap_table_size; -&gt; 2147483648
  select @@tmp_table_size; -&gt; 2147483648
  
  +-----------------------+------------------------+
  | Variable_name         | Value                  |
  +-----------------------+------------------------+
  | innodb_data_file_path | ibdata1:10M:autoextend |
  +-----------------------+------------------------+
  </code></pre>
  <p>Any suggestions will be appreciated. Thanks for reading.</p>