I have a habit of backing up everything. When I first installed Drupal, I made the 'mistake' of setting various configurations in the administrator settings to keep records of just about everything.

I went into MySQL today to make a backup of the database. I noticed that several tables are getting quite large. Namely: 'accesslog', 'cache', and 'watchdog'.

I searched the Drupal forums for an hour. However, I couldn't find an answer to my problem. I'm still in the process of learning how to administer Drupal and also MySQL.

So, my question is, how can I 'clear' these database tables (caches) without breaking anything?

Comments

kbahey’s picture

I don't use cache, so I don't have first hand experience in it. But I have seen many posts saying that it is safe to completely delete the contents of the table, and Drupal will just build it as it needs to.

For watchdog, there is a setting in /admin/settings for how long to keep entries. If you have it as 'Never' (keep everything forever), then you may want to set it to 4 weeks.

For accesslog, you can set the duration to keep in /admin/settings/statistics, the maximum is 16 weeks.

For both watchdog and accesslog, you need to setup periodical access to /cron.php so they get cleaned regularly. Perhaps this is what you are missing now?

--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba

robertDouglass’s picture

I clear the other tables (access log and watchdog) as well, but you of course lose this data. When I backup the db, I simply omit these three tables. I only clear cache if I'm programming something that makes a change to the menu structure. Then it is necessary to be able to see the change, as menus get cached. The other two tables only get cleared under very rare circumstances.

- Robert Douglass

-----
www.robshouse.net
www.webs4.com

enthusaroo’s picture

Sorry for the late reply!

So, I can just delete the tables in question (watchdog, cache, and accesslog) and then Drupal will recreate the tables by itself?

I just want to make certain before I delete the tables.

javanaut’s picture

Don't delete the tables, just their contents.

e.g. run the SQL statement to clear the contents of the cache table:
delete from cache;

..as opposed to dropping the table.

carlmcdade’s picture

The proper phrase is "truncate" or "empty" depending on the GUI or database server. If you delete or drop a table then it is gone for good.

---------------------------
www.hivemindz.com (running PHP5)
www.fireorb.org (documentation and hacks)
__________________________
Carl McDade
Information Technology Consult
Team Macromedia

ñull’s picture

I made a backup recently and instead of deselecting the watchdog table I deleted it and got into some trouble.

It had to do with the autoincrement value that remains like it is. After deleting the log entries would not show up anymore and but the pager would show pages that weren't there anymore. I had to manually reset the cardinality.

blueprintz’s picture

I have currently encounter the same problem and would like to know how did you reset the watchdog in your phpmyadmin or database software manually.

My site currently shows the following: After I mistakenly deleted the watchdog from my modules file in phpmyadmin when trying to deselect a table.

Error

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'kaccrav_urbanglam_dba.watchdog' doesn't exist: INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => user [:db_insert_placeholder_2] => Session opened for %name. [:db_insert_placeholder_3] => a:1:{s:5:"%name";s:15:"Kaccrav_ugAdmin";} [:db_insert_placeholder_4] => 5 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.urbanglam.co.uk/user/ [:db_insert_placeholder_7] => http://www.urbanglam.co.uk/user/ [:db_insert_placeholder_8] => 81.170.65.14 [:db_insert_placeholder_9] => 1424123266 ) in dblog_watchdog() (line 160 of /hsphere/local/home/kaccravoyage/urbanglam.co.uk/modules/dblog/dblog.module).

jsimonis’s picture

I've found that the database admin module works great for this. You can empty tables from it, all without leaving your website.

--
Jenni S.

Wolfflow’s picture

Hi, I have my first Drupal Site. After experimenting for 2 month I started a longterm
Multilingual Drupal Site.

Its still in testing and construction fase. Ok here my question:

As I need to figure out many change and setups with modules and Multilanguage Contents "Watchdog" really get filled very fast. How can I do a cleaning (watchdog "mysql" truncate) with an administrative
run menu or something like that, wich call up the cron job ?

Cheers Wolf

Edit: deleted old invalid site link

Contact me for drupal projects in English, German, Italian, Drupal Hosting Support.

Rosamunda’s picture

Use devel module, it´s pretty easy to clear the cache, just one little safe click.

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

venusrising’s picture

Awesome Suggestion about using Devel to clear Cache, I would have never know that and it helped so much!

venusrising

venusrising

As If’s picture

