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/