Friday, January 19, 2007

mysqldump: Couldn't execute SHOW TRIGGERS LIKE

mysqldump: Couldn't execute SHOW TRIGGERS LIKE

Importing a database with 251+ tables on Windows XP results in the following error:
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE table_name. Can't create
ate/write to file '\tmp\#sql_8d4_0.MYD' (Errcode: 17) (1)

This issue is due to the windows file handles.

Please examine the server status variable "open_files_limit" and note that
mysqld requires two open files for each myisam table.

On my machine it looks like this:
mysql> SHOW VARIABLES LIKE 'Open_files_limit';
Variable_name Value
open_files_limit 1024

From the manual:
The number of files that the operating system allows mysqld to open. This is
the real value allowed by the system and might be different from the value you
gave using the --open-files-limit option to mysqld or mysqld_safe. The value is
0 on systems where MySQL can't change the number of open files.

When mysqldump tries to dump the files, it will first take a read lock on all
the tables in the database. That requires it to open all of them at the same
time. So if the number of available open files is low, this kind of error can

To make mysqldump avoid taking the read lock use --skip-lock-tables option. I
successfully used that to dump more tables than my system had file descriptors.

It should also be possible to put a smaller number of tables in each database or
only dump a selected number of tables at a time.

But that are workarounds, best thing is to increase the number of open files on
the system.


mysqldump: Couldn't execute SHOW TRIGGERS LIKE

1 comment:

nulldev1ce said...

THANK YOU for posting this! Got rid of the issue for me on Fedora Core 9 and MySQL 5.0.77 with a large Wordpress MU installation.