Bug #79125 Server logs "File Descriptor n exceedeed FD_SETSIZE" and drops connection
Submitted: 4 Nov 2015 19:00 Modified: 16 Nov 2015 16:53
Reporter: Ralph Dosser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S4 (Feature request)
Version:mysqld Ver 5.7.9 for osx10.9 on x86_64 OS:Mac OS X
Assigned to:

[4 Nov 2015 19:00] Ralph Dosser
Description:
When connecting to a MySQL server on localhost, a PHP script throws this exception:

11/04/15 18:36:37.973 iaException [error] <pre>306 Uncaught Exception: E_WARNING mysqli::real_connect(): MySQL server has gone away at /active/web/webroot/framework/common/iaDatabase.php:281 

At the same time, MySQL logs this:

2015-11-04T18:36:37.017094Z 0 [Warning] File Descriptor 1048 exceedeed FD_SETSIZE=1024
2015-11-04T18:36:37.973278Z 0 [Warning] File Descriptor 1049 exceedeed FD_SETSIZE=1024

NOTE that this is copy/pasted, and the misspelled "exceedeed" is exactly as it appears.

A mysql CLI client running on the same host then must reconnect:

mysql> show status ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    107

The server must be restarted before PHP can connect again.

There is a pair of queries run by the PHP script that, when removed, prevent this from occurring:

        $queries = array(
            'existingConstraints' => '
                SELECT CONSTRAINT_SCHEMA AS `dbName`, TABLE_NAME AS `tableName`, CONSTRAINT_NAME `keyName`
                FROM information_schema.TABLE_CONSTRAINTS
                WHERE CONSTRAINT_SCHEMA LIKE "dbprefix_%" AND CONSTRAINT_NAME LIKE "fk%"',
            'existingKeys' => '
                SELECT TABLE_SCHEMA AS `dbName`, TABLE_NAME AS `tableName`, INDEX_NAME `keyName`
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA LIKE "dbprefix_%" AND INDEX_NAME LIKE "fk%"',
        );

How to repeat:
This only occurs within our PHP framework, so I have no way to tell you how to reproduce. Sorry about that.

Suggested fix:
We can only avoid this by removing those queries, which at some point will be required.
[10 Nov 2015 17:01] Sinisa Milivojevic
Hi,

Is your server running on OS X ??? If yes, then please increase the number of files descriptors in the shell from which you are running the server, not the client. Increase it to the upper limit allowed by the shell. If this repeats, increase the shell maximum to the hard limit as set in OS X.

If the problem occurs again, in spite of your efforts, please send us your configuration file(s). Also, check with P_S the number of files in usage when this happens.
[10 Nov 2015 17:03] Sinisa Milivojevic
Hi,

1024 is too low a number, anyway. Increase it at least to 4096 !!!!
[13 Nov 2015 5:42] Davi Arnaut
Sinisa,

FD_SETSIZE is a constant. Any fd whose numerical value is greater than FD_SETSIZE cannot be used with select(). It appears select() is used in Mac OS X.
[13 Nov 2015 5:54] Davi Arnaut
Here is a couple of links to make things easier to understand:

- https://github.com/mysql/mysql-server/commit/662a96e15 

Force use of select() on Mac OS X instead of poll().

- https://github.com/mysql/mysql-server/commit/de3d16186

Add check for FD_SETSIZE required for select() to work properly.

—

Basically, these changes restricted the maximum number of file descriptors on Mac to FD_SETSIZE (1024).
[13 Nov 2015 16:59] Sinisa Milivojevic
Davi,

Thank you for your contribution. As you might guess yourself, this bug is a duplicate of # 69903.

However, if the value is constant, the first thing that could be done is to see whether Apple has released OS X that would have that constant higher, or have changed it into a kernel value.

OS X is closed source, so, it is not possible to rebuild it from source , by the user.

Davi,

