PHP mysql_query Select fails to return row that was just inserted

Question

I am intermittently seeing a problem when trying to select a row immediately after inserting it in PHP. This issue will only happen once in every 100-1000 occurances. This is using php 4.4, with no option to upgrade / be able to use PDO or mysqli. I was considering using transactions, but both queries execute and would continue to do so even with transactions, but the second query runs and returns empty RS so I doubt that transactions would hatch it.

As an example, take a table with two columns: an auto-increment counter, and a stored value. The value must be unique as well, but joins are made on the counter, not the value. Values can be entered by more than one user at a time, so I cannot simply trust the highest counter to give me the value I just inserted.

    data:
    data_id int(11) UNIQUE AUTO_INCREMENT NOT NULL
    myData varchar(50) UNIQUE NOT NULL

Now each user is able to enter a new $val at any time, and when they enter it, an entry must also be made in the status table.

    statuses:
    status_id int(11) UNIQUE AUTO_INCREMENT NOT NULL
    data_id int(11) NOT NULL
    status int(1) NOT NULL

The code I'm using to accomplish this is:

// Get value
$insData = $_GET['VAL'];
// Insert value
$ins = mysql_query( "INSERT INTO data (myData) VALUES ('" . $insData . "')" )
    or die(mysql_error());
// Get value's id
$res = mysql_query( "SELECT data_id FROM data WHERE myData='" . $insData . "'" )
    or die(mysql_error());
// From here I would insert into the statuses table,
// but the new entry into the data table is sometimes not found!

Once every 100-1000 times the code gets run, the select will execute and return no rows. The data insert will execute correctly. I didn't believe that this was possible because the insert or die forces PHP to wait for a return value so that it would know whether or not to execute the 'or die' portion of the line. Is it possible that PHP has received a return value from the insert command before it has finished executing, thus the select statement immediately following the insert fails to return any rows? And how could this intermittent problem be prevented in the future?

Answer 1

Is the data you're trying to INSERT (when you don't get the expected error message) a duplicate record (ie matches a record that's already in TABLE DATA) by chance?

If so, see

• MySql Gotchas
• select statement always return the last inserted row in php mysql

specifically, quoting from mysqlgatchas:

"...A further idiosyncracy seems to be this behaviour:

CREATE TABLE insert_test2 (
id INT NOT NULL PRIMARY KEY,
txt VARCHAR(32)
);
INSERT INTO insert_test2 VALUES(1, 'foo');
INSERT INTO insert_test2 VALUES(2, 'bar');

Now watch this:

mysql> INSERT INTO insert_test2 VALUES(1, 'bar');
ERROR 1062: Duplicate entry '1' for key 1

Correct behaviour... now watch this:

mysql> INSERT INTO insert_test2 (id, txt) SELECT i.id, i.txt FROM insert_test i 
WHERE i.id=1;
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM insert_test2;
+----+------+
| id | txt  |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.00 sec)

Expected behaviour would be for this statement to fail with the same error as above; instead it appears that the statement was successful, with only the Duplicates: 1 notice indicating the statement was effectively ignored.

Update: in version 4.0.20, and possibly previous versions, this statement returns the expected error message..."

Answer 2

There's not a good explanation for why this occasionally fails, although if your table is MyISAM then I suspect you have some sketchiness creeping into your physical table structures. Otherwise there may be something about the values in your unescaped string that is causing the query not to be able to find what it just inserted.

The correct way to identify the last row you inserted it this:

SELECT LAST_INSERT_ID();

or this:

SELECT @@LAST_INSERT_ID;

These are equivalent, and will always give you the value assigned to the auto-increment column of the last row you inserted -- even if another session has inserted data after you.