Query returning wrong record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Query returning wrong record

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Query returning wrong record

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

When do your (2) and (3) happen? Using transactions? If so, which isolation level? or any locks?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

They happen in that order, everything is autocommit. You can run the queries in any order, same result.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
Post Reply