开发者

MYSQL Procedures run, but return 0 rows affected

I call a procedure, it runs, and the console says "0 rows affected". Is this normal behavior for a MySQL procedure ?

The procedures are clearly doing what they should. One procedure has 2 insert statements, another has an insert and update statement, and I've seen the results with my own eyes. There are indeed rows being affected.

I'm not s开发者_Go百科ure that I would use that result later on, but it seems like I'd want to get an accurate response from my DB whether or not anything was updated, especially when its expected.

Thoughts ?

MySQL 5.5 if it matters, and the procedures use transactions over auto-committed statements.

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_issue`(user_id SMALLINT, title varchar(255), body LONGTEXT)
BEGIN
    DECLARE MYUSERID SMALLINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
START TRANSACTION;
    INSERT INTO tracker.issue (user_id, title, body, creation_date, last_mod_date) values (user_id, title, body, CURDATE(), CURDATE());
    UPDATE user_activity SET last_new_issue = CURDATE(), post_count = post_count + 1 WHERE user_activity.user_id = user_id;
COMMIT;
END

Edited to show the actual query. Also I've been searching and as best as I can tell this is a known issue over a year and a half old. So I suppose this one can be closed.


the "0 rows affected" response is for the last statement executed in the stored procedure.

usually i track the number of rows effected by manually counting them into session variables

DELIMITER $$
CREATE  PROCEDURE `create_issue`(user_id SMALLINT, title varchar(255), body LONGTEXT)
BEGIN
    DECLARE MYUSERID SMALLINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
    SET @inserted_rows = 0;
    SET @updated_rows = 0;
START TRANSACTION;
    INSERT INTO tracker.issue (user_id, title, body, creation_date, last_mod_date) values (user_id, title, body, CURDATE(), CURDATE());
    SET @inserted_rows = ROW_COUNT() + @inserted_rows;
    UPDATE user_activity SET last_new_issue = CURDATE(), post_count = post_count + 1 WHERE user_activity.user_id = user_id;
    SET @updated_rows = ROW_COUNT() + @updated_rows;
COMMIT;
END
$$

the session variables can then be read after the SP was executed.

i am not sure if it is possible to override the response from the ROW_COUNT() function by setting a value to a variable,


I guess this is a reported bug. May be a good question for MySQL mailing list/forum. http://bugs.mysql.com/bug.php?id=44854


Something definitely isn't right. A sproc should still return the number of rows affected if there are multiple inserts occurring. I'm using the same version of MySQL and this works fine for me.


Are you sure you're not doing something like that

...SET col1='value1' AND col2='value2'...

instead of

...SET COL1='value1', col2='value2'...

Could you post your stored procedure?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