MySQL While Loop Ending After Insert – Troubleshooting Guide

insertMySQLstored-procedures

I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements runs for the first time i am kicked out of my while loop and the procedure ends. I have tried storing the inserts in another procedure and call that procedure from the inside the if statement and i have tried adding commit after the insert statements. Neither have works.

EDIT: OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.

WHILE(curse = TRUE) do
        fetch theTable into ciid,crid;
        SELECT ClientInformation_CIID INTO classAttOrAb FROM clientmonitoringdata LEFT JOIN clientattendancemonitoring 
          ON FK_clientmonitoringdata_ClientMonitoringDataID = ClientMonitoringDataID
          WHERE clientmonitoringdata.FK_clientrequirements_ClientRequirementsID  = crid AND
          EntryDate BETWEEN startDate and endDate  AND VoidEntry = FALSE AND clientattendancemonitoring.AttendedClass IS NOT NULL LIMIT 1;
        IF(classAttOrAb = 0) THEN
          INSERT INTO clientmonitoringdata(ClientInformation_CIID,EntryDate,FK_clientrequirements_ClientRequirementsID)
            VALUES( ciid, classDate, crid);

          SET cmid = LAST_INSERT_ID();

          INSERT INTO clientattendancemonitoring( FK_clientInformation_CIID, Absence, AttendedClass, ClassAttended, ExcusedAbsenceReason, 
            FK_clientmonitoringdata_ClientMonitoringDataID, FK_clientrequirements_ClientRequirementsID, AbsenceNotes, FK_fxuseraccounts_FXUserAccountsID) 
            VALUES(ciid,abType,FALSE,class,exAbReason,cmid,crid,abNotes,empId);
          COMMIT;
        end IF;

        SET classAttOrAb = 0;
      end while;

END

Best Answer

OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'

MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.

Best Answer

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

MySQL: Loop over cursor results ends ahead of schedule

Related Question