CODEWITHSUNDEEP
mysqltransactionsmariadb
passion053
passion053

Reputation: 493

Why MySQL (MariaDB) update transaction returns '0 row affected'?

I have something weird with MySQL. (Oh, first of all, sorry for my poor English.)

I've made the simple table and stored procedure. Table definition is...

CREATE TABLE numTable (
    firstNum INT,
    secondNum INT
);
INSERT INTO numTable (firstNum, secondNum) VALUES (1, 2);

and the SP is...

DELIMITER ;;

CREATE PROCEDURE updateNum (
    IN num1 INT,
    IN num2 INT
)

BEGIN

START TRANSACTION;

    UPDATE  numTable
    SET     firstNum = num1, secondNum = num2;

COMMIT;

END ;;

DELIMITER ;

Then I executed the SP like this...

CALL updateNum (3, 4);

And MySQL returns ...

0 row(s) affected

When I saw this result, I thought 'Is there any syntax error?' But, no. UPDATE query worked fine. numTable's data changed from (1, 2) to (3, 4).

And I also found this. I removed the 'START TRANSACTION;' and 'COMMIT' statement. and execute updateNum SP again. The result was ...

1 row(s) affected

This time, UPDATE query worked fine too. numTable's data has successfully changed.

Why this difference has happen? Is there a way I can get a right affected rows with using TRANSACTION statement?

I tested this at MySQL 5.6.27, MariaDB 10.0.21 and MariaDB 10.1.8 and results are same as above.

Thank you for read my question.

Upvotes: 2

Views: 3777

Answers (2)

Drew
Drew

Reputation: 24960

similar to @Leow's, but I have found that without a transaction, and without it happening immediately after the update, all is lost. So:

START TRANSACTION;
update ...
select row_count() into @theCount; -- saved for posterity, perhaps for use at end
COMMIT;
select @theCount;

Upvotes: 2

Leow Kah Man
Leow Kah Man

Reputation: 455

You will get the affected rows using

ROW_COUNT()

so, SELECT ROW_COUNT() at the end

Upvotes: 3

Related Questions