0

I'm getting the error message "ERROR 1206 (HY000): The total number of locks exceeds the lock table size" when running a query. Following a few searches I understand that increasing innodb_buffer_pool_size should resolve this. However I'm having some trouble doing so.

I'm using MySQL Workbench 6.3 CE and have found a file called "...\MySQL\MySQL Server 5.6\my-default.ini". I can't find any files called my.ini or my.cnf. Following advice from answers to other questions on this website I edited this file so that the part that read 

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

now reads

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 1024M

However this seems to make no difference to the original error and when I run 

SELECT variable_value FROM information_schema.global_variables
WHERE variable_name = 'innodb_buffer_pool_size';

it makes no difference to the size that gets returned: 263,192,576.

I have tried creating a copy of my-default.ini and saving it as my.ini, but this doesn't help either.

Any help would be massively appreciated!

MrTed
  • 1
  • I think you should look at alternative approach. I would consider that your query may badly structured or overly complex if this is happening. Can you show us your problem query? – Dragonthoughts May 17 '18 at 08:08
  • It's a fairly simple query, but I'm dealing with a fairly big dataset by my standards (80m rows, 200 variables). I'm looking to select a subset (or years) of the dataset into a new table. Here's the query - Create X as Select * from Y where Z in ('2012','2013', '2014', '2015'); – MrTed May 17 '18 at 12:37
  • If Mysql supports the `no lock` hint that may help. – Dragonthoughts May 17 '18 at 12:59

1 Answers1

0

With thanks to Dragonthoughts I searched for info on the no locks hint and found this - Any way to select without causing locking in MySQL?

I then used the following code 

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
Create X as Select * from Y where Z in ('2012','2013', '2014', '2015');
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

Which seemed to work.

MrTed
  • 1