FileField Meta records remain for temporary files removed by system cron.

sorry, minor fix in title.

Here's one possible solution: update the weight of the FileField Meta module so its loaded before the system module, its cron would also be executed before the system cron, and then implement something like this:

/**
 * Implementation of hook_cron().
 *
 * Remove meta data about temporary files that are older
 * than DRUPAL_MAXIMUM_TEMP_FILE_AGE.
 *
 * This cron task should be executed before system_cron().
 */
function filefield_meta_cron() {
  $result = db_query('SELECT * FROM {files} WHERE status = %d and timestamp < %d', FILE_STATUS_TEMPORARY, time() - DRUPAL_MAXIMUM_TEMP_FILE_AGE);
  while ($file = db_fetch_object($result)) {
    db_query('DELETE FROM {filefield_meta} WHERE fid = %d', $file->fid);
  }
}

hmm... in fact, updating the weight column of FileField Meta in system table would not really be necessary because filefield_meta is already loaded before system module.

So it could be simply fixed implementing the cron task above. A hook_upadate_N could cleanup the table, if needs be (this is alpha code, so...).

Want a patch with just the cron?

Well, here's a patch. It implements hook_cron() and it also includes a hook_update_N() to update the module weight to -1, so the cron here is really executed before system_cron().

I was partically wrong in my previous comment, modules are loaded in weight+filename order, but filename includes the path, so modules* loads before than sites*, hence the update to the module weight is necessary to ensure correct order of cron tasks.

I also thought about that possible approach, but I dsicarded it because it would require a more complex query (left join or full scan of filefield meta table find matches with file table)... much heavier in terms of performance.

We will be probably using filefield/imagefield for our project, and we can easily manage more than one million images, right from the start, since this is a migration of non-drupal site to D6. So we would prefer an as lighter as possible cron task hammering our DB server just for this cleanup processing.

Sorry, I'm not sure what you mean by "duplicating core" :(

Altering the weight of the module is something used by CCK Fieldgroup module. I don't see where's the problem on this.

Ideally, it is core's file API that would have to be patched to affer hooks in file_delete(), for example. But I discarded that since it doesn't look a nice idea to expect for a D6 patch at this point.

Please, consider the performance impact of cleaning the filefield mate table. Do you think of any other way to avoid orphans here? ...if solved by a cron task, we may end up with a hammer that hits the DB every 5 minutes if not optimized.

Ok, thank you. I'll try to roll and test a patch later today. :-D

The patch with the @todo note and also an additional query in hook_update_N() to deal with orphans that may exist in the site.

Hope that helps

dopry, the problem with a LEFT JOIN is that it will require a full scan that may take a lot of seconds when you have thousands or a million files, and this is going to hammer the DB at cron intervals.

system_cron() runs the same query, then in the loop it runs file_delete() for each expired temporary file. We need to act before that, or hook into system_cron() / file_delete(). I cannot see how without patching core. :(

Please, try to run such an SQL statement when thousands of records are involved. I did (*) and it took forever.

@"It's not really open to discussion": I'm sorry, but I won't push a patch that I will have to patch again on my system. What's the worst thing that can happen if system_cron() goes wrong? Is it possible to find an alternative method to clean up with LEFT JOIN that doesn't mean something hammering the DB at cron intervals?

(*) aside from testing that with Drupal, please see this bulletin board. We have +16 million posts, +1 million topics, etc. Since it's based on phpBB, it is not transactional, therefore it generates orphans here and there. I have a daily cron task that cleans the database. It certainly uses LEFT JOINs, but the process takes 2 hours. It's doable because it's run at midnight, when traffic is low. It wouldn't have been possible if it was spread across 5 minute interval crons. We also run this site (around 2 million pageviews a day), which is being migrated to Drupal. We have more than 1 million images that I plan to port to filefield/imagefield. I cannot run a cron to cleanup orphans in the files table with LEFT JOIN. I hope you can understand my concern.

hmm... have you thought about extending the files table instead of using a separate table for meta data?

I know many sites that use custom cron management tools just to run particular cron jobs at more controlled intervals

Is there such a thing to fine control cron tasks in Drupal? Could you please provide a link or something? Please

Oh, I see. Thanks for the tip.