It's quite easy to create administrative menu items (page nodes that run PHP scripts) for these tasks. For example, here is one to clear the "cache" table. First, create a page called "Clear Cache" and paste this into the body:

<?
echo "This page runs a script that clears the main cache.<br>";
$sql = "DELETE from cache WHERE 1";
$result = mysql_query($sql);
echo "It's clear now.<br>";
?>

Then down under "Menu Settings", you give it a name like, oh, let's say... "Clear Cache" and make it a child of the Admin branch (I put it under Site Configuration). The first time you save this page, the script will run. After that, just click it in the menu. Open PHPMyAdmin to check it out.

LVX
TF
-------------------------------------------
Interactive Worlds and Immersive Obsessions
http://www.asifproductions.com

-------------------------------------------
Interactive Worlds and Immersive Obsessions
http://www.asifproductions.com

stevew’s picture

Worked like a charm, and now I can see my new views in action. I was going crazy trying to figure out why they didn't work. Thanks a million!

Steve
--
http://etmeli.us/ - photos, commentary, occasional sarcasm

westbywest’s picture

Some suggested modification to the code above, since it doesn't account for table name prefixes, nor the fact that Drupal 5 has several cache tables that could do for purging.

<?
echo "This page runs a script that clears the main cache.<br>";
db_query("DELETE FROM {cache} WHERE 1");
db_query("DELETE FROM {cache_filter} WHERE 1");
db_query("DELETE FROM {cache_menu} WHERE 1");
db_query("DELETE FROM {cache_page} WHERE 1");
echo "It's clear now.<br>";
?>

Also, I'd recommend putting the "Clear Cache" in its own menu block whose visibility is restricted to certain roles (or use some other access control module). Otherwise, the "Clear Cache" item will be visible to everyone, even if the page itself has restricted node access. C.f. "Blocks content, but doesn't hide menu items?"

Ben West

SwitchBlade’s picture

This thread is a huge help. The script worked with ease on my prod site but the test site still needs to be fixed.

After days of not knowing what I did wrong - Thank you, thank you, thank you!

-SB

roedelius’s picture

I haven't tested this yet, but this is another option using drupal's own cache_clear_all function (which is called in update.php):


    // clear built-in cache:
    cache_clear_all('*', 'cache', TRUE);
    cache_clear_all('*', 'cache_page', TRUE);
    cache_clear_all('*', 'cache_menu', TRUE);
    cache_clear_all('*', 'cache_filter', TRUE);

    // my own addition:
    cache_clear_all('*', 'cache_views', TRUE);

vrc3’s picture

This is a very cool tip. Thank you.

STyL3’s picture

i agree with the others. This is an awesome idea and has been extremely helpful!

mariuss’s picture

I just realized that the update script (/update.php) is clearing the cache at the end of the update process, so you can use this side effect.

Even if you have nothing to update just run the /update.php script ("run the database upgrade script" link + "Update" button) and that will do the trick.

Quint’s picture

thanks for the tip!

tbuccelli’s picture

I've cleared the accesslog table in main db but log entries still are showing up. Why won't they go away if they've been truncated in the db admin?

Todd

lungtung’s picture

subcripts

mrgoltra’s picture

.. and I don't recommend it and it might not work for you.

THE NOT SO PROPER WAY

Backup everything before trying.

This was done on a live site. I had to export the database and those 3 tables where huge because the log was set to never delete records. I think it was close to 700MB. It it was impossible to install this locally because of those 3 tables.

here is what I did.

I emptied those tables and ran update.php (another page that is already open) after. I worked for me but I don't know what effect this will cause in the long run. Site came back up and I ran the test to see if anything was broken. so far soo good site is still up and I change the log setting to hold records for 4 weeks.

So good luck. It fails then restore.

xjessie007’s picture

Here are a few "consolidated" ideas about how to clear Drupal cache.

Tables:

Depending on your configuration, there can be a number of cache tables.

cache, cache_content, cache_filter, cache_menu, cache_page, cache_views

It seems like deleting the content from all of them does not hurt anything. Important note, deleting the content of the tables not the tables themselves.

Devel module:

There are two ways of deleting Drupal cache. Manually delete the data in tables in the DB or let Drupal do the work. Since Drupal is a CMS "system", I think it is always better to use what the system already provides than to dig into the code.

The Devel module provides functionality that deletes cache. For those not familiar with this module, see this page: http://drupal.org/project/devel. If you are just starting with the Devel module and want to use it just to delete cache and nothing else, then you only need to enable the Devel piece of the Devel module, no need to install Generator, Macro, and the other one.

