锁总数超过锁表大小

2021-11-20 00:00:00 sql mysql

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

参考 锁总数超过锁表大小

相关文章