📋

Description

MySQL Error 1587 indicates that the server has reached its configured limit for the number of open files, preventing it from executing the requested operation. This typically occurs when the operating system or MySQL's own configuration restricts the maximum number of file descriptors available to the MySQL process.

💬

Error Message

Too many files opened, please execute the command again

🔍

Known Causes

3 known causes

⚠️

OS File Descriptor Limit

The underlying operating system's global or per-process limit for open file descriptors is set too low for the MySQL server's current workload.

⚠️

MySQL Configuration Limits

MySQL's own configuration parameters, such as `open_files_limit` or `table_open_cache`, are not adequately sized for the number of tables or connections.

⚠️

High Concurrency/Workload

An unusually high number of concurrent connections, complex queries, or operations involving many temporary tables can rapidly exhaust available file handles.

🛠️

Solutions

3 solutions available

1. Temporarily Increase Open File Limit easy

Quickly increase the open file limit for the current session or the entire system.

1

Check the current open file limit for the MySQL process. You can do this by finding the MySQL process ID (PID) and then checking its limits.

ps aux | grep mysqld
cat /proc/<MYSQL_PID>/limits | grep 'Max open files'

2

If the limit is too low, you can temporarily increase it for the current shell session. This is useful for testing or immediate relief.

ulimit -n 65536

3

For a more persistent temporary increase (until the next reboot), you can edit the system-wide limits configuration.

sudo vi /etc/security/limits.conf

4

Add the following lines to the end of the file, replacing 'mysql' with the user running MySQL if it's different, and '65536' with your desired limit.

* soft nofile 65536
* hard nofile 65536
mysql soft nofile 65536
mysql hard nofile 65536

5

After saving the file, you will need to restart the MySQL service for the changes to take effect.

sudo systemctl restart mysql

2. Permanently Configure System-Wide Open File Limits medium

Configure the operating system to permanently allow a higher number of open files for MySQL.

1

Identify the user that the MySQL server runs as. This is typically 'mysql'.

ps aux | grep mysqld

2

Edit the systemd service file for MySQL to set the open file limit. The exact path may vary depending on your Linux distribution. Common locations include `/etc/systemd/system/mysql.service.d/override.conf` or by editing the main service file.

sudo systemctl edit mysql.service

3

Add the following content to the override file. This will set the `LimitNOFILE` to 65536. You can adjust this value as needed.

[Service]
LimitNOFILE=65536

4

Save and close the file. Then, reload the systemd daemon and restart MySQL.

sudo systemctl daemon-reload
sudo systemctl restart mysql

5

Verify the change by checking the limits of the MySQL process again.

ps aux | grep mysqld
cat /proc/<MYSQL_PID>/limits | grep 'Max open files'

3. Optimize MySQL Configuration medium

Reduce the number of open files by tuning MySQL's settings.

1

Analyze your MySQL configuration (`my.cnf` or `my.ini`). Look for settings that might be contributing to excessive file usage.

SHOW VARIABLES LIKE '%open%';

2

Consider reducing the `table_open_cache` and `table_definition_cache` values if they are set excessively high. These caches hold open file handles for table definitions and structures. Start with a lower value and gradually increase if performance degrades.

SET GLOBAL table_open_cache = 2000;
SET GLOBAL table_definition_cache = 1000;

3

Review your application's database connection management. Ensure that connections are being properly closed when no longer needed. Excessive unclosed connections can lead to numerous file descriptors being held open (sockets are also files).

N/A (Application Code Review)

4

Check for the presence of many temporary files created by MySQL, which could indicate issues with disk space or intensive temporary table usage. Adjust `tmpdir` if necessary.

SHOW VARIABLES LIKE 'tmpdir';

5

After making changes to `my.cnf`, remember to restart the MySQL server.

sudo systemctl restart mysql

🔗

Related Errors

5 related errors

MySQL Error 1587: Too Many Open Files