Working on a large 20gb innodb table that gets new records inserted daily, I decided to archive some of the old data by splitting the table into many table_archive_2015M subtables by using its `date` column and grouping the data by month.
While running the following query:
insert into ranking_archive_201508 select * from ranking_dailyresults where date >= '2015-08-01' and date < '2015-09-01' LIMIT 500000;
I got the following error from the mysql 5.1 server.
ERROR 1206 (HY000): The total number of locks exceeds the lock table size