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

MySQL & MariaDB Percona Server for MySQL 5.7
1

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
2

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
3

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.

1 Like
4

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

1 Like
5

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

1 Like