具有WHERE ID IN(SELECT ...)的mysqldump产生表“未锁定".错误

问题描述

我有2个数据库，db1的表field_collection_item中缺少约100,000行，我想通过从db2导出进行修复.

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. 通过db2中的item_id识别丢失的项目，并导出item_id的列表.
2. 将item_id导入db1到新表missing_field_collection_item

3. 使用以下mysqldump提取数据:

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用户-pPASS数据库--no-create-info --tables field_collection_item --where ="item_id IN(SELECT item_id FROM missing_field_collection_item));"

但这会导致错误:

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)

我更愿意在不更改db2的情况下执行此操作，但这不是严格必要的，如果事实证明，这样做的唯一现实方法是删除不需要的行，然后在不使用where子句的情况下进行转储

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.

更新

我只是通过添加--single-transaction发现了上面的作品，它似乎关闭了锁定.这应该很安全，因为db2不存在，但是我不确定我是否了解任何副作用，因此在没有其他意见的情况下，我不会接受此作为答案.

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.

推荐答案

如果您的表是MyISAM，则最安全，最简单的方法是传递标志--lock-all-tables.如果您的表是InnoDB，则--single-transaction更好.

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.

这篇关于具有WHERE ID IN(SELECT ...)的mysqldump产生表“未锁定".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！