mysql hanging while importing database dump

We have a db dump import script from our production db that we use to rebuild our sandbox dbs. The syntax we use for this is mysql -u uname -ppass dbname < prod_db_export.sql. The script proceeds to create the first table and开发者_C百科 then do this:

LOCK TABLES `ad` WRITE;
/*!40000 ALTER TABLE `ad` DISABLE KEYS */;
/*!40000 ALTER TABLE `ad` ENABLE KEYS */;
UNLOCK TABLES;

There is no data in table ad so there's no import statement after the DISABLE KEYS line. Anyway, the import is hanging at this point, and when we query the db with processlist we see output like this:

| 5116 | uname     | localhost | dbname     | Field List |   85 | Waiting for table |                        | 
| 5121 | uname     | localhost | dbname     | Query      |   44 | Waiting for table | LOCK TABLES `ad` WRITE | 
| 5126 | uname     | localhost | dbname     | Field List |   23 | Waiting for table |                        | 

Anybody have any idea what would cause this to happen? and better, how to resolve it?

Our SA does not want to restart mysql if at all possible because he is concerned it will fail to restart (which happened to us the last time we had a similar situation, and he had to rebuild the entire db, including all the sandboxes' dbs, from backup).

We subsequently created a new database, dbname2, and were able to run the import successfully with no hanging, no table lock messages in processlist.

In my case, it worked after restarting the mysql service

sudo service mysql restart

Being the SA referenced in this question I wanted to point out a few things:

• Before dropping the DB the ibdata files were deleted( we use table per idb ) for that DB
• The database was then dropped and recreated
• Upon import the first table is ad and it seems to already be locked.

To me this would mean that there is still lock information stored in the InnoDB metadata which is held in the shared ibdata file. Last time I had problems with the InnoDB metadata being out of sync with the individual table ibdata files I blew away everything and reimported. When I tried to restart on that occasion MySQL refused as it couldn't find table ibd files which had been removed, but were still in the metadata.

The persistent problem here is removing the ibd files via command line rather than doing a drop database. pebkac.

if you delete the inno tables space files without dropping the tables first, then you will be unable to manage the tables at all. the server might even refuse to come up.

in that case, you need to use the innodb_force_recovery option.

MAKE ABSOLUTELY SURE THAT WHEN YOU USE THIS OPTION, NO OTHER CLIENTS CONNECT AND TRY TO DO ANYTHING.

you probably need to set innodb_force_recovery to 3. if there were transactions in progress when you shut down the server and inno did not commit/rollback them cleanly, then you might need to use 6.

then you can DROP the tables and database. shut down the server again and set innodb_force_recovery back to 0.

if you still have problems, post the relevant portion of your mysql log.

In my case, the database dump tried to insert a million rows in one insert statement. A proper dump only tries to dump about 1500 rows at a time in one insert statement.

INSERT INTO `employees` (`id`, `name`) VALUES 
(1, 'jack'),
(2, 'mary'),  
--don't have too many of these rows -- start a new INSERT statement
--However, don't go the other extreme and only insert 1 row per insert statement