I'm trying to do a mysqldump on a Windows server and I get the following error message :

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

Here's the command I'm running :

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

Restarting the mysql service didn't help.

I always get the message for the same table.

I've tried reducing the table_cache and max_connections variables from 64 to 32 and 30 to 10 respectively but I still get the error only this time for a different table (and from now on the error message is always mentionning the second table).

The same script is running on a dozen other Windows servers having the same database without problems.

All databases have 85 tables.

  • What operating system do you have MySQL running on?
    – gm3dmo
    Jan 20, 2010 at 15:19
  • Windows everywhere. Jan 20, 2010 at 15:43
  • How many tables in the DB? Seems like some kind of file descriptor limit.
    – gm3dmo
    Jan 20, 2010 at 15:53
  • 85 tables in the DB. Jan 20, 2010 at 16:35
  • Informations added to the description. Jan 20, 2010 at 16:44

4 Answers 4


According to here - "OS error code 24: Too many open files" which lines up with the more general error 23 "Out of resources".

So it seems as though you are running out of file handles. This is usually server-end setting/problem, either in MySQL, or in the OS itself.

Perhaps check/adjust the --open-files-limit setting in MySQL itself and see if that helps.

Also, perhaps try running the dump, while no one else is using the DB, with the --single-transaction setting instead of --Lock-File, as several people suggest this will work one table at a time instead of opening them all at once (therefore using less file handles).

Beyond that you'll probably have to find a root cause as to why this particular server is running out of resources. Which would probably involve troubleshooting by disabling as many services/processes as possible and see if the dump goes through. Then figure out from there who the culprit is that's eating too many resources and perhaps not freeing them correctly.

  • 3
    --single-transaction instead of --lock-tables worked. Thanks a lot. Jan 21, 2010 at 14:29
  • Strangely I'm not using InnoDB but it doesn't work without this parameter. I thought --single-transaction was only relevant with innoDB. Jan 21, 2010 at 14:40
  • Nice. :) Glad it helped.
    – techie007
    Jan 21, 2010 at 21:42
  • 6
    --lock-all-tables also works, and doesn't have the inconsistency problems of --single-transaction when working on non-InnoDB tables.
    – freiheit
    Mar 21, 2011 at 16:26
  • 1
    Just to clarify: --single-transaction forces --lock-tables=off. Don't use this on non-transactional tables.
    – b2ag
    Nov 5, 2011 at 15:19

Are you in a position to try it with --single-transaction instead of --lock-tables e.g. the tables are InnoDB and you are not using Cluster tables and that ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE won't happen during the dump? Best confirm this is ok with your MySQL support org if you have one.

I've only tried this on unix but basically if I try with a DB with 2000 tables it fails with error similar to yours e.g. I've used all my open file handles.

  • +1. You solution being the same as techie007 worked too but he answered first so I accept his answer. For the records, I'm not using innoDB. Jan 21, 2010 at 14:31

You may get this error:

MySQL: Errcode: 24 when using LOCK TABLES

...along with other errors when you upgrade to MySQL 5.5 and you are running your backups on Plesk or any other OS executing mysqldump.

To fix:

  1. Edit my.cnf
  2. Add:

  3. Restart MySQL

If you are receiving:

Cannot load from mysql.proc. The table is probably corrupted (1548)

This is a result of an upgrade to 5.5. Execute:

mysql_upgrade --force

Tested and worked on CentOS 6.7 and Plesk 12.


I had a similar problem as Philipe. When I start dump I saw an error like this:

mysqldump: Got error: 23: Out of resources when opening file './c1baznarz/timecard.MYD' (Errcode: 24) when using LOCK TABLES

I used simple command:

mysqldump -uroot -p c1baznarz > c1baznarz.sql

So, I add another command to my mysqldump:


and dump is ready. So, my all mysqldump query looks like this:

mysqldump -uroot -p --lock-tables --single-transaction c1baznarz > c1baznarz.sql

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .