After SP Insert, next page have an empty result until reloaded

Question

I have a Stored Procedure (SP from now on) that inserts data to the database (SaveClient, see below). When the SP is done I redirect the PHP page to a different PHP page that lists the entries (FetchObjectList, see below). The list does not return the newly created record until I then reload/refresh the page.

The stored procedure has a COMMIT at the end, I close the database connection in the PHP code after the SP is called and there is a check for errors but nothing goes wrong.

The page itself returns a 200 statuscode which means it isn't cached so can't be browserrelated either.

The current workaround is a sleep(1) in the PHP code but when the code goes live I have no idea if it will suffice. I'd ofcourse rather have MySQL dish out the correct resultset.

EDIT: I'm using the MySQLi object interface of PHP, might be useful to know. ;)

My devcomputer got PHP 5.2.17, MySQL 5.0.51a (InnoDB) and Apache 2.2.17 installed and running on Windows 7 x64.

UPDATE

Added the following line CALL FetchObjectList('client_tbl', NULL, NULL, 1, 'client_tbl.name ASC', NULL, NULL); to the end of SaveClient. The resultset does not have the newly created client in the presented resultset.

UPDATE 2

I tried using the SQL_NO_CACHE as seen here but to no avail.

I will now try the same SQL directly in PHP instead of calling the SPs.

UPDATE 3 - 20 september

I've tried any reasonable answer/comment I've got so far without any luck. I tried to update my PHP and MySQL version today (since I today learned that the live server will run on PHP 5.3.something and MySQL 5.1.something) but did not get it to work. I need to update the PHP to get a more recent php_mysqli.dll/libmysql.dll since the one I got has only supports up to 5.0.51a and there might be my problem since nothing in the actual DB has worked. I tried the libmysql.dll from the MySQL install to no avail.

Note that I also changed the PHP code that I've included since I actually copied the wrong one that was calling the user_tbl and not the client_tbl and also simplified it (removed multiqueries) but still the same result.

I don't know what will happen to the bounty, if it reverts back to me I'll add it again.

Stored Procedure SaveClient

DELIMITER //

DROP PROCEDURE IF EXISTS work.SaveClient//

CREATE PROCEDURE work.SaveClient(
        IN ObjectID INT,
        IN UserID INT,
        IN ClientName VARCHAR(60),
        IN VersionFrom DATETIME,
        IN VersionTo DATETIME)
root:BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

/* 
    Default values ---------------------------------------------------------------------------------------------------------
*/
    # Used to block INSERT/UPDATEs
    SET @DoChanges      = TRUE;

    SET @Fields     = '*';
    SET @Version        = NULL;
    SET @UserVersion    = NULL;
    SET @DateNow        = NOW();
    SET @VersionActive  = CONCAT(
        '( ( NOW() BETWEEN ', 
        'version_from AND ', 
        'version_to ) OR ( ', 
        'version_from < NOW() AND ', 
        'version_to IS NULL ) )'
    );

    IF VersionFrom IS NULL THEN
        SET VersionFrom = @DateNow;
    END IF;

/*
    Search for client ------------------------------------------------------------------------------------------------------
*/
    IF ObjectID IS NOT NULL THEN
        SET @Client = CONCAT(
            'SELECT version INTO @Version FROM client_tbl WHERE object_id = ',
            ObjectID,
            ' AND ',
            @VersionActive
        );
        PREPARE stmt FROM @Client;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        # Check if there are any changes
        IF @Version IS NOT NULL THEN
            SELECT name INTO @Name FROM client_tbl WHERE name = ClientName AND version = @Version;
            IF @Name = ClientName THEN 
                SET @errorMsg = "Duplicate entry";
                SET @errorCode = "S0000002";
                SELECT @errorCode, @errorMsg;
                LEAVE root;
            END IF;
        END IF;

    END IF;
/*
    Search for user ---------------------------------------------------------------------------------------------------------
*/
    # Create this as a function
    IF UserID IS NOT NULL THEN
        SET @User = CONCAT(
            'SELECT version INTO @UserVersion FROM user_tbl WHERE object_id = ',
            UserID,
            ' AND ',
            @VersionActive
        );
        PREPARE stmt FROM @User;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    IF @UserVersion IS NULL THEN
        SET @errorMsg = "User is missing";
        SET @errorCode = "U0000099";
        SELECT @errorCode, @errorMsg;
        LEAVE root;
    END IF;

