How to cleanup Cache folder

[rgurgel]

Hi,

I have deleted older notes and a lot of e-mail attachment that is not important for me, but I know that files still phisically on my hard disk. I'm not talking about the "deleted flag item" on database, I'´m talk about files on cache/upload folder. How can safelly delete the same files that I deleted from database? I supose that administrator user can have a "cleanup routine" or something like that for server maintenance, to preserve a good consistence and performance of SUGARCRM.

Thanks.

[sacramentojoe]

Hi,

I have deleted older notes and a lot of e-mail attachment that is not important for me, but I know that files still phisically on my hard disk. I'm not talking about the "deleted flag item" on database, I'´m talk about files on cache/upload folder. How can safelly delete the same files that I deleted from database? I supose that administrator user can have a "cleanup routine" or something like that for server maintenance, to preserve a good consistence and performance of SUGARCRM.

Thanks.

Well, the files are named {unique-id}filename. I guess since you don't know the unique ID anymore since you deleted it, you'll have a hard time knowing which files you can delete. I guess you could compare the ID's in the document tab and remove the ones that are non existing.

Good luck.

[matteo1]

I have been puzzled by this as well. There does not seem to be a quick routine for performing clean up.

Or am I missing something?

Matt Anderson

[mycrmspacegunnar]

Well, the files are named {unique-id}filename. I guess since you don't know the unique ID anymore since you deleted it, you'll have a hard time knowing which files you can delete. I guess you could compare the ID's in the document tab and remove the ones that are non existing.

Good luck.

The records are still in the database, they are only market as deleted.

Technically its not difficult to write a small routine that
fetches all ids of the deleted records with one database query
and then physically deleted all files.

If you just need a quick solution then I could propably write this.
I'll have a look at it and get back to you with a module for this.

Cheers
Gunnar

[matteo1]

Gunnar -

I wasn't concerned with the records marked deleted. You will see my SQL script below to handle that issue. The bigger problem is that Sugar does not delete the external files (in /cache/uploads) when you delete an item, like an e-mail or document. If anyone has any pointers on PHP or SQL code to handle that, I would really love it.

Here's my clean-up code, which of course only cleans up the SQL databases:

# Clean up deleted e-mails
DELETE FROM `emails` WHERE deleted=1;
DELETE FROM `emails_contacts` WHERE deleted=1;
DELETE FROM `emails_users` WHERE deleted=1;
OPTIMIZE TABLE `emails`, `emails_contacts`, `emails_users`;

# Clean up other deleted records
DELETE FROM `accounts` WHERE deleted=1;
DELETE FROM `accounts_contacts` WHERE deleted=1;
DELETE FROM `accounts_opportunities` WHERE deleted=1;
DELETE FROM `calls` WHERE deleted=1;
DELETE FROM `calls_contacts` WHERE deleted=1;
DELETE FROM `calls_users` WHERE deleted=1;
DELETE FROM `contacts` WHERE deleted=1;
DELETE FROM `notes` WHERE deleted=1;
OPTIMIZE TABLE `accounts` , `accounts_contacts` , `calls_contacts` , `calls_users` , `contacts`,
`notes`, `schedulers` , `schedulers_times` , `tracker` , `user_preferences` , `users`;

Thanks -

Matt

[rgurgel]

The records are still in the database, they are only market as deleted.

Technically its not difficult to write a small routine that
fetches all ids of the deleted records with one database query
and then physically deleted all files.

If you just need a quick solution then I could propably write this.
I'll have a look at it and get back to you with a module for this.

Cheers
Gunnar

Hi Gunnar,

I will happy if you can write a simple routine or module for this. It will help so much. It will help also, because I can understand where and how thinks are stored and I can complent this by making a inverse query (php routine to get list of files and query the database) and It will be very usefull, I gess.

[conciseusa]

Has anyone started working on a script to cleanup the files of deleted notes? I am considering working on one, but don't want to if it is already being worked on.

[malcolmh]

Hi,

As far as deleting the soft deleted records is concerned, there is already a function available to do this in Sugar, the Prune database function in Schedulers does this, although we found that it doesn't delete the deleted records (4.5.0f) in custom tables.

As far as deleting files in the cache directory, I am not aware of any routine to do this, although I think you would have to be careful what you delete, because say attached documents are stored here.

[conciseusa]

I tested the prune function and it leaves orphan files in the cache/upload/ dir. So I wrote a script that that inspects all the files in cache/upload/ dir and sees if the filename is of the form of a GUID. Then these filenames are searched for in the database. If they are referred to by a note record that has been deleted, the file is deleted. If I run this script right before the prune function runs, it will keep the orphan files from building up.

If anyone is interested, I will find a way to make it available to other Sugar users. While I am an experienced PHP programmer, this is the first Sugar script I have written. I could not find any docs that show you how to use the Sugar database abstraction, so the script only works with MySQL. I have it running on a production system so over time it will be tested and I will be able to fill out any needed functionality to keep my Sugar system from bloating.

Does anyone know why Sugar leaves these orphan files? This seems like a significant issue because over time a Sugar install could bloat to an unmanageable size, and once the parent records are pruned, cleaning up these files is a pain in the arse.

[matteo1]

Concise -

I would love to get a copy of your script. Any way you can post it?

Thanks -

Matt Anderson