Bug #40534 adding a reference to RENAME in DELETE doc
Submitted: 5 Nov 2008 18:36 Modified: 9 Dec 2008 18:47
Reporter: Johan Euphrosine Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Paul Dubois CPU Architecture:Any

[5 Nov 2008 18:36] Johan Euphrosine
Description:
What about adding a reference in this doc page to using RENAME technique to delete many rows without locking too long.
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

proppy: is there a way to get around this error ?
proppy: ERROR 1206 (HY000): The total number of locks exceeds the lock table size
proppy: I'm trying to delete 20M row from a table
ebergen: an innodb table?
proppy: ebergen: yep
proppy: trying to set innodb_buffer_pool_size in my.cnf
ebergen: how many rows are in the table?
proppy: ebergen: 40 000 000
proppy: ebergen: I want to delete 20 000 000 rows
ebergen: hm
ebergen: you have basically two choices
ebergen: 1 delete the rows in small transactions
ebergen: 2. create a new table with the rows you want and do an atomic rename
ebergen: innodb simply wasn't built to delete 20mil rows in a single transaction
proppy: ebergen: atomic swap you mean ?
proppy: rename would likely fail if the table exist ?
ebergen: proppy rename table a to b, b to a;
ebergen: err
ebergen: a to b, c to a
proppy: ebergen: what if there are request to a in the between ?
ebergen: rename table locks all the tables before renaming them
ebergen: that's why it's an atomic rename
proppy: oh you mean renaming the both table is atomic ?
ebergen: there is an example at the top of: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
proppy: ebergen: thanks a lot

How to repeat:
read http://dev.mysql.com/doc/refman/6.0/en/delete.html
no reference to RENAME except in the comment
(note that TRUNCATE is referenced as an alternative way to delete a whole table)
[9 Dec 2008 18:47] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've added an example showing how to use INSERT ... SELECT and RENAME TABLE to quickly delete all but a given set of rows from a table.