MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query

I've created a procedure to loop through large InnoDB tables and delete a chunk of rows, then pause and repeat until max field value allowed is reached.

Binary logging is enabled, but I'm leaving it at the default NONDETERMINISTIC setting, so I don't think that matters.

The CALL command includes the number of rows to delete per row (num_rows), and the last field value to delete (maxId). (I'm using the primary key id).

The first part works correctly, it loops through and deletes a chunk of rows each pass.

There's an If statement to check the last id (@z) of the current chunk against maxId, it should exit the loop if the last id is null or is greater than the maxId. But it never fires and leaves the loop.

I can't determine what I've done wrong, thoughts?

UPDATE:

I've determined the source of the problem, but I'm not sure how to fix it.

The last id @z of the chunk to delete is set with a limit statement in the @sql_text2 query.

But at the end, the limit option in that query causes it to return an empty result set because the offset is greater than result set and rows to return is one.

For example, I ran the query that would be run at the end and it returns an empty result set (changed numbers for clarity). Using id for keyfield:

SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

The query @sql_text2 as it would be run by the procedure (with INTO @z):

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

The last id in the table is 7817.

The statement says to get 1000 rows starting with the last id deleted in the previous pass (7000) and return one row (the last row) of the result set.

The last id would of the result set would be 7999, but that doesn't exist in the table.

So I'm not sure what value is assigned to @z and can't check for it to trigger LEAVE loop_label;

In the IF block, I was checking is null and I've added a check for = "" and it still doesn't ma

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:


When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.

see: https://dba.stackexchange.com/a/35207/12923

StackExchange Database Administrators Q#30851, answer score: 4