[rt-users] Option to store attachments on the filesystem

[Geoff Mayes]

> I may be mistaken, but I thought that all ticket content is currently stored
> as an attachment in the DB and not just those available in the Create Ticket or
> Display Ticket screens.

You're right: all uploaded attachments (images, logs, PDFs, etc) as well as all textual ticket updates (comments, correspondence, etc) are stored in the Attachments table.  I've only been working on RT for a couple weeks, but I've worked on Bugzilla for a few years, so it has been very interesting to compare the two.  I am surprised that uploaded attachments and ticket comments/correspondence are stored in the same table!  That feels like overloading and/or de-normalization to me.  So, yes, any implementation of the "store attachments on the filesystem" feature would have to change the current schema so that all textual ticket updates remain in the database, but all file uploads are stored on the filesystem.  My brief reading of the patch submitted during a previous post about this issue (http://www.gossamer-threads.com/lists/rt/users/67406) shows that the current schema can be kept, but a clean, non-hackish implementation should probably change the schema.

I wonder if we've been misunderstanding each other in other places because of my lack of understanding on this matter until now.  If so, I apologize.

> I do think that more people count on the ticket system as a resource and expect
> it to be continuously available, the larger the consequences of adding additional
> "moving parts" to the system.
> It is very easy to trivialize the issues
> that need to manage a filestore, whether in a SQL DB or filesystem DB(or data
> store). When reliability and availability are important, many measures need to
> be taken to ensure access to all of the metadata+data and handle business
> continuity and disaster recovery.

As a general statement, I would agree with everything you wrote.  I will say, though, that for our RT instance I'd take an NFS mount of attachments over an additional 14GB table in the database any day of the week.  Additionally, our rate of RT usage is growing every month, so that 14GB Attachments table could easily become 40GB in two years.  I like future-proofing.

> As an example, using a different DB product you can replicate the backend to
> a new instance and keep it in sync until the upgrade. Then you have effectively
> zero time to copy the DB because the work was done outside the critical path
> for the upgrade.

That's a good idea.  Unfortunately, we couldn't have done this for our last upgrade because we moved from MySQL 4.1 to 5.1 (http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html).

I'm pretty sure we are going to make this change to our instance.  (I'm pushing for it.)  I was just hoping to get this feature into the main RT repo to spread the love, de-duplicate the identical work done by others, and make future upgrades easier for us.