Fixing mysqldump: Got error: 1016: Can’t open file & mysqldump: Got error: 23

When doing exports of large databases using mysqldump, it is common to get errors that are along the lines of:

mysqldump: Got error: 1016: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24) when using LOCK TABLES

or maybe

mysqldump: Got error: 23: Out of resources when opening file ‘./databasename/tablename.MYD’ (Errcode: 24) when using LOCK TABLES

Both come from the same cause and don’t worry – your database is not currupt..

The first action that mysqldump takes is to lock all of the tables so that the database cannot go out of sync from the beginning to the end of the export.  Of course, that means that your users can’t make changes while  doing the dump. And that’s just one more reason you need to be careful using mysqldump.

The solution to this problem is simple add –skip-lock-tables to your command line.

At that point the tables won’t be locked, and the export will run much faster. There is a slight downside in that people can use the table as it is being exported and you could potentially get an update written as you are exporting that table. But it is unlikely that you’ll hit any real world problems when doing this, especially with things like blog exports.

  • neeraj

    thanks brother

  • Kapil

    Thanx

  • Wonderful goods from you, man. I’ve understand your stuff previous to and you are just too great. I really like what you have acquired here, really like what you are saying and the way in which you say it. You make it enjoyable and you still take care of to keep it wise. I can not wait to read far more from you. This is really a wonderful web site.

  • If you want to improve your experience just keep visiting
    this web page and be updated with the latest news posted here.

  • Hey there, I think your site might be having browser compatibility issues.
    When I look at your website in Ie, it looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, superb blog!

  • Amazing issues here. I am very happy to look your article.
    Thank you so much and I am looking forward to contact you.

    Will you kindly drop me a mail?