# How do I clear cache using an SQL query?

After a module update, my site has become unusable, and only shows a php error message. I tried circumventing the problem with drush cc, but that didn't help.

Also tried with a custom php script but that has issues finding my DRUPAL_ROOT

I just want to clear the cache tables from Drupal 7 directly on the mysql server, but I'm unsure which tables should be cleared for that and especially which I should not clear.

Do I just need to clear all the [SITE-PREFIX_]cache* tables?

• Could you please add the query you used? – ipwa Feb 13 '12 at 20:10
• actually I was looking for a Query to use with something like: EMPTY WHERE tablename IS LIKE 'prefix_chache%' but in the end I just used phpmyadmin to drop the tables because I couldn't find such a query. – FLY Feb 14 '12 at 7:54
• Just as a note, make sure you've run the database updates either through drush updb or from the admin interface, or by simply going to /update.php after a module/core update. – Beebee Aug 13 '14 at 10:47

Any well written module that has a cache, should prefix it with cache, meaning that the answer to your question is "Yes".

In the odd event that a module caches data somewhere else, you can check your modules for implementations of hook_flush_caches, and see what they remove.

Yes, you can simply clear (TRUNCATE) all cache* tables.

This worked well for me:

TRUNCATE TABLE cache;
TRUNCATE TABLE cache_block;
TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_field;
TRUNCATE TABLE cache_filter;
TRUNCATE TABLE cache_form;
TRUNCATE TABLE cache_image;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_path;
TRUNCATE TABLE cache_token;
TRUNCATE TABLE cache_update;


If you use drush, run drush sql-cli and paste the above in there.

These commands might not clear all cache tables of your specific site but it can help fix some errors. Afterwards you can try drush cc all to clear the rest.

• It's also easy to add extra TRUNCATE queries for any additional cache_ tables that might be present on your site. – millionleaves Nov 12 '15 at 21:25

To clear all cache tables run this command in your server terminal.

# Truncate cache tables in MySQL regardless of the active cache backend
echo "SHOW TABLES LIKE 'cache%'" | $(drush sql-connect) | tail -n +2 | xargs -L1 -I% echo "TRUNCATE TABLE %;" |$(drush sql-connect) -v


this will loop through all cache tables and truncate them in one command.

• This works, tested it and all is zero. – Marko Blazekovic May 6 '16 at 11:57
• This helped me get a site running again when it had unclear "Class not found" errors. – user18099 Oct 16 '17 at 14:30
• Thanks Mohammad, I like your one-liner solution better than the accepted answer, because it's more flexible: the pattern catches custom/contrib cache tables as well, not only the hard-coded lists. – Balu Ertl Apr 26 at 9:30

You can either TRUNCATE/DELETE each table separately which starts from cache_ like:

DELETE FROM cache;
DELETE FROM cache_block;


and so on (check via drush sqlq "SHOW TABLES LIKE 'cache_%'").

Or generate query with list of tables and pass into drush to truncate them, e.g.:

echo "SHOW TABLES LIKE 'cache%'" | $(drush sql-connect) | tail -n +2 | xargs -L1 -I% echo "DELETE FROM %;" |$(drush sql-connect) -v


or:

echo "SELECT CONCAT('DELETE FROM ', GROUP_CONCAT(TABLE_NAME SEPARATOR ';DELETE FROM ') , ' cache;' ) AS statement FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'cache_%'" | drush sqlc --extra=--skip-column-names | drush sqlc


### Memcache

If you're using memcached, you also need to flush the caches there, e.g. (Bash syntax):

echo flush_all > /dev/tcp/127.0.0.1/11211


Or, you can import you mysql dump with already truncated cache* tables:

gunzip -c ~/database.sql.gz | grep -v 'INSERT INTO cache'| mysql --user=root drupal


drush cr and/or drush cc all is supposed to clear all caches, but indeed some cache tables do not get cleared. The following (simplified) command does truncate all caches:

drush sql-query "SHOW TABLES LIKE 'cache%'" | while read table; do drush sql-query "TRUNCATE \$table"; done;


JFR in this article an other flexible pattern-based solution is offered in a form of raw SQL-query, but was not working for me as good as Mohammad's: https://thebarton.org/clear-drupal-cache-sql-query/