[Ask] Applying 1 year expiration on all guest uploaded contents

[Andy]

I’ve just configured my Chevereto setup to expire guest-uploaded content after 1 year automatically, but I’m wondering how I can retroactively apply this 1-year expiration to all existing guest uploads. Any tips would be greatly appreciated!

 

[Andy]

@Rodolfo: Can we run a manual SQL query for this?

 

[Andy]

bump

 

[Rodolfo]

UPDATE chv_images
SET image_expiration_date_gmt = NOW() - INTERVAL 1 YEAR
WHERE image_user_id IS NULL;

 

[Andy]

+----------+---------------------------+
49801 rows in set (0.347 sec)

MariaDB [chevereto]> UPDATE chv_images
    -> SET image_expiration_date_gmt = NOW() - INTERVAL 1 YEAR
    -> WHERE image_user_id IS NULL;
Query OK, 49801 rows affected (2.073 sec)
Rows matched: 49801  Changed: 49801  Warnings: 0

MariaDB [chevereto]>

Just like that, and it's all done. Thanks @Rodolfo!

 

[Andy]

It’s been 24 hours since I actioned the mysql script above, and I can confirm that all unowned files older than a year have been removed. I’ve also run the stats rebuild from the dashboard homepage, but the disk usage hasn’t gone down. Is there anything I might’ve missed?

 

[Andy]

After checking the filesystem, it looks like the SQL only removed entries from the database, but not the actual files.
It’s even worse now — the references are gone from Chevereto, but the files are still sitting in the filesystem like remnants.
Here’s one example I recorded — this image was deleted from Chevereto, but even after 48 hours and multiple cron runs, it’s still there:

site@cana:/home/site/public_html/i$ ls *bgB9* -al
-rw-r--r-- 1 site site 37450 Nov 20  2015 bgB9C.jpg
-rw-r--r-- 1 site site  6772 Nov 20  2015 bgB9C.th.jpg

How can we safely clear these orphaned files?

 

[Rodolfo]

When Chevereto delete files via queues (using storage API) it will keep a track of the row which failed to delete, therefore such issues never occur as the system has a built-in retry mechanism. When Chevereto delete files in the local system, without the local storage API wrapper, like in this case, it will rely on whatever the filesystem returns which seems to be it was a false positive delete operation.

You can review the chv_deletions table for the files recently removed as we are smart enough to keep a delete log if things like this ever happens.

By the way, if using local storage we recommend always configuring it via Local Storage API.

 

[Andy]

Yes, it's been always using Local API.

 

[Andy]

I think I’ve figured out what’s going on. Some of the guest images were uploaded using a different API back then, not the Local API. That might be why they’re not being picked up by the auto-delete rule, even after running the SQL command to set image_expiration_date_gmt.

How can we make Chevereto treat those existing images as if they were uploaded using the Local API, so the system will handle them correctly during cleanup? Once that’s done, I’d like to retry the SQL again to set the expiration and let the scheduler delete them.

Thanks!