Bug #95328 Crashes and out of memory Messages
Submitted: 10 May 2019 10:35 Modified: 23 May 2019 14:25
Reporter: Frederic Steinfels Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.16 OS:Fedora (official rpm)
Assigned to: CPU Architecture:x86
Tags: crash, out of memory

[10 May 2019 10:35] Frederic Steinfels
Mysql should never crash. However, it is currently reporting random (?) out of memory messages and does restarts from time to time.

How to repeat:
This is the statement:

SELECT GROUP_CONCAT(DISTINCT pid) FROM ( SELECT   pa3.`product_id` AS pid FROM   accessories AS ac,   pactive AS pa3,   pricecache AS pc WHERE   (ac.from_pactive_id=1043755)   AND   ac.to_pactive_id=pa3.id   AND   pa3.`id`=pc.`pactive_id`   AND         pc.`hd`='1'         AND         (pa3.`webshop`='1' OR @icadminid) UNION ALL  SELECT   pa2.`product_id` AS pid FROM   categoryaccessories AS ca,   categorydefaults AS cd,   pactive AS pa2,   pricecache AS pc2 WHERE   (pa2.id=1043755)   AND   (pa2.cat1_id=cd.cat1_id AND pa2.cat2_id=cd.cat2_id AND pa2.cat3_id=cd.cat3_id)   AND   cd.id=ca.categorydefaults_id   AND   ca.product_id=pa2.id   AND   pa2.`id`=pc2.`pactive_id`   AND         pc2.`hd`='1'         AND         (pa2.`webshop`='1' OR @icadminid) ) AS something;

Sometimes it is correctly returning:

| GROUP_CONCAT(DISTINCT pid)                              |
| 1043757,1043758,1043759,1043760,1043762,1043764,1043765 |
1 row in set (1.07 sec)

And sometimes it is returning:

ERROR 5 (HY000): Out of memory (Needed 671088668 bytes)

I assume this error occurs in conjunction with some other things happening in the background (memory corruption, excessive use of some buffers?) and is very hard for me to debug.

In the journal I read:

May 10 12:22:05 www.highdefinition.ch kernel: Out of memory: Kill process 2595 (mysqld) score 314 or sacrifice child
May 10 12:22:05 www.highdefinition.ch kernel: Killed process 2595 (mysqld) total-vm:49342940kB, anon-rss:26898440kB, file-rss:0kB, shmem-rss:0kB
May 10 12:22:05 www.highdefinition.ch kernel: oom_reaper: reaped process 2595 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

Is it possible to somehow dump the sql statements that were being executed in the case of such a kill?
[10 May 2019 10:35] Frederic Steinfels
mysqld error log

Attachment: mysql.log (text/plain), 11.01 KiB.

[11 May 2019 17:42] Frederic Steinfels
I have reported similar bugs in the past. Some where my fault (underdefined WHERE Statement resulting in too many search results / memory exhaustion), some others where real bugs and have been fixed so far.
In 8.0.16 there seems to be a new problem eating away my 64gigs of memory at about 500MB/hour until mysqld gets killed. 

With 8.0.15 I had the

2019-03-17T11:29:37.087498Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files

error. You told me to change the tmp table format to
which I did. With 8.0.16 I changed it back, ie added the comment sign.

Since then, it takes about 1.5 days for mysql to be killed because of memory exhaustion:

2019-05-02T06:08:31.347848Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-04T14:06:07.723600Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-04T14:06:07.741418Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-05T03:18:49.619089Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-05T03:18:49.633238Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-06T23:01:29.211684Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-06T23:01:29.850448Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-07T02:16:51.546462Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-07T02:16:51.557097Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-09T01:13:50.495064Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-09T01:13:50.506618Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-10T10:22:57.340767Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-10T10:22:57.349472Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-05-11T14:12:01.945310Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-05-11T14:12:01.957274Z 0 [System] [MY-010232] [Server] Crash recovery finished.
[13 May 2019 14:22] MySQL Verification Team

Thank you for your bug report.

Since 8.0.16, you can not use MEMORY engine any more. You have to set  internal_tmp_disk_storage_engine  to InnoDB only.

If you continue to see problems after that, then you should consider seriously to tune your configuration variables , like internal-tmp-mem-storage-engine, tmp_table_size, etc ......
[15 May 2019 17:03] Frederic Steinfels
Thanks for your feedback. When starting mysqld, its using 30gb. After 1.5 days, it is using maybe 50gb and then it is getting killed. So it seems to be a bug. I certainly can upload more debug files. I have been using older versions of mysqld with the same settings without running nout of memory.
[15 May 2019 18:45] Frederic Steinfels
I do not think it has anything to do with the settings as older versions did not continually loose memory. The only relevant variables I am setting are these:

binlog_format = row
max_binlog_size  = 100M
[16 May 2019 12:13] MySQL Verification Team

As I wrote before, this is a simple matter of misconfiguration. Of course , 8.0 will itself use more memory, but these settings clearly explain the cause of your problem.


Not a bug.
[17 May 2019 22:08] Frederic Steinfels
I understand you think those variables are excessively large and that at some point, because those buffers are allocated, mysqld will get killed. So if I lowered those buffers and mysqld would still get killed on a regular base because its using up all available memory, would you then agree that we have a possible memory leak worth investigating?

I do not really want to lower those:

As I had some SQL Statements failing at some point and then I increased those buffers. However Group_concat_max_len could be lowered to maybe 64MB.

And those here, do you think 1G is ok instaed of 10G?
[20 May 2019 12:39] MySQL Verification Team

You should truly look at session variables that are defining memory to be allocated.

Our Reference Manual has all that you need in order to do the tuning.

Regarding memory leaking, we use many, many tools for so many tests to discover if there is a leak or corruption of memory and the like. We do not publish new release without all those tests.

So, yes, this report could be treated as memory leak if you come up with test results from such a tool.

Otherwise, this looks only just like the request for free support. And this is not a forum for free support.
[21 May 2019 12:50] MySQL Verification Team

Everything that I wrote to you still stands.

If you come with a repeatable test case that always lead to OOM situation, or if you come up with memory leak from some appropriate tool, we would welcome your bug report and we would verify it , provided that we can repeat it.

Thank you for your contribution.
[23 May 2019 14:25] Frederic Steinfels
I have to add that earlier this year I have already posted a similar bug that is closed now and in this bug, there is some logging. I found that I had an sql statement containing an error and therefore creating a excessive amount of results and allocating too much memory and I attributed the crash to that statement. However, I do no longer have such long running and excessive statements anymore that could explain the crashes therefore I made a new report.