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 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.