MySQL Bugs: #28613: Using utility MYSQLDUMP: Crash dumping DB with a large number of tables

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:	Windows (Windows Server 2003 SP2 / Win XP Professional SP2 / Win 2000 SP4)
Assigned to:		CPU Architecture:	Any

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

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.

Result of the dump of a view

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

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

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

Fixed with Bug#24509 (in 6.0)