| Bug #99100 | GROUP BY will throw table is full when temptable memory allocation exceed limit | ||
|---|---|---|---|
| Submitted: | 27 Mar 2020 13:22 | Modified: | 18 Aug 2021 18:10 |
| Reporter: | Dean Zhou | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 1114, table is full, temptable | ||
[30 Mar 2020 13:36]
MySQL Verification Team
Hi Mr. Zhou, Thank you for your bug report. Your report looks very interesting. With settings that you used, if internal in-memory TempTable overflows your setting of 2 Mb, it would be converted into InnoDB table. Hence, we would very much like to repeat this behaviour. For that purpose we need a dump of your table in question. You can make a smaller table then the one you use, but it is important that we repeat the behaviour that you are reporting. Thank you very much in advance.
[31 Mar 2020 9:29]
Dean Zhou
test data
Attachment: sbtest1.sql (application/octet-stream, text), 1.87 MiB.
[31 Mar 2020 9:39]
Dean Zhou
Hi Sinisa Milivojevic:
My test data have been uploaded. The repeat operation as follows:
1. restart mysqld
2. load data
mysql -S /data/mysql5858/mysql.sock test < sbtest1
3. execute sql
mysql> set global temptable_max_ram = 2097152;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10;
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| cnt | pad | c |
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| 2 | 69934613059-88090326252-43723189827-95836596353-21488500475 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 |
| 1 | 00008932442-46813404728-01940988773-52841132560-83229624724 | 39545630315-26642029789-33173101198-03433120718-18245412627-25499335728-86684299507-68747727553-68821166586-23320828147 |
mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10;
ERROR 1114 (HY000): The table '/data/mysql5858/tmp/#sql6d02f_9_3' is full
--------------------------
Only when the table()->file->ha_update_row() is executed, a temptable memory overflow can trigger an error, You have to construct this scene.
In my case, the row id = 1 and the row id = 3549 has same pad value '69934613059-88090326252-43723189827-95836596353-21488500475', the row id = 1 first processed, then when the row id = 3549 iterate, it find duplicate pad value in temptable, so call table()->file->ha_update_row(), if temptable memory overflow happen, an error may throw.
If you still not repeat ,you can position the row that trigger temptable memory overflow as follows:
edit sql/composite_iterators.cc
// add function
static std::string buf_to_raw(const uchar *buf, uint buf_size_bytes) {
std::string r;
r.reserve(buf_size_bytes);
for (uint i = 0; i < buf_size_bytes; ++i) {
const uchar c = buf[i];
r += static_cast<char>(isprint(c) ? c : '.');
}
return r;
}
bool TemptableAggregateIterator::Init() {
...
// print the row, When this row was process, a temptable memory overflow was triggered, causing the convert to disk table. Then update this row pad value, make pad value duplicate.
fprintf(stderr, "dean begin create_ondisk_from_heap: %s\n", buf_to_raw(table()->record[0], table()->s->reclength).c_str());
if (create_ondisk_from_heap(thd(), table(), error, false, NULL)) {
end_unique_index.commit();
return true; // Not a table_is_full error.
}
...
}
[31 Mar 2020 14:38]
MySQL Verification Team
Hi Mr. Zhou, I have tried to repeat your test case. In your steps you omitted two details. First, ONLY_FULL_GROUP_BY should not be set in the sql_mode. Second, temptable_use_mmap=0 should be set as well. However, I tried three times and could not repeat the behaviour: mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0,10; +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | cnt | pad | c | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | 2 | 69934613059-88090326252-43723189827-95836596353-21488500475 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | | 1 | 00008932442-46813404728-01940988773-52841132560-83229624724 | 39545630315-26642029789-33173101198-03433120718-18245412627-25499335728-86684299507-68747727553-68821166586-23320828147 | | 1 | 00012121473-99340742818-65645218245-24672190492-91206300763 | 10551337033-35012101948-10777343612-40342522882-55133369504-03274346501-88550763035-44986334365-50440772271-05209617886 | | 1 | 00014707131-35961550937-96740474351-20764844506-51790117327 | 65909732174-08915122040-84703019410-88540499992-36625696192-56977236518-37644208706-63093978941-41930549832-33750809425 | | 1 | 00015972636-12938797184-51480469689-95438959539-49609260223 | 25223568049-11393603278-58393229064-23925481880-78860656214-64411479937-22473237394-93102487604-86556078208-89998390593 | | 1 | 00025657405-89720992240-75705004494-59945544768-03922509539 | 22255864436-61183834403-74800947466-54206596558-28113794942-84230886096-95282314842-38929134176-64704205104-61809024504 | | 1 | 00031797732-28539023142-12749859403-49115835313-49859166692 | 67668351388-27412243714-36400684636-76684336020-09618768230-20100590242-56285492525-81444741143-27857444804-76698775731 | | 1 | 00040693668-14897682639-78422250679-07899532961-01913290396 | 33496983956-04778812647-96555973965-53303752721-42532112217-28755536610-90515137014-56475965986-12380271982-81588142687 | | 1 | 00052612811-83871450789-38629549990-99368533768-69494881700 | 92736175277-32828338665-47548678925-35112523595-99270925935-50009548284-07458205883-48817201800-26127652859-35595913518 | | 1 | 00059584344-80865914534-06944141263-71034194819-41225372610 | 10605791058-03493906918-64934487291-20725760796-45864390924-78283400000-05423546275-70094329459-55378101721-04884213222 | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.90 sec) mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0,10; +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | cnt | pad | c | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | 2 | 69934613059-88090326252-43723189827-95836596353-21488500475 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | | 1 | 00008932442-46813404728-01940988773-52841132560-83229624724 | 39545630315-26642029789-33173101198-03433120718-18245412627-25499335728-86684299507-68747727553-68821166586-23320828147 | | 1 | 00012121473-99340742818-65645218245-24672190492-91206300763 | 10551337033-35012101948-10777343612-40342522882-55133369504-03274346501-88550763035-44986334365-50440772271-05209617886 | | 1 | 00014707131-35961550937-96740474351-20764844506-51790117327 | 65909732174-08915122040-84703019410-88540499992-36625696192-56977236518-37644208706-63093978941-41930549832-33750809425 | | 1 | 00015972636-12938797184-51480469689-95438959539-49609260223 | 25223568049-11393603278-58393229064-23925481880-78860656214-64411479937-22473237394-93102487604-86556078208-89998390593 | | 1 | 00025657405-89720992240-75705004494-59945544768-03922509539 | 22255864436-61183834403-74800947466-54206596558-28113794942-84230886096-95282314842-38929134176-64704205104-61809024504 | | 1 | 00031797732-28539023142-12749859403-49115835313-49859166692 | 67668351388-27412243714-36400684636-76684336020-09618768230-20100590242-56285492525-81444741143-27857444804-76698775731 | | 1 | 00040693668-14897682639-78422250679-07899532961-01913290396 | 33496983956-04778812647-96555973965-53303752721-42532112217-28755536610-90515137014-56475965986-12380271982-81588142687 | | 1 | 00052612811-83871450789-38629549990-99368533768-69494881700 | 92736175277-32828338665-47548678925-35112523595-99270925935-50009548284-07458205883-48817201800-26127652859-35595913518 | | 1 | 00059584344-80865914534-06944141263-71034194819-41225372610 | 10605791058-03493906918-64934487291-20725760796-45864390924-78283400000-05423546275-70094329459-55378101721-04884213222 | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.90 sec) Are there any settings that should be used ?????
[1 Apr 2020 3:37]
Dean Zhou
Hi, Sinisa Milivojevic: Yes, I have set sql_mode='' and temptable_use_mmap=0. Because the memory consumption of each environment is different, Trigger conditions are very strict, temptable memory overflow must happen in table()->file->ha_update_row(). In my case, the trigger row is the row with id = 3549, It could be other row in your environmen, As I said above, I suggest you add log info to print the row that trigger temptable memory overflow, then update pad value of the row, make pad value duplicate with row id = 1, and then you can repeat "the table is full" error.
[2 Apr 2020 12:14]
MySQL Verification Team
Hi Mr. Zhou, I have ran several more tests and I managed to repeat the behaviour: mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | cnt | pad | c | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | 2 | 69934613059-88090326252-43723189827-95836596353-21488500475 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | | 1 | 00008932442-46813404728-01940988773-52841132560-83229624724 | 39545630315-26642029789-33173101198-03433120718-18245412627-25499335728-86684299507-68747727553-68821166586-23320828147 | | 1 | 00012121473-99340742818-65645218245-24672190492-91206300763 | 10551337033-35012101948-10777343612-40342522882-55133369504-03274346501-88550763035-44986334365-50440772271-05209617886 | | 1 | 00014707131-35961550937-96740474351-20764844506-51790117327 | 65909732174-08915122040-84703019410-88540499992-36625696192-56977236518-37644208706-63093978941-41930549832-33750809425 | | 1 | 00015972636-12938797184-51480469689-95438959539-49609260223 | 25223568049-11393603278-58393229064-23925481880-78860656214-64411479937-22473237394-93102487604-86556078208-89998390593 | | 1 | 00025657405-89720992240-75705004494-59945544768-03922509539 | 22255864436-61183834403-74800947466-54206596558-28113794942-84230886096-95282314842-38929134176-64704205104-61809024504 | | 1 | 00031797732-28539023142-12749859403-49115835313-49859166692 | 67668351388-27412243714-36400684636-76684336020-09618768230-20100590242-56285492525-81444741143-27857444804-76698775731 | | 1 | 00040693668-14897682639-78422250679-07899532961-01913290396 | 33496983956-04778812647-96555973965-53303752721-42532112217-28755536610-90515137014-56475965986-12380271982-81588142687 | | 1 | 00052612811-83871450789-38629549990-99368533768-69494881700 | 92736175277-32828338665-47548678925-35112523595-99270925935-50009548284-07458205883-48817201800-26127652859-35595913518 | | 1 | 00059584344-80865914534-06944141263-71034194819-41225372610 | 10605791058-03493906918-64934487291-20725760796-45864390924-78283400000-05423546275-70094329459-55378101721-04884213222 | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.11 sec) mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; ERROR 1114 (HY000): The table '/tmp/#sql1967_9_3' is full mysql> mysql> mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | cnt | pad | c | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ | 2 | 69934613059-88090326252-43723189827-95836596353-21488500475 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | | 1 | 00008932442-46813404728-01940988773-52841132560-83229624724 | 39545630315-26642029789-33173101198-03433120718-18245412627-25499335728-86684299507-68747727553-68821166586-23320828147 | | 1 | 00012121473-99340742818-65645218245-24672190492-91206300763 | 10551337033-35012101948-10777343612-40342522882-55133369504-03274346501-88550763035-44986334365-50440772271-05209617886 | | 1 | 00014707131-35961550937-96740474351-20764844506-51790117327 | 65909732174-08915122040-84703019410-88540499992-36625696192-56977236518-37644208706-63093978941-41930549832-33750809425 | | 1 | 00015972636-12938797184-51480469689-95438959539-49609260223 | 25223568049-11393603278-58393229064-23925481880-78860656214-64411479937-22473237394-93102487604-86556078208-89998390593 | | 1 | 00025657405-89720992240-75705004494-59945544768-03922509539 | 22255864436-61183834403-74800947466-54206596558-28113794942-84230886096-95282314842-38929134176-64704205104-61809024504 | | 1 | 00031797732-28539023142-12749859403-49115835313-49859166692 | 67668351388-27412243714-36400684636-76684336020-09618768230-20100590242-56285492525-81444741143-27857444804-76698775731 | | 1 | 00040693668-14897682639-78422250679-07899532961-01913290396 | 33496983956-04778812647-96555973965-53303752721-42532112217-28755536610-90515137014-56475965986-12380271982-81588142687 | | 1 | 00052612811-83871450789-38629549990-99368533768-69494881700 | 92736175277-32828338665-47548678925-35112523595-99270925935-50009548284-07458205883-48817201800-26127652859-35595913518 | | 1 | 00059584344-80865914534-06944141263-71034194819-41225372610 | 10605791058-03493906918-64934487291-20725760796-45864390924-78283400000-05423546275-70094329459-55378101721-04884213222 | +-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.11 sec) mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; ERROR 1114 (HY000): The table '/tmp/#sql1967_9_3' is full mysql> mysql> I have checked and 8.0.18 does not have the same behaviour. That means that this is a regression bug in 8.0.19 and so, it should get a severity of S2. Verified as reported.
[6 Apr 2020 14:58]
Dean Zhou
Hi, Sinisa Milivojevic:
Thank you for verification, I can reproduce in 8.0.16, 8.0.18, 8.0.19. The temptable_use_mmap variable introduced by 8.0.16.
// 8.0.16 source code
template <class T> inline void *Allocator<T>::mem_fetch(size_t bytes) {
...
if (t == Mem_type::RAM) {
ptr = mem_fetch_from_ram(bytes);
if (ptr == nullptr) {
throw Result::OUT_OF_MEM;
}
} else {
DBUG_ASSERT(t == Mem_type::DISK);
if (!temptable_use_mmap) { // if temptable_use_mmap = 0, it will throw RECORD_FILE_FULL
throw Result::RECORD_FILE_FULL;
}
ptr = mem_fetch_from_disk(bytes);
if (ptr == nullptr) {
throw Result::RECORD_FILE_FULL;
}
}
...
}
I found that the TemptableAggregateIterator::Init() and end_update() did not catch the RECORD_FILE_FULL error returned by table()->file->ha_update_row();
We shoud catch RECORD_FILE_FULL error, then convert temptable to disk table.
[7 Apr 2020 12:11]
MySQL Verification Team
Thank you, Mr. Zhou. We shall make your findings available to our developers.
[21 Apr 2021 13:32]
MySQL Verification Team
Setting the correct severity.
[27 Apr 2021 7:22]
Simon Mudd
Seen up to 8.0.23 with sys queries as simple as: user@host [(none)]> select user, current_memory from sys.user_summary where user="some_username"; ERROR 1114 (HY000): The table '../tmp/#sqlfe4_b9deb6_6' is full user@host [(none)]> This is a surprising error for such a query and if it's expected (I guess now it is) then a comment on how to work around it or what is going on would be useful in the docs.
[6 May 2021 8:21]
MySQL Verification Team
Workaround: set session internal_tmp_mem_storage_engine=Memory;
[10 Jun 2021 11:41]
Todd McGuinness
Any updates on this? We are still experiencing this on version 8.0.25...
[10 Jun 2021 12:39]
MySQL Verification Team
Hi Mr. Zhou, Thank you for your enquiry. MySQL Verification team is not informed on the scheduling or any progress in relation to the verified bugs, so we are not able to update this page. When the bug is fixed, then another department will close this report with a comment on which MySQL version will contain the fix. Meanwhile, you have an easy workaround for the problem.
[30 Jul 2021 21:16]
Karl Johansson
I am also experiencing this issue on 8.0.23. Super annoying. The query is an analytics query that does GROUP BY on 14 columns. Thank you so much for the workaround with set session internal_tmp_mem_storage_engine=Memory; as this resolves the problem and the query completes in just 15 seconds, but there really does seem to be a bug here. I have over 500 gigs of tmp space available and 128 gigs of RAM and didn't find a single setting that helped regarding heap/tmp table size except for the workaround listed above.
[2 Aug 2021 12:15]
MySQL Verification Team
Hi, There is no doubt that there is a bug here. That is why this is a verified bug with a high severity !!!!
[16 Aug 2021 14:30]
Manoj Soni
Hi, Even i am facing this same issue on my production 8.0.25. But weird part is cannot find on my other two instances with the same version 8.0.25. Two questions 1. Suppose if we will set this variable from SET SESSION internal_tmp_mem_storage_engine=MEMORY; to globally ? what will be the impact if changing the value to MEMORY ? 2. When will this bug get fix ?
[17 Aug 2021 11:42]
MySQL Verification Team
Thank you for your enquiry. MySQL Verification team is not informed on the scheduling or any progress in relation to the verified bugs, so we are not able to update this page. When the bug is fixed, then another department will close this report with a comment on which MySQL version will contain the fix. Meanwhile, you have an easy workaround for the problem. Regarding your question on switching to MEMORY, there is a separate variable that controls when is memory used and when disk for the temporary tables. Do note that this is the amount of RAM that will be allocated to each thread that requires temporary table in order to resolve the query.
[17 Aug 2021 12:02]
MySQL Verification Team
Actually, there could be some news coming in the queue.
[17 Aug 2021 12:02]
MySQL Verification Team
Actually, there could be some news coming in the queue.
[18 Aug 2021 18:10]
Daniel Price
Posted by developer: ixed as of the upcoming 8.0.27 release: A query that used a temporary table for aggregation exhausted the memory available to the TempTable storage engine, causing an update operation to fail with a table is full error.
[19 Aug 2021 11:31]
MySQL Verification Team
Thank you, Daniel.
[1 Oct 2021 13:01]
Derli Dias Campos Junior
Thanks for the workaround, I will deploy this on my prod server. I just have one question, by running this workaround, won't it be only for the current session? set session internal_tmp_mem_storage_engine=Memory; Then, if I run this: SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; The outcome will differ before and after I run the first command, Before: +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | internal_tmp_mem_storage_engine | TempTable | +---------------------------------+-----------+ After: SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | internal_tmp_mem_storage_engine | MEMORY | +---------------------------------+--------+ If I try to make it global and persistent, by running this: SET GLOBAL internal_tmp_mem_storage_engine=MEMORY; The value of the variable won't change
[4 Oct 2021 12:15]
MySQL Verification Team
Hi, As long as you use SET LOCAL ........... that value will be applied only to the current session, until it is closed or if the same variable is reset.
[4 Oct 2021 13:27]
Derli Dias Campos Junior
Thanks for replying. It only works if I do "set session", "set glocal" didn't work. If I close the shell, and re-open it, the value of the variables goes back to what it was before. I want to make it permanent.
[4 Oct 2021 13:32]
Derli Dias Campos Junior
"Set global" I meant. -------- ysql> SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | internal_tmp_mem_storage_engine | TempTable | +---------------------------------+-----------+ 1 row in set (0.01 sec) mysql> SET GLOBAL internal_tmp_mem_storage_engine=MEMORY; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | internal_tmp_mem_storage_engine | TempTable | +---------------------------------+-----------+ 1 row in set (0.01 sec) mysql> set session internal_tmp_mem_storage_engine=Memory; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | internal_tmp_mem_storage_engine | MEMORY | +---------------------------------+--------+ 1 row in set (0.01 sec) -------- As you can see, it only works/changes when I run "set session" and not "SET GLOBAL"
[4 Oct 2021 13:49]
MySQL Verification Team
Hi Mr. Dias Campos, What you describe is exactly the expected behaviour. That is how local / global are designed to work and it is described in our Reference Manual.
[5 Oct 2021 1:59]
Robert Roland
I can also confirm that I am experiencing this issue in a group by query in 8.0.23.
[5 Oct 2021 2:00]
Robert Roland
I can also confirm that I am experiencing this issue in a group by query in 8.0.23.
[13 Oct 2021 11:53]
Lyle Hopkins
I just hit into this issue on 8.0.26 Is the internal_tmp_mem_storage_engine=MEMORY workaround suitable for large queries? I suspect not as it'll either be limited by RAM or hit this issue when it converts from RAM to disk temp table.
[13 Oct 2021 12:58]
MySQL Verification Team
Hi, Last question is not a report of the bug, but a request for the free support. We do not provide free support. It is also totally unrelated to this bug report.
[15 Oct 2021 15:49]
Aviad Pineles
I am most likely affected by this too - in my experience this is not closed. I am using MySQL 8.0.26 on CentOS and am getting tmp table full errors on big queries randomly. So more often than not the queries succeed, but sometimes I get the tmp table full error. The hosting machine has 64G RAM and over 500GB of free space on the data disk which is SSD.
[12 Nov 2021 12:20]
Derli Dias Campos Junior
I have moved my tempdir to a different volume, the exact same volumes the Databases are stored, because before, the databases were stored in a volume and the tempdir in a different volume, now they are in the exact same volume, for a while, the issues stopped, but they have now returned. By the way, I'm running MySQL 8.0.26 on Windows 2016. Any advice anybody?
[12 Nov 2021 12:20]
Derli Dias Campos Junior
I have moved my tempdir to a different volume, the exact same volumes the Databases are stored, because before, the databases were stored in a volume and the tempdir in a different volume, now they are in the exact same volume, for a while, the issues stopped, but they have now returned. By the way, I'm running MySQL 8.0.26 on Windows 2016. Any advice anybody?
[12 Nov 2021 12:30]
Derli Dias Campos Junior
Also, this is the query I'm running when then the issue for the disk being full occurs. SELECT MIN(open_time), count(open_time) FROM mt4_tradesGROUP BY DATE_FORMAT(open_time, '%Y%M')
[12 Nov 2021 14:01]
MySQL Verification Team
Hi, If you run non-filtered aggregating queries on the large tables, you could run out of disk space. For that purpose, check all your volumes and make sure that each partition that you re using has ample free disk space, at least 10 - 50 Gb. Changing the temporary memory table type or the limit for in-memory intrinsic table sizes will not help. Also, this bug is fixed , so please use our 8.0.27 binaries.
[12 Nov 2021 14:28]
Derli Dias Campos Junior
I will then proceed with upgrading my MySQL version from 8.0.26 to 8.0.27 on a quieter time.
Description: Running a query that is using a temporary table for GROUP BY will throw "the table is XXXXX full" . mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; ERROR 1114 (HY000): The table '/data/mysql5858/tmp/#sql639b7_13_4' is ful How to repeat: 1. test environment sysbench insert 10000 into sbtest1 alter table sbtest1 modify pad varchar(60) NOT NULL DEFAULT ''; set global temptable_max_ram = 2097152; set global temptable_use_mmap = 0; 2. execute sql mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; // Execute twice mysql> update sbtest1 set pad = '69934613059-88090326252-43723189827-95836596353-21488500475' where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 // id = 3549 row is an experience value, When processing this row, temptable will be converted to a disk temporary table, You can get the exact row by printing the log before create_ondisk_from_heap is executed mysql> update sbtest1 set pad = '69934613059-88090326252-43723189827-95836596353-21488500475' where id = 3549; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc limit 0, 10; ERROR 1114 (HY000): The table '/data/mysql5858/tmp/#sql639b7_13_4' is full mysql> Suggested fix: the gdb stack: temptable::Memory<(temptable::Source)0>::allocate (bytes=<optimized out>) at /data/mysql-8.0.19-official/storage/temptable/include/temptable/memutils.h:132 132 if (!temptable_use_mmap) { (gdb) p temptable_use_mmap $2 = false (gdb) n 133 throw Result::RECORD_FILE_FULL; (gdb) bt #0 temptable::Memory<(temptable::Source)0>::allocate (bytes=<optimized out>) at /data/mysql-8.0.19-official/storage/temptable/include/temptable/memutils.h:133 #1 temptable::allocate_from (size=<optimized out>, src=<optimized out>) at /data/mysql-8.0.19-official/storage/temptable/include/temptable/block.h:282 #2 temptable::Block::Block (memory_source=<optimized out>, size=<optimized out>, this=<optimized out>) at /data/mysql-8.0.19-official/storage/temptable/include/temptable/block.h:307 #3 temptable::Allocator<unsigned char, temptable::AllocationScheme::Exponential>::allocate (n_elements=128, this=0x7fa7ec0a7f80) at /data/mysql-8.0.19-official/storage/temptable/include/temptable/allocator.h:371 #4 temptable::Row::copy_to_own_memory (this=this@entry=0x7fadc47e8320, columns=std::vector of length 3, capacity 3 = {...}, mysql_row_length=<optimized out>) at /data/mysql-8.0.19-official/storage/temptable/src/row.cc:74 #5 0x00000000023bc4cf in temptable::Table::update (this=0x7fa7ec0a7f80, mysql_row_old=<optimized out>, mysql_row_new=0x7fa7ec1197f8 "", target_row=0x7fa7e69129c8) at /data/mysql-8.0.19-official/storage/temptable/src/table.cc:194 #6 0x0000000001026a77 in handler::ha_update_row (this=0x7fa7ec118168, old_data=0x7fa7ec11a008 "", new_data=0x7fa7ec1197f8 "") at /data/mysql-8.0.19-official/sql/handler.cc:7889 #7 0x0000000000fd01bd in TemptableAggregateIterator::Init (this=0x7fa7ec05c5e0) at /data/mysql-8.0.19-official/sql/composite_iterators.cc:1408 #8 0x0000000001009de3 in filesort (thd=0x7fa7ec000d70, filesort=0x7fa7ec05c3f0, source_iterator=0x7fa7ec05c5e0, fs_info=fs_info@entry=0x7fa7ec05c680, sort_result=sort_result@entry=0x7fa7ec05c708, found_rows=found_rows@entry=0x7fadc47e89f8) at /data/mysql-8.0.19-official/sql/filesort.cc:416 #9 0x0000000000d519f5 in SortingIterator::DoSort (this=this@entry=0x7fa7ec05c640, qep_tab=qep_tab@entry=0x7fa7ec05bb40) at /data/mysql-8.0.19-official/sql/sorting_iterator.cc:636 #10 0x0000000000d51b02 in SortingIterator::Init (this=0x7fa7ec05c640) at /data/mysql-8.0.19-official/sql/sorting_iterator.cc:498 #11 0x0000000000fcc286 in LimitOffsetIterator::Init (this=0x7fa7ec05c7c8) at /data/mysql-8.0.19-official/sql/composite_iterators.cc:102 #12 0x0000000000e99ae8 in SELECT_LEX_UNIT::ExecuteIteratorQuery (this=this@entry=0x7fa7ec00a278, thd=thd@entry=0x7fa7ec000d70) at /data/mysql-8.0.19-official/sql/sql_union.cc:1498 #13 0x0000000000e9bdd9 in SELECT_LEX_UNIT::execute (this=this@entry=0x7fa7ec00a278, thd=thd@entry=0x7fa7ec000d70) at /data/mysql-8.0.19-official/sql/sql_union.cc:1588 #14 0x0000000000e2f5db in Sql_cmd_dml::execute_inner (this=0x7fa7ec05ae88, thd=0x7fa7ec000d70) at /data/mysql-8.0.19-official/sql/sql_select.cc:910 #15 0x0000000000e38a38 in Sql_cmd_dml::execute (this=0x7fa7ec05ae88, thd=0x7fa7ec000d70) at /data/mysql-8.0.19-official/sql/sql_select.cc:715 #16 0x0000000000deb1b8 in mysql_execute_command (thd=thd@entry=0x7fa7ec000d70, first_level=first_level@entry=true) at /data/mysql-8.0.19-official/sql/sql_parse.cc:4478 #17 0x0000000000ded605 in mysql_parse (thd=thd@entry=0x7fa7ec000d70, parser_state=parser_state@entry=0x7fadc47ea4d0) at /data/mysql-8.0.19-official/sql/sql_parse.cc:5288 #18 0x0000000000dee819 in dispatch_command (thd=thd@entry=0x7fa7ec000d70, com_data=com_data@entry=0x7fadc47eabc0, command=COM_QUERY) at /data/mysql-8.0.19-official/sql/sql_parse.cc:1777 #19 0x0000000000df065c in do_command (thd=thd@entry=0x7fa7ec000d70) at /data/mysql-8.0.19-official/sql/sql_parse.cc:1275 #20 0x0000000000effa68 in handle_connection (arg=arg@entry=0x4fe3c40) at /data/mysql-8.0.19-official/sql/conn_handler/connection_handler_per_thread.cc:302 #21 0x00000000023355c9 in pfs_spawn_thread (arg=0x526a530) at /data/mysql-8.0.19-official/storage/perfschema/pfs.cc:2854 #22 0x00007fade24cadd5 in start_thread () from /lib64/libpthread.so.0 #23 0x00007fade063f02d in clone () from /lib64/libc.so.6 table()->file->ha_update_row will throw HA_ERR_RECORD_FILE_FULL when temptable consumed memory exceed temptable_max_ram, see the gdb stack for details on memory allocation failure. The correct way is that if error code is HA_ERR_RECORD_FILE_FULL, then TemptableAggregateIterator::Init add create_ondisk_from_heap function, convert temptable to innodb disk temp table, then call table()->file->ha_index_read_map position cursor, finally execute table()->file->ha_update_row again bool TemptableAggregateIterator::Init() { ... } else { for (ORDER *group = table()->group; group; group = group->next) { Item *item = *group->item; item->save_org_in_field(group->field_in_tmp_table); /* Store in the used key if the field was 0 */ if (item->maybe_null) group->buff[-1] = (char)group->field_in_tmp_table->is_null(); } const uchar *key = m_temp_table_param->group_buff; group_found = !table()->file->ha_index_read_map( table()->record[1], key, HA_WHOLE_KEY, HA_READ_KEY_EXACT); } if (group_found) { // Update the existing record. (If it's unchanged, that's a // nonfatal error.) restore_record(table(), record[1]); update_tmptable_sum_func(m_join->sum_funcs, table()); int error = table()->file->ha_update_row(table()->record[1], table()->record[0]); if (error != 0 && error != HA_ERR_RECORD_IS_THE_SAME) { PrintError(error); return true; } continue; } ... }