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:
item_id
in db2
, exporting the list of item_id
s.item_id
s into db1
into a new table missing_field_collection_item
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.
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.
By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With