Bug #90902 Select Query With Complex Joins Leaks File Handles
Submitted: 17 May 2018 2:31 Modified: 25 Feb 2019 13:01
Reporter: James Wilson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7, 5.7.22 OS:Ubuntu (Ubuntu 16:04, Open Stack)
Assigned to: CPU Architecture:x86 (2 CPUs, 16GB RAM)

[17 May 2018 2:31] James Wilson
Description:

I am deploying SugarCRM 7.9. We use MySQL 5.7 along with Apache 2.4, PHP 7.1 and Elastic Search 1.7.6. Sugar's Contacts List view is implemented with a relatively complicated query with a number of joins. Each time I run this query, I see leakage of file handles (tmp files) which eventually results in MySQL crashing. The issue can be resolved by restarting the MySQL service (service mysql restart). I am able to replicate this issue reliably on our server, but I don't have access to a separate 5.7 server to confirm it behaves similarly. I have created a replicable test case so hopefully this won't be too hard for you to replicate.

Thx
James

How to repeat:
Here is the test case that demonstrates the failure:
- create a set of tables with data (see Dump20180516.sql)
- make sure that optimizer_switch = block_nested_loop=off in my.cnf (see attached my.cnf)
- start a shell running as root and run the following script to see the number of file handles held open by MySQL:
while true; do date;pgrep mysql | while read pid; do echo mysql has `ls /proc/$pid/fd | wc -l` file descriptors; done; sleep 1; done;
- run the query (see query.sql)
- every time I run the query, I expect to see file handles allocated to MySQL increase temporarily - ie, the number of file handles should bump up while the query is running and then settle back to the original value after the query ends and the tmp files are released
- what actually happens is that the number of file handles bumps up, but never drops back down
- I strongly suspect that memory allocated to MySQL is also leaking, but I have not confirmed this
- after a period of time, MySQL runs out of file handles and crashes. Restarting MySQL resolves the issue until MySQL runs out of handles again.

Notes:
- The above behaviour appears to be similar to that described in:
https://bugs.mysql.com/bug.php?id=47623
- there is a related issue (https://bugs.mysql.com/bug.php?id=69721) with MySQl that has affected us too. The file handles don't leak when we set optimizer_switch = block_nested_loop=on in my.cnf. However, the full query (including commented out elements) runs unacceptably slowly when block_nested_loop is turned on. Note that the test set does not include sufficient data to support the full query

Suggested fix:
- as a partial workaround, I have increased the value of LimitNOFILE to 50000 in /lib/systemd/system/mysql.service. Obviously this workaround is not a permanent workaround as it is a matter of time before MySQL runs out of file handles and the server crashes
- a periodic scheduled restart should be a viable work around for some users, but will be of no use for 24x7 users
[17 May 2018 3:37] James Wilson
MySQL config file

Attachment: my.cnf (application/octet-stream, text), 1.60 KiB.

[17 May 2018 3:37] James Wilson
Query that exhibits the failure

Attachment: Query.sql (application/octet-stream, text), 7.42 KiB.

[17 May 2018 3:40] James Wilson
I have uploaded a file called mysql-bug-data-90902.zip to sftp.oracle.com. This file contains the data set that can be used to replicate the issue.
[17 May 2018 10:04] MySQL Verification Team
Hello James,

Thank you for the report.
I didn't observe any crash etc in my 3 attempts on 5.7.22 but observed that deleted tmp files not cleaned up and thus prevented other operations once open files limit exhausted.

Thanks,
Umesh
[17 May 2018 11:51] James Wilson
Hi Umesh,

You won't see a "crash" until MySQL runs out of file handles. I have to run the query many times before it runs out. Also, when I say "crash", MySQL throws an error in the MySQL logs (error 24 from memory) then refuses to respond to requests to process queries.
[25 Feb 2019 13:01] Jon Stephens
Documented fix in the MySQL 5.7.26 and 8.0.16 changelogs as follows:

    Some queries involving complex joins leaked file handles.

Closed.