MySQL/InnoDB – ‘Unable to lock’ issue

Today, one of our MySQL database suddenly went unresponsive. The first thing I noticed was the following lines in the error.log:

InnoDB: Unable to lock /path/to/ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

Upon further investigation, I found a not perfectly matched issue but does look similar: http://forums.mysql.com/read.php?22,22344,24497#msg-24497.  I took a chance and did the following:

  1. killed the lingering mysql process
  2. mv (move) ibdata1 file to ibdata1.bad
  3. cp -a ibdata1.bad ibdata1
  4. restart the db server

Note: ‘-a’ argument in cp command is the same as –archive which means presereve as much as possible of the structure and attributes of the original files in the copy.

Voila! The mysql instance started a crash recovery and in the end all was good again.  On the error log, here’s what I saw (recovery messages):

100825 16:58:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
100825 16:58:40  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 2737988291.
InnoDB: Doing recovery: scanned up to log sequence number 8 2738024293
100825 16:58:48  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 182, file name log-bin.000039
100825 16:59:12  InnoDB: Started; log sequence number 8 2738024293

Our system configuration:

  • mysql 5.0.15
  • Red Hat ELS release 4

4 Responses to “MySQL/InnoDB – ‘Unable to lock’ issue”

  1. Great Places…

    […]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[…]…

  2. woww… Thanks Man.. It worked for me

  3. Josh M. says:

    This saved the Day!

    MySQL 5.0.67
    RHEL5

  4. Seb says:

    same for me, i had the error on a debian squeeze machine, running Mysql 5.1.66,
    thanx

Leave a Reply