The total number of locks exceeds the lock table size

Question

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:

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

The queries in question are:

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?

EDIT: 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.

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

Answer 1

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.

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

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

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

1. service mysqld restart
2. /etc/init.d/mysqld restart

Reference The total number of locks exceeds the lock table size