MySQL Bugs: #79125: Server logs "File Descriptor n exceedeed FD

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:	MacOS
Assigned to:		CPU Architecture:	Any

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.

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.

Hi,

1024 is too low a number, anyway. Increase it at least to 4096 !!!!

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.

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).

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 ???

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.

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.

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.

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 !!!

http://bugs.mysql.com/bug.php?id=80452 marked as duplicate of this one.

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?

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.

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/...

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.

Posted by developer:
 
Hi again Derek,

We now have a patch with kevent that we are testing in our dev branch.

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.

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.

When is 5.8 expected to be released?

Anyone have solutions for this issue for MySQL 5.7.25?  macOS 10.14

I have been getting this issue of several version of MacOS and various versions of mysql. I was still getting this issue on 10.15.4 and mysql 5.7.27. However I have found that if I limit the open table cache size then I no longer have this issue. I have set it to 100 and have not noticed any detrimental effects. To set the table open cache limit, add this line to your mysqld section of the configuration:

table_open_cache = 100