CODEWITHSUNDEEP
phpmysqlstored-procedures
Stan
Stan

Reputation: 8768

Stored procedure with a cursor executes in strange manner

I have a stored procedure, which should process rows in a table using a cursor. The procedure works most of the time, but sometimes it just does not execute fully. I know this because I have a simple debugging facility, embedded into the code, which logs specific lines and variables into dedicated debug table. Most interesting that the problem does always occur when running from PHP. If I use mysql client I never got this problem.

The procedure (presented here in somewhat shortened way) is the following:

CREATE PROCEDURE findnextedge(IN lastid BIGINT)
findnext_context:BEGIN
  DECLARE stop BOOLEAN DEFAULT FALSE;
  DECLARE count INT DEFAULT 0;
  DECLARE cur_fid BIGINT DEFAULT 0;
  DECLARE cur_pid1 BIGINT DEFAULT 0;
  DECLARE cur_pid2 BIGINT DEFAULT 0;
  DECLARE cur CURSOR FOR SELECT fid, pid1, pid2 FROM edges WHERE pid1 = lastid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = TRUE;

  CALL debuglog(0, 'findnextedge', 'lastid', lastid, NULL, NULL, NULL, NULL);
  SELECT SQL_CALC_FOUND_ROWS fid FROM edges WHERE pid1 = lastid;
  SET count = FOUND_ROWS();
  CALL debuglog(1, 'findnextedge', 'count', count, NULL, NULL, NULL, NULL);
  IF count = 0 THEN
    DELETE FROM paths WHERE pid1 = lastid AND pid2 = 0;
    SELECT COUNT(*) INTO count FROM paths WHERE pid2 = 0;
    CALL debuglog(2, 'findnextedge', 'count', count, NULL, NULL, NULL, NULL);
    IF count = 0 THEN
      SET @count = 1;
    END IF;
    LEAVE findnext_context;
  END IF;

  DELETE FROM paths WHERE pid1 = lastid AND pid2 = 0 ORDER BY pid1 LIMIT 1;

  OPEN cur;
  CALL debuglog(6, 'findnextedge', 'open', TRUE, NULL, NULL, NULL, NULL);
  REPEAT
    FETCH cur INTO cur_fid, cur_pid1, cur_pid2;
    CALL debuglog(7, 'findnextedge', 'stop', stop, NULL, NULL, NULL, NULL);
    IF stop = FALSE THEN

      CALL debuglog(3, 'findnextedge', 'cur_fid', cur_fid, 'cur_pid1', cur_pid1, 'cur_pid2', cur_pid2);

      // DO MAIN JOB
      // ...

      CALL debuglog(5, 'findnextedge', NULL, NULL, NULL, NULL, NULL, NULL);

    END IF;
    CALL debuglog(8, 'findnextedge', 'stop', stop, NULL, NULL, NULL, NULL);
    UNTIL stop = TRUE
  END REPEAT;

  CLOSE cur;
END;

The whole output produced if the problem occurs:

point   context name1   value1  name2   value2  name3   value3  counter time
    0   findnext    lastid  0   NULL    NULL    NULL    NULL    0   2012-11-27 18:29:56
    1   findnext    count   1   NULL    NULL    NULL    NULL    1   2012-11-27 18:29:56
    6   findnext    open    1   NULL    NULL    NULL    NULL    2   2012-11-27 18:29:56
    7   findnext    stop    0   NULL    NULL    NULL    NULL    3   2012-11-27 18:29:56

According to the log, in the point 7, just after fetching the cursor stop value is false, yet execution does not reach neither point 3, nor 8.

It looks like some internal error occurs, but i'm not sure how can I trap it. Strangely, that this happens on the very same data, from time to time, and works otherwise.

P.S. MySQL version 5.0.51b, PHP 5.2.6.

P.S.S. I've managed to find a related question - Calling a Stored Procedure Within a Cursor Loop, Without Tripping the Continue Handler. As the name of my procedure implies, it gets invoked from inside a loop in an external procedure (that has a loop through the "paths" table, by the way, and another continue handler), so it resembles those situation, and may be somehow important. I've tried the solution from the linked question, but it didn't help either.

A solution is found, the answer is posted below.

Upvotes: 4

Views: 254

Answers (2)

Stan
Stan

Reputation: 8768

I've solved the problem, though the way how it's done seems very strange to me.

The lines:

SELECT SQL_CALC_FOUND_ROWS fid FROM edges WHERE pid1 = lastid;
SET count = FOUND_ROWS();

was replaced with simplified:

SELECT COUNT(*) INTO count FROM edges WHERE pid1 = lastid;

And this works without a flaw.

The reason why the same thing was initially coded in 2 lines is that I did not have debugging table at the beginning and I controlled execution flow via SELECT's.

It seems stored procedures do not like when something is SELECTed into (unavailable) console. In my case this led to inconsistent behaviour: procedure dropped execution in some arbitrary test runs on the same data. If someone could shed a light on such behaviour, I'd be glad to accept that answer, instead of mine.

Upvotes: 0

Marc Alff
Marc Alff

Reputation: 8395

The handler to stop the loop, as in:

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = TRUE;

is global to the entire procedure.

As a result, it may well catch other not found events, not coming from the cursor fetch, and cause the loop to exit early.

Try using a begin end block just around the cursor fetch, and declare a handler there.

Upvotes: 1

Related Questions