Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysqldump with WHERE id IN (SELECT ...) yields table "was not locked" error

Tags:

I have 2 databases, with ~100,000 rows missing from the table field_collection_item from db1, which I'd like to repair by exporting from db2.

My plan to accomplish this was to:

  1. identify the missing items by item_id in db2, exporting the list of item_ids.
  2. import the item_ids into db1 into a new table missing_field_collection_item
  3. Using the following mysqldump to pull the data:

    mysqldump -u USER -pPASS DATABASE --no-create-info --tables field_collection_item --where="item_id IN (SELECT item_id FROM missing_field_collection_item);"

however this gives the error:

Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `field_collection_item` WHERE item_id IN (SELECT item_id FROM missing_field_collection_item);': Table 'missing_field_collection_item' was not locked with LOCK TABLES (1100) 

I would prefer to do this without making changes to db2 however it's not strictly necessary, if it turns out the only realistic way to do this is to drop the rows I don't want and then dump without a where clause.

UPDATE

I discovered the above works simply by adding --single-transaction, which seems to turn off locking. This should be safe since db2 isn't live, however I'm not confident I understand any side-effects, so I won't accept this as the answer without a second opinion.

like image 565
DanH Avatar asked Aug 05 '13 07:08

DanH


People also ask

What is not locked with lock table in MySQL?

You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically.

Does Mysqldump lock table?

By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.

Does SELECT query lock table in MySQL?

SELECTs do not normally do any locking that you care about on InnoDB tables. The default transaction isolation level means that selects don't lock stuff.

Does MySQL lock table on insert?

MySQL uses table locking (instead of row locking or column locking) on all table types, except InnoDB and BDB tables, to achieve a very high lock speed.


1 Answers

If your tables are MyISAM, the safest, easiest way to handle this is to pass the flag --lock-all-tables. If your tables are InnoDB then --single-transaction is better.

like image 91
Alexander Garden Avatar answered Sep 19 '22 11:09

Alexander Garden