zeldor.biz

Linux, programming and more

Copyright © 2025
Log in

MySQL: Error 1016 when using LOCK TABLES

August 8, 2011 by Igor Drobot 2 Comments

I run a nightly mysqldump of all my databases and one of them has over 500 tables, mysqldumper bring me a error while processing this huge database:

My Error message:

1
mysqldump: Got error: 1016: Can't open file: './dbname/tablename.frm' (errno: 24) when using LOCK TABLES

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

I found two different solutions to avoid this nasty problem.

Solution one:
This solution will cause mysql to keep only one table open at a time.
Little example of the mysqldump command:

1
mysqldump --single-transaction --user=root --password=root database >database.sql

mysqldump --single-transaction --user=root --password=root database >database.sql

Solution two:
The second solution is a little bit dirty, it will skip all locked tables.
Append –skip-lock-tables option to your mysqldump operations, this will look like this one:

1
mysqldump --skip-lock-tables --user=root --password=root database >database.sql

mysqldump --skip-lock-tables --user=root --password=root database >database.sql

Filed Under: Linux, MySQL Tagged With: MySQL, mysqldump, single-transaction, skip-lock-tables

Categories

Archives

Tags

apache2 Apple arduino ARM Automation backup bash Cisco Cluster Corosync Database Debian Debian squeeze DIY DNS Fedora FTP Fun Icinga Ipv6 KVM Linux LVM MAC OS X Monitoring MySQL Nagios Nginx openSUSE OpenVPN PHP Proxy Python python3 qemu RAID rsync Samba security ssh Ubuntu virtualization Windows Windows 7 Wordpress

Comments

  1. Thomas says

    November 5, 2011 at 16:00

    Hi,

    the option “–single-transaction” should only be used with InnoDB tables and implies “lock-tables=false”. So handle with care. And RTFM:
    http://www.manpagez.com/man/1/mysqldump/

    Bye

Trackbacks

  1. MySQL Dump says:
    August 14, 2020 at 14:18

    […] For very large databases you can use this previous written post: Dump locked Tables […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Yeaaah Cookie! We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.Ok