Delete from table in chunks I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this?

DELIMITER $$
DROP PROCEDURE IF EXISTS archive_table $$
create procedure archive_table () 
set @min=(select min(dob) from test
              where dob &lt; DATE_SUB(CURDATE(), INTERVAL 30 day));
while @min is not null
begin   
    delete from test where dob = @min;
    commit ;
    select min(dob) from test
            where dob &gt; @min
              and dob &lt; DATE_SUB(CURDATE(), INTERVAL 30 day) );
END WHILE;
END$$


ERROR:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
 near 'while @min is not null
begin
    delete from test where dob = @min;
    commi' at line 1
The openark kit by Shlomi Noach could be right up your alley!

From the introduction here:


oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.


From the documentation page here:


  This utility allows for splitting long running or non-indexed
  UPDATE/DELETE oprations, optionally multi-table ones. Long running
  updating queries are often used. Some examples:

Purging old table records (e.g. purging old logs).
Updating a column on a table scale.
Deleting or updating a small number of rows, but with a non-indexed search condition.
Inserting into one table aggregated values from other tables.



The tool has a load of options with examples - I was very impressed when I used a couple of his tools for various bits and pieces.

So, kick it off with cron at 03:00 in the morning and relax!