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

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

1 Like

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.

1 Like