MySQL results not updating? Check the query cache
A few days ago I was testing some changes to my local database (MySQL) and was having trouble getting it to update. I changed the category of some rows in my database, but when I ran a query on my testing site nothing had changed.
Little did I know, my SQL server had cached the results for that query and (for some unknown reason) didn’t update the cache. Here’s something to keep in mind - I never told the database to cache this query, so I had no idea what was happening. Here’s an example of someone else having the same problem.
What to do
If you’re running MySQL 8.0 or higher, you don’t have to worry about the query cache, it’s been removed. For anyone running an older version of MySQL, here are a few ways to purge the cache.
One way to make sure your query cache is empty is to reset the query cache using the RESET QUERY CACHE query. Run the query however you normally run MySQL queries on your server. Running a FLUSH TABLES statement will also clear the query cache. You can find more information on clearing the query cache in the MySQL documentation.
Another method is disabling the query cache altogether. You can do this by setting the query_cache_type to 0 or by setting the query_cache_size to 0. In the documentation, it’s suggested that you set the query_cache_type to reduce overhead.
Here’s an example of setting those variables and the result. You can use the GLOBAL keyword to set them globally or use the SESSION keyword to set it on a session-by-session basis.
Set the query cache size:
mysql> SET GLOBAL QUERY_CACHE_SIZE=0;
Query OK, 0 rows affected, 1 warning (0.32 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1287 | '@@query_cache_size' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.84 sec)
Set the query cache type:
mysql> SET GLOBAL query_cache_type = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1287 | '@@query_cache_type' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
If you still need more information, check out these helpful links: