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;
}
