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