锁总数超过了锁表的大小 [英] The total number of locks exceeds the lock table size

问题描述

我正在MySQL中运行报告.查询之一涉及将大量行插入到临时表中.当我尝试运行它时，出现此错误:

I'm running a report in MySQL. One of the queries involves inserting a large amount of rows into a temp table. When I try to run it, I get this error:

错误代码1206:锁的数量超过了锁表的大小.

Error code 1206: The number of locks exceeds the lock table size.

有问题的查询是:

create temporary table SkusBought(
customerNum int(11),
sku int(11),
typedesc char(25),
key `customerNum` (customerNum)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into skusBought
select t1.* from
    (select customer, sku, typedesc from transactiondatatransit
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondatadelaware
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondataprestige
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku) t1
join
(select customernum from topThreetransit group by customernum) t2
on t1.customer = t2.customernum;

我已经读过更改配置文件以增加缓冲池大小会有所帮助，但这无济于事.作为临时替代方法或永久修复程序，将如何解决此问题?

I've read that changing the configuration file to increase the buffer pool size will help, but that does nothing. What would be the way to fix this, either as a temporary workaround or a permanent fix?

更改了查询的一部分.不应该影响它，但是我做了一个查找替换所有操作，却没有意识到它把它搞砸了.不会影响这个问题.

changed part of the query. Shouldn't affect it, but I did a find-replace all and didn't realize it screwed that up. Doesn't affect the question.

将typedesc添加到t1.我在查询中更改了它，但在这里没有.

EDIT 2: Added typedesc to t1. I changed it in the query but not here.

推荐答案

可以通过为MySQL变量innodb_buffer_pool_size设置更高的值来解决此问题. innodb_buffer_pool_size的默认值为8,388,608.

This issue can be resolved by setting the higher values for the MySQL variable innodb_buffer_pool_size. The default value for innodb_buffer_pool_size will be 8,388,608.

要更改innodb_buffer_pool_size的设置值，请参见以下设置.

To change the settings value for innodb_buffer_pool_size please see the below set.

1. 从服务器上找到文件my.cnf.对于Linux服务器，这通常位于/etc/my.cnf
2. 将innodb_buffer_pool_size=64MB行添加到此文件
3. 重新启动MySQL服务器

1. Locate the file my.cnf from the server. For Linux servers this will be mostly at /etc/my.cnf
2. Add the line innodb_buffer_pool_size=64MB to this file
3. Restart the MySQL server

要重新启动MySQL服务器，可以使用以下2个选项之一:

To restart the MySQL server, you can use anyone of the below 2 options:

1. 重新启动mysqld服务
2. /etc/init.d/mysqld重新启动

参考 查看全文