向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

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

26 May 2004 5:42] Mudit Wahal
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[26 May 2004 21:55] Mudit Wahal
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)[29 May 2004 19:03] Sinisa Milivojevic
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.[29 May 2004 19:13] Mudit Wahal
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[29 May 2004 19:28] Mudit Wahal
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[29 May 2004 19:41] Sinisa Milivojevic
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.[29 May 2004 19:42] Mudit Wahal
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 ![29 May 2004 19:44] Mudit Wahal
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[29 May 2004 19:46] Sinisa Milivojevic
With this number of tables, 8192 is too low.

You will probably even have to make system wide changes, as per our manual.[29 May 2004 19:51] Mudit Wahal
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[29 May 2004 19:55] Sinisa Milivojevic
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.[29 May 2004 19:56] Mudit Wahal
How come this was not an issue in 4.0.1 ?[29 May 2004 19:59] Sergei Golubchik
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.[29 May 2004 20:12] Mudit Wahal
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 ![29 May 2004 20:30] Mudit Wahal
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.[5 Aug 2005 12:28] karel barel
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?[4 Mar 2008 19:57] Gladstone Taylor
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

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/2913/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！