I came across this problem this morning. Yesterday night before I went to bed, I launched a big SQL query that looks like that:
INSERT INTO table2 SELECT * FROM table1 WHERE date > "2009-12-01"
The structure of table2 is identical to the structure of table1, and table1 counts around 35 millions rows. After like 4 hours running, the query ended with this error message:
The total number of locks exceeds the lock table size
And no rows were inserted in the destination table at all. Indeed, the problem is the size of table1 with its 35 millions rows. MySQL tries to copy all the rows of table1 in a temporary table (the lock table) during the SELECT operation before even trying to insert the first row. That’s why if the lock table’s max size is smaller than the number of rows you want to treat, MySQL will return this error.
The solution is simply to edit my.cnf, and add or edit the following line:
innodb_buffer_pool_size = 16M
To make it work, I set the size in bytes, so if I wanted 256 megabytes for my buffer pool, I would write 268435456. It would look like like this thus:
innodb_buffer_pool_size = 268435456
Restart MySQL so that the new conf file is taken in count, and your big query should work now.
nb: at first I tried to change this value dynamically with SET VARIABLE because I didn’t want to restart the server, but it wasn’t taken in count… Apparently it’s a variable that has to be set from the startup of MySQL.
nb2: this method might not work if you’re not using innoDB tables, since we’re fixing the issue thanks to an innoDB confi directive. However, I’m not sure the problem would occur if the tables were MyISAM or anything else. Someone to confirm?