问题描述

我正在 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.

编辑 2:将 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 restart

参考 锁总数超过锁表大小

这篇关于锁总数超过锁表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！