ERROR 1100 (HY000) at line 11084: Table ‘search_index’ was not locked with LOCK TABLES

I’m trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the below error:

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

When I open up the backup at line 11084 here is what I get:

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` VALUES ...
-- MySQL dump 10.11
--
-- Host: localhost    Database: drupal
-- ------------------------------------------------------
-- Server version       5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `search_index`
--

DROP TABLE IF EXISTS `search_index`;        <=== LINE 11084
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

I checked the other statements and normally INSERT statements are followed by UNLOCK TABLES;. Accordingly if I make the below modifications to the mysqldump file the problem is fixed:

INSERT INTO `sessions` VALUES ...
UNLOCK TABLES;                   <=== ADDED LINE
-- MySQL dump 10.11
[...]
DROP TABLE IF EXISTS `search_index`;

I don’t know what’s wrong there but the whole comment block starting with -- MySQL dump 10.11 is the same as the one at the beginning of the mysqldump file, and it looks like mysqldump started a new dump while writing the INSERT INTO sessions statements which were not closed properly with UNLOCK TABLES;.

Does someone have any idea as to what’s going on here?

My initial guess would be that when the mysqldump file was being created, the option single-transaction was not used.

According to MySQL Documentation on single-transaction

The –single-transaction option and the –lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

The fact that you embedded UNLOCK TABLES; into the dump manually shows that neither option was called when the mysqldump was first created. Thus, upon reload, this error reared its ugly head.

Sometimes this error comes when you have the following command in your sql dump without any insert

LOCK TABLES `table_name` WRITE;

Just remove the above line and it will resolve the issue for you.

Hope it helps

Cheers
S