带有游标的存储过程以奇怪的方式执行

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.

我有一个存储过程,它应该使用游标处理表中的行。该过程大部分时间都有效,但有时它不会完全执行。我知道这是因为我有一个简单的调试工具,嵌入到代码中,它将特定的行和变量记录到专用的调试表中。最有趣的是,从PHP运行时,问题总是会发生。如果我使用mysql客户端我从来没有遇到过这个问题。

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.

根据日志,在点7中,刚刚取出光标停止值为假,但执行既不到达点3也不到达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.

附: MySQL版本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.

P.S.S.我设法找到一个相关的问题 - 在游标循环中调用存储过程,而不跳过继续处理程序。正如我的过程的名称所暗示的那样,它从外部过程中的循环内部调用(顺便说一下,循环通过“paths”表,另一个是继续处理程序),所以它类似于那些情况,并且可能是不知何故重要。我已经尝试了链接问题的解决方案,但它也没有帮助。

A solution is found, the answer is posted below.

找到了解决方案,答案如下。

2 个解决方案

#1

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.

尝试在游标提取周围使用开始结束块,并在那里声明一个处理程序。

#2

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.

最初在2行中编码相同的东西的原因是我在开始时没有调试表,我通过SELECT控制执行流程。

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.

似乎存储过程不喜欢什么东西被SELECTed into(不可用)控制台。在我的情况下,这导致了不一致的行为:过程在相同数据的某些任意测试运行中丢弃了执行。如果有人能够阐明这种行为,我很乐意接受这个答案,而不是我的答案。

#1