Bug #28613 Using utility MYSQLDUMP: Crash dumping DB with a large number of tables
Submitted: 23 May 2007 9:51 Modified: 27 Sep 2008 18:40
Reporter: Christian Chinelli Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18/5.0.22/5.0.37 OS:Microsoft Windows (Windows Server 2003 SP2 / Win XP Professional SP2 / Win 2000 SP4)
Assigned to: CPU Architecture:Any

[23 May 2007 9:51] Christian Chinelli
Description:
I have a DB with approximately 1400 tables, and now I have many problems to make the dump using utility mysqldump; but until 2 months ago, when the DB was smaller, all worked perfectly.
The errors that I get are various:
1)mysqldump: Got error: 29: file * not found (errcode: 24) when using LOCK TABLES
2)mysqldump: Got error: 23: out of resource (errcode: 24) when using LOCK TABLES
3)mysqldump: Got error: 1017: Can't open file: '*.frm' (errno: 24) when using LOCK TABLE.
I have read that the problem could be resolved by increasing the value of variable "open-files-limit" in the configuration file "my.ini"; but when I try to increasing this number, I get the error: "could not increase number of max_open_files more than 2048".
I don't know what I can still do; please, give me a solution as soon as possible.
Thanks a lot.

Christian Chinelli
Multi Media Planet srl
Italy

How to repeat:
I have a DB with approximately 1400 tables, and now I have many problems to make the dump using utility mysqldump; but until 2 months ago, when the DB was smaller, all worked perfectly.
The errors that I get are various:
1)mysqldump: Got error: 29: file * not found (errcode: 24) when using LOCK TABLES
2)mysqldump: Got error: 23: out of resource (errcode: 24) when using LOCK TABLES
3)mysqldump: Got error: 1017: Can't open file: '*.frm' (errno: 24) when using LOCK TABLE.
I have read that the problem could be resolved by increasing the value of variable "open-files-limit" in the configuration file "my.ini"; but when I try to increasing this number, I get the error: "could not increase number of max_open_files more than 2048".
I don't know what I can still do; please, give me a solution as soon as possible.
Thanks a lot.

Christian Chinelli
Multi Media Planet srl
Italy
[23 May 2007 13:05] Valeriy Kravchuk
Thank you for a problem report. As you are on Windows, 2048 open files is a (known) limitation of current versions on that platfrom. Please, try to dump individual tables, in batches, with < 1000 at once.
[28 May 2007 8:18] Christian Chinelli
Result of the dump of a view

Attachment: users.sql (text/plain), 766 bytes.

[28 May 2007 8:56] Christian Chinelli
Hello.
As suggested, I have set the dump of my DB in a batch file, by reading and dumping individual tables, using the instruction "SHOW TABLES FROM (DB)"; now, the dump of tables works correctly, but there are the following problems:
1) the instruction "SHOW TABLES" shows the views too, but the dump for these views is in an incorrect format, as specified in the file attached;
2) the instruction does not return the procedures and functions.

Now, how can I dump views, procedures and functions of my DB?
Please, reply to me.
Thank a lot.

Christian Chinelli.
Multi Media Planet srl
Italy
[29 Jul 2008 12:03] Chaitanya Jakhadi
I am facing issue on MySQL 5.0.37 on linux. I have 2500+ tables in database. I am trying to take mysqldump but showing (Errcode: 24) when using LOCK TABLES
[27 Sep 2008 18:39] Vladislav Vaintroub
Fixed with Bug#24509 (in 6.0)