MySQL Bugs: #3893: mysqldump: Got error: 1105: not found (Errcode: 24) when using LOCK TABLES

Bug #3893	mysqldump: Got error: 1105: not found (Errcode: 24) when using LOCK TABLES
Submitted:	26 May 2004 3:42	Modified:	29 May 2004 18:00
Reporter:	Mudit Wahal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	Ver 14.3 Distrib 4.1.1-alpha	OS:	Linux (Redhat 7.2)
Assigned to:		CPU Architecture:	Any

Description:
mysql  Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)

mysqldump is giving error on few tables. I even dropped the tables
and recreated them with same data. Still same error messsage.

mysqldump: Got error: 1105: File './intraday/ASKJ_R.MYD' not found (Errcode: 24)when using LOCK TABLES

-rw-rw----    1 mysql    mysql        8656 May 25 17:59 ASKJ_R.frm
-rw-rw----    1 mysql    mysql      404524 May 25 17:59 ASKJ_R.MYD
-rw-rw----    1 mysql    mysql      182272 May 25 17:59 ASKJ_R.MYI

mysqldump: Got error: 1105: File './eod_indices/GSPCARD_X.MYD' not found (Errcode: 24) when using LOCK TABLES
-rw-rw----    1 mysql    mysql        8714 May 25 18:09 GSPCARD_X.frm
-rw-rw----    1 mysql    mysql       34664 May 25 18:09 GSPCARD_X.MYD
-rw-rw----    1 mysql    mysql        7168 May 25 18:10 GSPCARD_X.MYI

Thanks

How to repeat:
Dont know .. can you give the data

mysql> use eod_indices;
Database changed
mysql> desc GSPCARD_X;
+--------+---------------+------+-----+------------+-------+
| Field  | Type          | Null | Key | Default    | Extra |
+--------+---------------+------+-----+------------+-------+
| date   | date          |      | PRI | 0000-00-00 |       |
| close  | decimal(10,2) | YES  |     | NULL       |       |
| high   | decimal(10,2) | YES  |     | NULL       |       |
| low    | decimal(10,2) | YES  |     | NULL       |       |
| volume | int(11)       | YES  |     | NULL       |       |
| yest   | decimal(10,2) | YES  |     | NULL       |       |
+--------+---------------+------+-----+------------+-------+
6 rows in set (0.52 sec)

mysql> use intraday;
Database changed
mysql> desc ASKJ_R;
+--------+---------------+------+-----+------------+-------+
| Field  | Type          | Null | Key | Default    | Extra |
+--------+---------------+------+-----+------------+-------+
| date   | date          |      | MUL | 0000-00-00 |       |
| time   | time          |      |     | 00:00:00   |       |
| value  | decimal(10,2) |      |     | 0.00       |       |
| volume | int(11)       |      |     | 0          |       |
+--------+---------------+------+-----+------------+-------+
4 rows in set (0.06 sec)

Hi!

Thank you for writting to us.

The error that you are getting indicates that you have ran out of file descriptors. 

Please, try to increase those for a MySQL server , for example in a manner described in our manual.

If the error repeats even after that, please let us know.

I've table_cache = 24, max_connections = 100 and open_files_limit = 1024.
I'll increase the open_files_limit to 8192 and see if it happens again.

Thanks

Mudit

I changed the open_files_limit to 8192 and it happened again
but for a different table.

mysqldump: Got error: 1105: File './intraday/JOE_R.MYD' not found (Errcode: 24) 
when using LOCK TABLES

-----------

Here is how I'm dumping the database.

mysqldump  --add-drop-table=false --force --quote-names --all --extended-insert --quick --add-locks --where "date =  '2004-05-29'" --database mydatabase_name

------------

If I remove --add-locks, I still get the SAME error message. I guess "--all" option is automatically adding the lock table.

>mysqldump --version
>mysqldump  Ver 10.2 Distrib 4.1.1-alpha, for pc-linux (i686)

Thanks
Mudit

You have to change it in mysqld_safe script, unless you are running mysqld under uid of root. Also, script has to be started by root user.

Removed -all from mysqldump, removed --quick, still same issue. There are 8091 tables
in that particular database. When the open_files_limit was 1024, it
used to fail around 507th table. Now when the open_files_limit is
8192, its failing at 4091th table.

BTW, this is only happening since I upgraded from 4.0.1 to 4.1.1-alpha.
I never had this issue in 4.0.1 for 3 years !

I've changed in /etc/my.cnf file. I run the mysql.server script from
/etc/rc.d/rc.local script. Will change the mysqld_safe and let you know.

Thanks

With this number of tables, 8192 is too low.

You will probably even have to make system wide changes, as per our manual.

Why mysqld has to keep the table open after it has been dumped ? I
think its really bad design. If I've 100,000 tables, do you expect
me have 100,000 tables open ???? It should be able to do simple
file management.

Is the issue with only mysqldump or is it general issue with mysqld ?
May be I've to write my own dump program to work around this issue.
I'd expect mysql team to come up with a better solution in future
instead of blaming it on the kernel. 8192 open files are really
more than enough. For dumping tables, it should be one file at a
time anyway.

Are you sure there is no option to flush the tables and close them
after each query ?

Thanks

Mudit

Yes, there is a way of circumventing this, but this forum is not here for 
free support.

This forum is for fully repeatable test cases, and I have manged recently to dump entiere database with more then 9000 tables on Red Hat system.

How come this was not an issue in 4.0.1 ?

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

As Sinisa pointed out error 24 means that you're out of file descriptors:

% perror 24
Error code  24:  Too many open files

MySQL doesn't need to keep tables open for dumping, but it does, if they are locked with LOCK TABLES.

in 4.1 --opt option of mysqldump was made default, and it causes LOCK TABLES. Use --skip-lock-tables to disable.

Hmm... if it was working in 4.0.1 and not working in 4.1.1 , how its NOT a bug ? Suddenly its a feature/support request ???

I'm really baffled and disappointed at the sametime. And its 100% reproducible.

Just create a database with 10,000 tables, leave open_files_limit=1024.
Try to dump with 4.0.1 (I'm sure you guys still have it sitting around somewhere).
Then dump the same database with the 4.1.1 server.

If it works in 4.0.1 and doesn't work in 4.1.1 , its a BUG. PERIOD.
Mysql doesn't want to fix it, fine !

My previous comment applied to "[29 May 8:55am] Sinisa Milivojevic ".
That was not a support question but a genuine bug. If some thing
works in version 4.0.1 and doesn't work in 4.1.1, thats A BUG !
If you change the default behavior of commands across versions, its
an issue with mysql and not with customer.

After following suggestion made by "[29 May 8:59am] Sergei Golubchik ",
I'm not getting any more messages. Thanks Sergei.

I agree with Mudit Wahal in one thing. If I do backup, eg via mysqlhotcopy, all tables are open after it dumped. Its bad, you must set open_files_limit to custum value, but this case not occur in standing running. Same problem may be with mass myisamchk. Why DB mng don't close this tables, pos. if reach any limit?

you my all find this usefull if the problem still persists.. or if you are still using an older build of mysql

http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html