Any other ideas ???
[13 Nov 2015 21:26] Davi Arnaut
Don't use select()? OS X has plenty other equivalent APIs like kevent, poll, etc. I'm also sure some users would prefer having some KILL issues over being limited to 1024.
[16 Nov 2015 16:53] Sinisa Milivojevic
Davi has shown a manner to improve the behavior on OS .

I completely understood his thoughts, so this report is verified , but as feature request, due to the fact that OS X will have to get its own, separate communication layer.
[9 Feb 2016 10:29] Richard Gaunt
My colleague and I are both getting this error as well. It effectively is breaking mysql on osx for complicated database operations. 

And for some reason the fix in changing the variable on osx is not working and so we continue to get this problem.
[9 Feb 2016 14:30] Sinisa Milivojevic
Ralph,

I do not doubt that you and others MySQL on OS X have troubles when a very large number of descriptors is used on OS X. I am testing and developing MySQL only on OS X, but I never have many file descriptors used.

So, the only thing that you can do is to check with OS X versions larger then 10.9, whether that constant has increased and then use that version. If not, you will have to contact Apple and ask for the increase.

We, at MySQL, can not change anything, as it would require us to write a whole new communication layer, JUST for OS X. That endeavor would take many men-months !!!
[20 Feb 2016 22:30] Godofredo Miguel Solorzano
http://bugs.mysql.com/bug.php?id=80452 marked as duplicate of this one.
[26 May 2016 19:02] Derek Jones
What is the reason for not switching back to using poll() on OS X? Granted I do not have a solid understanding of the internals, but everything I can find in searching shows that Darwin's poll() has implemented POLLHUP since OS X 10.4.4.

http://www.greenend.org.uk/rjk/tech/poll.html
https://curl.haxx.se/mail/lib-2007-02/0071.html

As a temporary workaround for my local environment, I've reduced my interactive_timeout and wait_timeout down to 600 seconds, in hopes that the file descriptors will drop off fast enough to never exceed 1024. I'm not sure if it has worked, since it's very situational to trigger, and is obviously not a good solution for production servers. Any insight on this?
[27 May 2016 14:29] Sinisa Milivojevic
We have only a separate protocol for Windows. We do not have resources to write separate protocol for each *nix version.

Anyway, this remains a feature request, but real solution is in Apple hands.  It would be nice to know how big is this constant in the last OS X.
[27 May 2016 15:00] Derek Jones
FD_SETSIZE in the latest OS X / Darwin is still 1024: http://opensource.apple.com/source/xnu/xnu-3248.20.55/bsd/sys/_types/_fd_def.h

I don't think I'm suggesting writing a new protocol, I'm wondering if forcing OS X to use select() as an exception in this commit:

https://github.com/mysql/mysql-server/commit/662a96e15

is still necessary? In other words, can't it use the same methods as every other Unix system since poll() seems to have POLLHUP implemented since 10.4.4? If so, then the FD_SETSIZE is moot because MySQL would not be using select() as an exception for that OS.

OS X man page for poll() for ref:

https://developer.apple.com/library/ios/documentation/System/Conceptual/ManPages_iPhoneOS/...
[30 May 2016 7:55] Ståle Deraas
Posted by developer:
 
Hi Derek,

We are experimenting with an improvement here using kevent. It will still be some time before we conclude, as there are other bindings. But will update the bugreport when the findings are conclusive.
[12 Jul 2016 7:17] Ståle Deraas
Posted by developer:
 
Hi again Derek,

We now have a patch with kevent that we are testing in our dev branch.
[7 Oct 2016 0:35] Chris Jett
Wondering what the status is on this bug on OS X.  It's preventing use of MySQL in a high traffic environment on OS X.
[12 Oct 2016 8:36] Ståle Deraas
Posted by developer:
 
Hi Chris,

We fixed this in 8.0 by introducing the use of kqueue. This build on another change in the code, which will make the backport to 5.7 non-trivial and inherently pose some risk. So we are reluctant to backport to a GA version.
[12 Oct 2016 15:11] Chris Alemany
When is 5.8 expected to be released?