zeldor.biz

Linux, programming and more

Copyright © 2023
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 *