How to Fix “Out of resources” Error in MySQLDump on Windows: File Handle Exhaustion Solution


1 views

When executing mysqldump on Windows servers, some administrators encounter the frustrating error:

mysqldump: Got error: 23: Out of resources when opening file '.\\db\\sometable.MYD' (Errcode: 24) when using LOCK TABLES

This typically occurs during a command like:

mysqldump -u user -p"pass" --lock-tables --default-character-set=latin1 -e --quick databasename > "query.sql"

The error stems from Windows system limitations regarding file handles. When mysqldump attempts to lock multiple tables simultaneously (via --lock-tables), Windows may refuse to open additional files due to:

  • Per-process file handle limit reached (default 512 handles in Windows)
  • System-wide file handle exhaustion
  • Inherited handle leaks from parent processes

1. Temporary Workaround

For immediate relief:

mysqldump -u user -p"pass" --single-transaction --default-character-set=latin1 -e --quick databasename > "query.sql"

Key changes:

  • Replace --lock-tables with --single-transaction for InnoDB tables
  • Removes the need for simultaneous file locks

2. Permanent System Configuration

Modify Windows registry to increase handles:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL]
"ObjectName"="LocalSystem"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]
"Last Counter"=dword:00000ed4
"Last Help"=dword:00000ed5

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"PagedPoolSize"=dword:ffffffff
"SystemPages"=dword:ffffffff

3. MySQL Server Configuration

Add to my.ini:

[mysqld]
table_open_cache=400
open_files_limit=5000
innodb_open_files=3000

[mysqldump]
quick
max_allowed_packet=128M

For persistent cases:

# Check current handle usage
handle.exe -p mysqld.exe

# Alternative dump method
mysql -u user -p"pass" -e "SELECT * FROM db.tbl" --quick > partial_export.txt
  • Schedule smaller dumps during off-peak hours
  • Implement chunked exports for large tables
  • Consider using mysqlpump for parallel exports

While performing routine database backups using mysqldump on a Windows server, I encountered this persistent error:

mysqldump: Got error: 23: Out of resources when opening file '.\\db\\sometable.MYD' (Errcode: 24) when using LOCK TABLES

What's puzzling is that the exact same command works flawlessly on a dozen other servers with identical database structures (all containing 85 tables). Here's the command I was running:

mysqldump -u user -p"pass" --lock-tables --default-character-set=latin1 -e --quick databasename > "query.sql"

After encountering the error, I tried several standard approaches:

  • Restarted MySQL service - no effect
  • Reduced table_cache from 64 to 32
  • Lowered max_connections from 30 to 10

The parameter adjustments only changed which table triggered the error, moving the problem to a different table but not resolving it.

The key to solving this lies in understanding Errcode: 24. In Windows systems, this corresponds to:

ERROR_TOO_MANY_OPEN_FILES

Windows imposes strict limits on file handles per process. The default is typically 512 handles, which mysqldump might exceed during large database operations.

Here are the most reliable fixes I discovered:

// Solution 1: Increase Windows handle limit
// Run as administrator in cmd:
editbin /nologo /largeaddressaware C:\path\to\mysqldump.exe

// Solution 2: Alternative mysqldump approach
mysqldump -u user -p"pass" --single-transaction --skip-lock-tables 
--default-character-set=latin1 -e --quick databasename > "query.sql"

Key parameter changes:

  • --single-transaction: Uses transaction isolation instead of locks
  • --skip-lock-tables: Avoids the file handle intensive locking mechanism

For servers consistently experiencing this issue, modify MySQL's my.ini:

[mysqld]
table_open_cache=2000
open_files_limit=4000

[mysqldump]
quick
max_allowed_packet=512M

Remember to restart MySQL after configuration changes.

For particularly stubborn cases, consider these alternatives:

// Option 1: Split the backup by tables
FOR /F "tokens=1" %i IN ('mysql -uuser -ppass -e "SHOW TABLES" databasename') DO (
  mysqldump -u user -ppass --single-transaction databasename %i > %i.sql
)

// Option 2: Use mysqlpump (MySQL 5.7+)
mysqlpump -u user -ppass --default-parallelism=4 --compress-output=LZ4 databasename > backup.lz4