Consistent DB references to uploaded media files in filesystem

[pkirkaas]

Hi;

LAMP infrastructure. We have a website that allows users to upload media & image files. We keep the files stored as files in the file system, and keep references to them in the MySQL db.

Inconsistencies happen, however. Maybe a code "upgrade" that doesn't work as it should, or a system crash between the file upload or delete & the db registration... point is, sometimes there are files in the upload directory that are not represented in the DB, and v/v; files referred to in the DB that don't exist in the filesystem.

My question is, are there any good practices, standard approaches to ensuring consistency between the file system & DB? Obviously, we can run a periodic cron job that deletes files in the upload directory that don't have references in the DB, and delete records in the DB that don't have a corresponding existing file in the filesystem. And maybe that's the way to go, but I wondered if there is another more clever approach that hasn't occurred to me.

Any suggestions?

Thanks,

Paul

[coopster]

That is what I do, Paul. I don't know any other way around it. The reference in the database has to be compared to the filesystem one way or another.

[vincevincevince]

For non-large files, it is possible to store them in BLOB format - and with some database types this is actually very efficient (thinking of postgresql in particular here). That means that deleting a row == deleting the file; and backing up the database == backing up the files as well.