{"id":27,"date":"2007-07-27T11:02:07","date_gmt":"2007-07-27T15:02:07","guid":{"rendered":"http:\/\/blogs.n1zyy.com\/andrew\/2007\/07\/27\/query-uncache\/"},"modified":"2007-07-27T11:02:07","modified_gmt":"2007-07-27T15:02:07","slug":"query-uncache","status":"publish","type":"post","link":"https:\/\/blogs.n1zyy.com\/andrew\/2007\/07\/27\/query-uncache\/","title":{"rendered":"Query Uncache"},"content":{"rendered":"<p>Apparently, in MySQL 5.0.36, there was a bug that prevented <code>INSERT INTO ... ON DUPLICATE KEY UPDATE ...<\/code> queries from flushing the query cache for the table they were modifying. According to the MySQL manual:<\/p>\n<blockquote><p>If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE.<\/p><\/blockquote>\n<p>But the evidence speaks for itself:<\/p>\n<pre>mysql&gt; insert into sessions values ('fooh', 'blah', now())\n  on duplicate key update session_data=values(session_data);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql&gt; select * from sessions where session_id='fooh';\n+------------+--------------+---------------------+\n| session_id | session_data | date_modified       |\n+------------+--------------+---------------------+\n| fooh       | blah         | 2007-07-27 07:41:57 | \n+------------+--------------+---------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; insert into sessions values ('fooh', 'blah2', now())\n  on duplicate key update session_data=values(session_data);\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql&gt; select * from sessions where session_id='fooh';\n+------------+--------------+---------------------+\n| session_id | session_data | date_modified       |\n+------------+--------------+---------------------+\n| fooh       | blah         | 2007-07-27 07:41:57 | \n+------------+--------------+---------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; select sql_no_cache * from sessions where session_id='fooh';\n+------------+--------------+---------------------+\n| session_id | session_data | date_modified       |\n+------------+--------------+---------------------+\n| fooh       | blah2        | 2007-07-27 07:41:57 | \n+------------+--------------+---------------------+\n1 row in set (0.00 sec)<\/pre>\n<p>Now, for us the fix is as simple as adding the <code>SQL_NO_CACHE<\/code> hint to our queries (or updating our version of MySQL, possibly). And, actually, adding it to the session queries isn&#8217;t a bad idea anyways &#8212; there&#8217;s not really any point in attempting to cache data from a table that gets written to with every page load. This could free up space in the query cache for other data that might have a chance of sticking.<\/p>\n<p>As far as I can tell, this bug was fixed in 5.0.41 (at the latest).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Apparently, in MySQL 5.0.36, there was a bug that prevented INSERT INTO &#8230; ON DUPLICATE KEY UPDATE &#8230; queries from flushing the query cache for the table they were modifying. According to the MySQL manual: If a table changes, all cached queries that use the table become invalid and are removed from the cache. This [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-27","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts\/27","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/comments?post=27"}],"version-history":[{"count":0,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/posts\/27\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/media?parent=27"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/categories?post=27"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/andrew\/wp-json\/wp\/v2\/tags?post=27"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}