# Error when running mysqldump restore Re: ERROR 1100 (HY000) at line : Table was not locked with LOCK TABLES

**URL:** <https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [October 5, 2021, 2:49pm UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429 "2021-10-05T14:49:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ipcmlr](https://avatars.discourse-cdn.com/v4/letter/i/df705f/32.png) [@ipcmlr](https://forums.percona.com/u/ipcmlr)\
**Post date:** [October 5, 2021, 2:49pm UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429/1 "2021-10-05T14:49:46Z")

</div>

I’m trying to restore a dumpfile.  
The source is mysql 5.7 and the destination is mysql 8.0

I get errors because the mysqldump file doesnt lock all necessary tables.  
Is there a way to dump or restore so that I don’t get this error?

Or do I just have to manually edit the mysqldump file. There is quite a bit of tables that will hit this error so I would rather not do it manually.

In the sqldump file:

LOCK TABLES `TABLE_A` WRITE;  
/\*!40000 ALTER TABLE `TABLE_A` DISABLE KEYS _/;  
set autocommit=0;  
ALTER TABLE `TABLE_A` ADD KEY `TABLE_A_IX1` (`SESSION_ID`);  
ALTER TABLE `TABLE_A` ADD CONSTRAINT `TABLE_A_FK` FOREIGN KEY (`SESSION_ID`) REFERENCES `TABLE_B` (`SESSION_ID`) ON DELETE CASCADE;  
/_!40000 ALTER TABLE `TABLE_A` ENABLE KEYS \*/;  
UNLOCK TABLES;  
commit;

its should be LOCK TABLES `TABLE_A` WRITE, `TABLE_B` READ;

so that I dont get the error:  
ERROR 1100 (HY000) at line 74: Table ‘TABLE\_B’ was not locked with LOCK TABLES

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [October 6, 2021, 11:20am UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429/2 "2021-10-06T11:20:29Z")

</div>

Hi ipcmlr, I am not sure what options you are using to run mysqldump but try using --single-transaction. That should let you export/import without any table locking.

---

<div class="post-metadata">

**Author:** ![ipcmlr](https://avatars.discourse-cdn.com/v4/letter/i/df705f/32.png) [@ipcmlr](https://forums.percona.com/u/ipcmlr)\
**Post date:** [October 28, 2021, 1:23pm UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429/3 "2021-10-28T13:23:09Z")

</div>

I think it has something to do with using mysqldump on 5.7 then importing into 8.0.  
I dont get any issues when importing into a 5.7 database.

Decided to just use xtrabackup instead for restores.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [October 28, 2021, 1:30pm UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429/4 "2021-10-28T13:30:03Z")

</div>

Definitely xtrabackup is a better option but did you try --single-transaction? that should help avoid locking.

---

<div class="post-metadata">

**Author:** ![ipcmlr](https://avatars.discourse-cdn.com/v4/letter/i/df705f/32.png) [@ipcmlr](https://forums.percona.com/u/ipcmlr)\
**Post date:** [October 28, 2021, 1:45pm UTC](https://forums.percona.com/t/error-when-running-mysqldump-restore-re-error-1100-hy000-at-line-table-was-not-locked-with-lock-tables/12429/5 "2021-10-28T13:45:54Z")

</div>

Yes. I tried single transaction. i tried just locking tables. Didnt work either way.