Module block: After you install and enable the Devel module, you need to enable a Block that is associated with it. The Empty cache link is not in the Administer menu as one might expect. Go to Blocks and enable the Devel block.

Security: You do not want everyone to be able to access this module, so go to Administer -> User Management -> Access Control and allow only the admin to see this module.

Here you can find print screens related to this:
Drupal Devel module installation (@ Maxi-Pedia.com)
Drupal Devel module security (@ Maxi-Pedia.com)

Now you should be able to see the Empty cache link in the Devel block.

Direct link: In case you have troubles setting this up, you can also access the Empty cache functionality by navigating to devel/cache/clear. (See function devel_menu() in devel.module)

Where the functionality is defined: See the function devel_cache_clear() in devel.module.

Drawback: It looks like this function does not clean up all the tables that need to be cleaned up. At least, it did not clean everything for me. In this case, you might want to do the residual clean up using the cache_clear_all() function.

See this page for more details: How to delete or clear Drupal cache tables?.

cache_clear_all():

The Devel module is the preferred way of clearing the cache because it is internal to Drupal and has been tested. However, if you want to get into the custom stuff, you can use the cache_clear_all() function which is defined in cache.inc.

Just set up a page and throw a new block to this page. The block should display on this page only. For the block, select PHP input when editing it and include something like cache_clear_all('*', 'cache', TRUE);. Do it for all the tables you want to be cleaned up.

Note, again, you would not want this block to be accessible by everyone, and you do not want it to be indexed by robots, so you might need to take some security measures. See this page for more details: Drupal cache clean up - cache_clear_all().

Cron:

I do not exactly know why, but I think it is a good idea to finish the clean up by running cron.

Cache and throttle and expire:

A few important points. If you delete your cache tables using Devel or the function and go to the database, you will see that they are populated with some stuff again (just a little of data, but still..). This can happen for two reasons.

a) I think drupal instantly caches any blocks that you have throttled if you use throttle. So, even if you delete cache, some of it comes right back.

b) It looks to me like the Devel module and the cache_clear_all deletes only data with not having zero in the expiry column on the cache tables. So, if you really really want to clean up your web, you probably need to dump the remaining lines with expiry=0 manually. (Just my experience, I have not researched why some data has expiry 0, and what it means.)

SQL delete from:

delete from cache; Although this technically works, I would be careful. This deletes the data, but depending on the database, it may not reset table index. I do not know if indexing matters on these tables, or if Drupal is smart enough to fix them if needed, but just judging by my experience, knowing that not resetting table index after deleting data can mess up things, I would be careful with this one. Just a thought...

www.finance-management.cz
www.maxi-pedia.com

Wolfflow’s picture

xjessie007.

In representation of all of us (Drupalers, Members, Funs and Newcomer) a big THANKs for your issue!!

P.S. I just want to add something very important to your Posting:

BEFORE doing any cache clearing, if you want to setup your custom procedure on your production site,
make a backup of your database, so if something goes wrong your are able to restore everything.

Share your experience with the Open Source Community
it's not only a choice but a Life Philosophy !!!
http://www.adaccs.at : A Drupal advanced CSS Content System - Community Project

Contact me for drupal projects in English, German, Italian, Drupal Hosting Support.

ratnesh aarohi’s picture

Thanks for the extensive guide.
Is it a good idea to clear the cache (and watchdog) using the EMPTY button provided in PHPMYADMIN.
Can i just select the table and click on EMPTY to clean it off.

drsmith’s picture

Has anyone successfully done this through PHPmyAdmin?

As If’s picture

Sure. Yes, you can select every table that begins with "cache" and empty it. You can do the same with watchdog. I haven't personally emptied accesslog, but I don't think it would cause any problems. If you're worried you can always use the "Operations" tab to create a copy of the original table with a slightly different name, so you can replace it later.

-------------------------------------------
Interactive Worlds and Immersive Obsessions
http://www.asifproductions.com

greg.harvey’s picture

Use Drush! http://drupal.org/project/drush

Once configured, switch to the root of your Drupal directory (e.g. cd /path/to/drupal) and execute this command: drush cache clear. Works with all Drupal version, AFAIK.

scott.browne’s picture

I just copied the table structure and swap names on the tables, can hold on to that table for a couple days incase something happens then delete it. Worked fine for me so far.

AhmadZain’s picture

better to use "drush cc all"

or the flush tool with the admin menu module