/*
    Add the client ---------------------------------------------------------------------------------------------------------
*/
    # Close the current version
    IF @Version IS NOT NULL THEN 
        IF @DoChanges = TRUE THEN 
            CALL UpdateVersion(
                ObjectID, 
                UserID, 
                @Version, 
                @DateNow, 
                'client_tbl'
            );
            SET @Version = @Version + 1;
        END IF;
    ELSE
        SET @Version = 1;
    END IF;

    IF @DoChanges = TRUE THEN 

        IF ObjectID IS NULL THEN
            INSERT INTO 
                object_tbl 
                (
                    object_class_id, 
                    created,
                    created_by
                )
                VALUES(
                    2,
                    NOW(),
                    UserID
                )
            ;
            SET ObjectID = LAST_INSERT_ID();
        END IF;

        INSERT INTO 
            client_tbl 
            (
                object_id, 
                version, 
                version_from, 
                version_to, 
                changed, 
                changed_by, 
                name
            ) 
            VALUES(
                ObjectID,
                @Version,
                VersionFrom,
                NULL,
                @DateNow,
                UserID,
                ClientName
            )
        ;
    END IF;

    COMMIT;
END //

DELIMITER ;

Stored Procedure FetchObjectList

DELIMITER //

DROP PROCEDURE IF EXISTS work.FetchObjectList//

CREATE PROCEDURE work.FetchObjectList(
        IN ObjectType VARCHAR(60),
        IN ObjectSubType VARCHAR(60),
        IN ObjectSubID INT,
        IN IsActive INT,
        IN OrderBy VARCHAR(100),
        IN SetStart INT,
        IN MaxResults INT)
root:BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    # Allow the "JSON" output be a max of 8kb
    SET GLOBAL group_concat_max_len = 8096;

/* 
    Default values ---------------------------------------------------------------------------------------------------------
*/
    SET @Fields     = '*';
    SET @VersionWhere   = '1'; # Get everything
    SET @Special        = '';
    SET @OrderBy        = '';
    SET @SetStart       = '';
    SET @MaxResults     = '';
    SET @JoinIn     = '';

    IF IsActive = 1 THEN
        SET @VersionWhere = CONCAT(
            '( NOW() BETWEEN ', 
            ObjectType, 
            '.version_from AND ', 
            ObjectType, 
            '.version_to OR ( ', 
            ObjectType, 
            '.version_from < NOW() AND ', 
            ObjectType, 
            '.version_to IS NULL ) )'
        );
    END IF;

    IF OrderBy != '' THEN
        SET @OrderBy = CONCAT(
                'ORDER BY ', 
                OrderBy
        );
    END IF;

/*
    Specials for each type -------------------------------------------------------------------------------------------------
*/

/*
    - Clients ------------
*/

    IF ObjectType = 'client_tbl' THEN
        SET @Fields = '
            *, 
            client_tbl.object_id AS object_id, 
            (
                SELECT 
                    COUNT(*) AS Total 
                FROM 
                    client_user_privilege_tbl cup 
                WHERE 
                    cup.client_id = client_tbl.object_id 

            ) AS usercount
        ';
    END IF;
/*
    - Configuration ------------
*/

    IF ObjectType = 'configuration_tbl' THEN
        SET @Fields = '
            *
        ';
    END IF;
/*
    Add upp the query to run -----------------------------------------------------------------------------------------------
*/
    SET @Query = CONCAT(
        'SELECT ',
        @Fields,
        ' FROM ', 
        ObjectType, 
        ' ',
        @JoinIn, 
        ' WHERE ', 
        @VersionWhere,
        ' ',
        @Special, 
        @OrderBy

    );

    PREPARE stmt FROM @Query;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

    COMMIT;

END //

DELIMITER ;

PHP CODE SNIPPET (Updated 20 september)

$query = "CALL FetchObjectList('client_tbl', NULL, NULL,  1, NULL, NULL, NULL)";
addTrace($query);

$rs = $db->query($query);
if( $rs ) {
    addTrace('Query done -> Results: ' . $rs->num_rows);
    while($r = $rs->fetch_assoc()){
        $fetchArray[] = $r;
    }
    $count = $rs->num_rows;
    $rs->close();
    $db->next_result();
} else {
    addTrace('Query failed -> ' . $db->error);
    flushTrace();
    exit;
}

Answer 1

Since it's a pretty aged version of mysql it would not surprise me that this would be bug related but one thing I would -in your place- want to know is if this would work by not using transactions at all. (e.g. autocommit = on).

For that version 5.0 I would also check the query cache and disable it all together instead of per query (see SHOW VARIABLES LIKE 'have_query_cache'; SET GLOBAL query_cache_size =0; ). That would at the least eliminate those playing a role in this problem, reproduce(or try) the problem and see if anything changed. If not, I would start searching for specific bugs, especially when the query cache is disabled and it still does this without using transactions.

I verified the support for 5.0 mysql (innodb).

• innodb_flush_log_at_trx_commit = 1
• innodb_flush_method = O_DIRECT

Set these options specifically in your my.cnf, the top one is most important. They explain nicely what those do.

See http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit