Page 1 of 1

Query returning wrong record

Posted: Fri Jul 27, 2007 12:27 pm
by Benjamin
Get this..

1. record_id is a primary key..
2. The original value of field_one was foo
3. field_one has been updated to bar with a replace into query.

The following results are returned with queries ran from PHP scripts..

Code: Select all

select field_one from table_one where record_id = 162; -- returns foo
select field_one from table_one where record_id =  162; -- returns bar
select SQL_NO_CACHE field_one from table_one where record_id = 162; -- returns bar
select field_one from table_one where record_id = '162'; -- returns bar
When ran from a terminal, all queries return bar, which is the correct result. Obviously a cache issue, but this makes for some real fun when tracking down bugs. It appears that replace into queries aren't clearing the cache.

MySQL Version 5.0.37

Re: Query returning wrong record

Posted: Fri Jul 27, 2007 2:31 pm
by superdezign
astions wrote:When ran from a terminal, all queries return bar, which is the correct result. Obviously a cache issue, but this makes for some real fun when tracking down bugs. It appears that replace into queries aren't clearing the cache.
8O SQL has a cache? Ohhhh. I ran into a problem like that before, and just ended up dismissing it as a bug with REPLACE, and used an SQL IF statement to determine whether to UPDATE or INSERT. Maybe I was right (but not in the sense that I thought). Oh well... REPLACE sucks anyway.

Posted: Fri Jul 27, 2007 3:25 pm
by timvw
When do your (2) and (3) happen? Using transactions? If so, which isolation level? or any locks?

Posted: Fri Jul 27, 2007 6:34 pm
by Benjamin
They happen in that order, everything is autocommit. You can run the queries in any order, same result.

Posted: Fri Jul 27, 2007 6:52 pm
by timvw
What order? AS in: spell out the exact sequence of statements

(1) insert x
(2) insert x on duplicate key update ...
(3) select
(4) update
(5) select ...

Posted: Fri Jul 27, 2007 7:42 pm
by Benjamin
1. SELECT field_one FROM table_one WHERE record_id = 162; -- returns foo
2. replace into query updates field_one to bar for record_id 162

Then the following queries in any order..

Code: Select all

SELECT field_one FROM table_one WHERE record_id = 162; -- returns foo
SELECT field_one FROM table_one WHERE record_id =  162; -- returns bar
SELECT SQL_NO_CACHE field_one FROM table_one WHERE record_id = 162; -- returns bar
SELECT field_one FROM table_one WHERE record_id = '162'; -- returns bar 
The result for the original query is cached, but not cleared by the replace into query.