How do I clear cache using an SQL query?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

For me (Drupal 9.1.3) - works below:

TRUNCATE cache_bootstrap;                  
TRUNCATE cache_config;                     
TRUNCATE cache_container;                  
TRUNCATE cache_data;                       
TRUNCATE cache_default;                    
TRUNCATE cache_discovery;                  
TRUNCATE cache_dynamic_page_cache;         
TRUNCATE cache_entity;                     
TRUNCATE cache_menu;                       
TRUNCATE cache_page;                       
TRUNCATE cache_render;                     
TRUNCATE cache_toolbar;                    
TRUNCATE cachetags;

After TRUNCATE this tables clear browser's history. That's all.

Answer 5

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

Answer 6

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;

Answer 7

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

Answer 8

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/