I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows):
>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks exceeds the lock table size
I guess the client is acquiring a lock for each row, so I tried locking the table first without success:
>LOCK TABLES a WRITE; Query OK, 0 rows affected (0.00 sec) >ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks exceeds the lock table size
- Why exactly are there so many locks, i.e. what is being locked in this query?
- Why didn't locking the table solve the problem? Do I need to do the lock in a different way or lock something else as well?
- If the only solution is to increase the
innodb_buffer_pool_size, how large does it need to be for this query?
I obviously don't need a sustainable solution - just need to do this the one time. For example, the box isn't a dedicated database server but I could increase the pools size brief it for this query and then set it back to something reasonable afterwards.