MySQL Bugs: #99320: Unexpected restart after continuous querying

Bug #99320	Unexpected restart after continuous querying
Submitted:	22 Apr 2020 8:25	Modified:	25 May 2020 12:00
Reporter:	Jeroen Bourgois	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.19	OS:	MacOS (10.15.3)
Assigned to:		CPU Architecture:	x86

Description:
We have a rather intense query on one of the listing pages within our web application, that also involves paging. So we understand the operation of traversing the pages is heavy, since the paging itself uses limit/offset (thus needing to query twice each time, to get the count).

Regardless, we feel (sorry, we cannot proof this) that since recent versions we are seeing issues we did not have before.

Upon repeatedly refreshing the page we got errors from our middleware. It is an Elixir/Phoenix app, so first Cowboy and then the Erlang DBConnection were reporting generic errors.

We investigated some more and found out that the mysql process suddenly exits and restarts. Stack trace was too long, added in separate file.

Dev machines are running Mac OS X 10.15.3 with mysql 8.0.19.
Staging and production environment running debian 9.12 with mysql 8.0.19.

How to repeat:
We can reproduce the error both on our local dev machines as on the production and staging machines.

We just continue to refresh the page in the browser and then - at random - get an error.

Suggested fix:
We think our mysql settings are not good, but don't have a clue how to tweak them regarding to the crash, since we do not understand why it crashed in the first place.

Stack trace of the crash

Attachment: mysql-crash-stack-trace.txt (text/plain), 10.14 KiB.

We tested our application with Dockerized versions of MySQL 8.0.18 and 8.0.17.

- 8.0.18 renders the same stacktrace as 8.0.19.
- 8.0.17 on the other hand, is showing no errors or crashes whatsoever.

Maybe this information could be valuable in determining the root cause.

We tried to create a simple script to consistently reproduce the error, but we did not succeed. So it might be due to an interplay of one of our Elixir libraries and these specific MySQL versions (*.18 and *.19)

Hi Mr. Bourgois,

Thank you for your bug report.

First of all, we do not debug WWW applications. Second, we can not provide you help with the tuning of your MySQL server.

However, what we do and what we would truly like to do is to repeat the crashing that you observe.

Hence, we need a fully repeatable test case, that will always lead to MySQL crashing. This test case should consist of the set of SQL statements that always lead to MySQL 8.0.19 crash. Since I am using the same macOS as you, it should not be difficult for me to repeat the SQL.

If you can't make a test case, send us a stack trace and I will let you know what command is in question. After that it will be up to you to make a test case.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

For what it's worth, the stack trace in the private attachment looks like
Bug #30674598  (hidden/internal) which is fixed in 8.0.20.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html
"For prepared statements, re-execution could cause a server exit if a cleaned-up materialized temporary table was still being referred to. (Bug #30674598)"