Called MySQL Stored Procedure via PHP does not execute properly

Question

This one has got me foxed, and I'm hoping the solution is a "you know why that isn't working, don't you?" type of reply.

Here goes: Via PHP I am calling a MySQL stored procedure thus:

$sql = "CALL sp_test;";
$mysqli->query($sql);

No problem there. I know the Stored Procedure gets executed.

Here's the Stored Procedure that I have striped for this example, and it is still producing the same problem.

DELIMITER \\
DROP PROCEDURE IF EXISTS sp_test\\

CREATE PROCEDURE sp_test()
DETERMINISTIC

BEGIN
DECLARE exit_loop TINYINT(1) DEFAULT 0;
DECLARE v_TableName VARCHAR(50); DECLARE v_ScanID INT(11); DECLARE v_ScanType TINYINT(4); 
    DECLARE v_TitleID INT(11); DECLARE v_VolumeNo INT(11); DECLARE v_IssueNo VARCHAR(10); DECLARE v_IssueDate INT(11); DECLARE v_FIDateUnknown TINYINT(4); DECLARE v_PureHavocScan TINYINT(4);

-- declare cursor for employee email
DECLARE storeroom_cursor CURSOR FOR SELECT TableName, ScanID, ScanType, FIDateUnknown, TitleID, VolumeNo, REPLACE(IssueNo, "\'", "") AS IssueNo, IssueDate FROM t_storeroom ts JOIN t_issueguide tig ON ts.TitleID = tig.ComicTitleID;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

OPEN storeroom_cursor;

get_purehavoc: LOOP

    FETCH storeroom_cursor INTO v_TableName, v_ScanID, v_ScanType, v_FIDateUnknown, v_TitleID, v_VolumeNo, v_IssueNo, v_IssueDate;

    IF exit_loop = TRUE THEN 
        LEAVE get_purehavoc;
    END IF;

            CASE v_ScanType

                WHEN '1' AND v_FIDateUnknown = 0 THEN
                    SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");

                WHEN '1' AND v_FIDateUnknown = 1 THEN
                    SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");

                -- WHEN '2' THEN
                    -- Do something

            END CASE;

            PREPARE statement FROM @sql_text;
            EXECUTE statement;  

            -- Setting the PureHavocScan variable to enter into the PREPARE STATEMENT.
            IF @RowCount = 0 THEN 
                SET @PureHavocScan = 1;
            ELSE                    
                SET @PureHavocScan = 0;
            END IF;

            SET @ScanID = v_ScanID;
            SET @TableName = v_TableName;

            -- SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ? WHERE TableName = ? AND ScanID = ? ");
            SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ", @PureHavocScan, " WHERE TableName = '", @TableName, "' AND ScanID = ", @ScanID, ";");
            PREPARE statement FROM @sql_text;
            EXECUTE statement;

END LOOP get_purehavoc;

DEALLOCATE PREPARE stmt;
CLOSE storeroom_cursor; 
END//
DELIMITER ;

When I run this exact Stored Procedure within my MySQL database admin tool (Navicat in my case), the stored procedure works 100% as intended and ALL the rows that should get updated DO get updated.

However, when I run this exact Stored Procedure via PHP, ONLY 1 (sometimes 2) rows get updated.

Other stored procedures that get called in exactly the same way work fine. It's just this one. The ONLY difference with the one that's failing, is that the MySQL command is an UPDATE, as opposed to the SELECTS and INSERTS that work fine.

I've been at this for two days now and have exhausted all 'my' knowledge. So if someone can put me out of my misery, I'd be mightily grateful.

How do you determine that only one (or sometimes two) rows get updated? — Ro Achterberg, Dec 17 '18 at 17:40
Beware updating the table over which your cursor is iterating https://stackoverflow.com/questions/30365427/is-it-legal-to-update-cursors-querying-table-in-the-loop-of-fetching-data-from — lufc, Dec 17 '18 at 17:48
Ro Achterberg - When I run the same procedure via my 'database admin tool' I receive all the 'expected' results (i.e. 100+ rows get updated correctly). But after the PHP CALL command takes place, a table in question only ever gets '1' row updated. Another 'different' table may get '2' rows updated. I've used many tables and the successfully undated row count NEVER exceeds '2'. I'm starting to think the problem is happening because I'm updating a table that I'm iterating through (as LUFC) has suggested. — Alan N, Dec 17 '18 at 18:43
lufc - I investigated your 'iterating cursor' suggestion (i.e. INSENSITIVE and ASENSITIVE cursors). I even tried creating a new temp table and UPDATING that with the new information, but it STILL failed in exactly the same way. Incredible! The row that can get updated on each iteration only updates itself and no other rows. So the table cannot kind of re-order itself somehow, as may occur when updating a table that is being iterated through. There has to be something else that I am simply not seeing here. Thanks for the suggestion though. — Alan N, Dec 17 '18 at 19:45
Are you sure you're connecting to the same database in both situations? Are you using the same MySQL credentials? — Barmar, Dec 17 '18 at 19:49
Other than that, the database can't tell the difference between connections from PHP versus Navica, and it's hard to see how a procedure could execute differently. — Barmar, Dec 17 '18 at 19:50
The PHP is definitely connecting to the same database. I have tested it by coding the PHP to call a much simpler Store Procedure immediately prior to it calling the troublesome Stored Procedure, and it executes it swiftly and correctly. I agree, the database shouldn't tell the difference between connections, but somehow something isn't working right. I'm now wondering if my WAMPServer is somehow less forgiven of bad code then Navicat is (although I think the code isn't that bad). i.e. Navicat may realise a variable contents is a string and automatically inserts quote marks (') around it. — Alan N, Dec 17 '18 at 20:41

score 0 · Answer 1 · answered Dec 17 '18 at 17:49

This CASE statement is not correct:

        CASE v_ScanType

            WHEN '1' AND v_FIDateUnknown = 0 THEN
                SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");

            WHEN '1' AND v_FIDateUnknown = 1 THEN
                SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");

            -- WHEN '2' THEN
                -- Do something

        END CASE;

CASE <expression> WHEN <condition> THEN ... is a shortcut for CASE WHEN (<expression>) = (<condition>) THEN .... So your code is not comparing v_ScanType to '1' or '2', it's comparing it to ('1' AND v_FIDateUnknown = 0). So it's just comparing the variable to the TRUE or FALSE result of the comparison with v_FIDateUnknown.

You need to write it without the shortcut.

        CASE

            WHEN v_ScanType = '1' AND v_FIDateUnknown = 0 THEN
                SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");

            WHEN v_ScanType = '1' AND v_FIDateUnknown = 1 THEN
                SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");

            -- WHEN v_ScanType = '2' THEN
                -- Do something

        END CASE;

I'm not sure why this would only cause it to fail when you call the procedure from PHP. Maybe it's just a coincidence. But it's hard to reason about a procedure with such a blatant error.

I changed the CASE statement as Barmar suggested, but it still failed in exactly the same way, unfortunately. Thanks for the CASE tip though. — Alan N, Dec 17 '18 at 18:12

Called MySQL Stored Procedure via PHP does not execute properly

1 Answers1

Linked