Storing files in database - share your experience

greg

Hi there,

I have recently run into problems with storing documents in various formats in AwareIM MySQL database. It was first reported to me that some queries of records with attachments returned System Error, while other searchers retrieved all records just fine.

Another problem with stored data files manifested itself during test run of the full database backup and restore. The sql backup file did not restore. The restore process halted on some, but not all, records containing stored data files. I attributed this problem to some binary characters confusing MySQL database.

Please share your thoughts and experience with data file storage, have you run into similar problems? Do you prefer storing data in the database or you only store the file path?

greg

pbrad

Hi,

If you really want some fun, try migrating from one server to another server when your database is gigabytes in size due to documents being stored in the database such as images and pdfs.

What I do is to import the document, then export it to a file system and maintain the path information to the document only in the database. Then you can either re-import it at runtime or even just point directly to the file and open it up in the browser. I used to import the documents at runtime but because I had thousands of documents spread against several business spaces I was started to get a weird error if two documents were imported at the same time by different business spaces and they would transpose themselves.

Now I just point to them within a secure folder and it works flawlessly without the hassles that occur with needlessly large database sizes.

Cheers,
Pete

tford

Ditto what Pete said.

The reason I began to do it this way was that earlier versions of AwareIM had too many User steps to display docs. Storing the path, I was able to control the display process.

thom

Greg,

I have more experience on SQLServer than MySQL, but up until a couple of years ago I believe the greater DBA community was of like mind on storing documents or files within a database column. It was generally recommend that if you were dealing with finite number of files (i.e. like Employee Images) that is was ok to store these files in a data object - but if you were going to store an unlimited number of files of varying sizes and types that you should store a pointer (a file path and name) within the database to a file server. The reason for this was mostly because the files were stored in BLOB or IMAGE data types (which are quirky at best and cause problems like you are experiencing)

With the advent of SQLServer2005/2008 we saw the support of new data types (new to MS, but not other db types) that allowed more effective storage of files within a column. The new data types are VARBINARY(MAX) and/or FILESTREAM. There was also some really cool stuff done here to compression/decompress file stored in a column using these types that had pretty impressive performance results.

Now, in AwareIM, the files are stored in an IMAGE data type (on SQLServer, but I am guessing its the same in MySQL). If this is true than I would recommend storing the files on the file server and saving the relative path or url on the database. I am not sure if or how Aware could do this but I am interested enough to try it out and I will post my comments here...

I apologize for the long winded response - just my gentle nudge to Aware that I would like to see some of these other data types (i.e. time, varchar(max), varbinary(max), and xml) supported ....eventually.

thom

ACDC

There is obviously pros and cons as to which way you do it. I understand the issue of backing up and restoring the database and the complexities, but are there any disadvantages of storing the file outside the database. ?

It seems to me if you have everything encapsulated in the database its more portable than having to worry about a secondary file store or file system. So maybe the answer is to find a solution to the back up and restore problem when there is large file sizes

The one issue that always concerns me is what happens if the file uploaded contains a virus, how do you handle this ? In some way if its isolated in the database I feel more protected

It would be good to hear further comments on this subject

ACDC

this article seems to shed more light on the topic

http://books.google.co.za/books?id=zzlhSgGtVe4C&pg=PA474&lpg=PA474&dq=storing+files+in+a+database+or+a+file+system&source=bl&ots=h1FdcUsanu&sig=S8xgKBWLLNR9ANeeCJ4yDOZ4gmY&hl=en&ei=DmvHSq6jC4Sn8AbqgvnhCA&sa=X&oi=book_result&ct=result&resnum=7#v=onepage&q=storing%20files%20in%20a%20database%20or%20a%20file%20system&f=false

thom

ACDC - I agree with your first post, but it has been my experience that this is really bigger than just a backup/restore issue. The use of the BLOB/IMAGE data type is very problematic.....and over time you will see that some of your data that is stored in these columns is corrupted-all links will point you back to the inherent problems with data type rather than any specific code blocks that loads or retrieves the data.

Your second thread - referrences both the .NET framework as well as the use of the FILESTREAM data type - both of these are currently outside the AwareIM framework, although it does support encapsulation, but until these types are supported by Aware the best method is store them outside as Pete and Tom outlined.

If you have experience within Aware of doing it one way or the other I am keen on knowing your approach, benefits or pitfalls....

thom

rocketman

Hi all - just picked up on this thread because I've just started creating functionality to upload and store our committee minutes and flying manuals into our aware system.

So far Ive just used an attrib of the document type which I'm assuming puts the doc directly into the database, so if this is going to give me problems over time, I'd be interested to see some code snippets dealing with - how to allow a user to upload a doc and save it to disc without fist loading it into the database. Or is it a matter of uploading to database, exporting to disc, deleting the DB version and storing the path to the file into a plain e
text attrib

tford

Rocketman,

Or is it a matter of uploading to database, exporting to disc, deleting the DB version and storing the path to the file into a plain e
text attrib

Yes, that's how you do it. I use a Document attribute in LoggedInRegularUser to temporarily store the uploaded doc.

ACDC

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

I have run some tests on backing up and restoring blobs in MySQL and the only problem that surfaces is when the files become larger than 1mb.

However if you set the "max_allowed_packet = 20M" (by default it is set to 1M) in the my.ini file of MySql server, this problem goes away and Restore and Backup can be run without a problem

tford

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

Why do you say this?

ACDC

Well, sometimes you may not have full control over the the file system. If you are committed to security levels e.g.such patient records, imaging etc etc, I think the DB and application rules let you control the security far better .

rgates@ttcdas.com

Tom,

Do you have a sample BSV that I could look at? I'm currently creating a CRM solution. I am currently storing the documents in the database, but from the sounds of it -- I should be storing them at the file level.

I put a rule at the BO level that does an export, but it exports all the attributes and not just the document. Not sure how to just export the document so that I can just embed a link onto the form for viewing the object.

tford

Sample BSV is at http://www.awareim.com/forum/viewtopic.php?p=13269#13269

kklosson

This is an interesting thread. I have a BO that does nothing but store documents related to another BO. It's a key feature. I looked at the sample BSV (though not the code). It looked as though this was constructed to migrate the documents from the BLOB to the file system. My app is still in development and if it's a bad course to store documents in the Document attribute, then I should change my course now. However, I can't really imagine how to implement this in Aware IM. As stated, I have a Case File thing and I need to allow the attachment of many files. This would probably require a file stricture having a file folder for each case file or something.

Is this really a course of action I should consider? And if so, does anyone have a BSV that demonstrates this?

Thanks

RLJB

1.5 years later ... does anyone care to update on their experiences and thoughts?

FYI - we just use Doc attributes and thus store in the DB. No problems so far BUT our DB is just at 1.1 GB - not too big. Are there dangers ahead?

nlarson

at Satuit we use bLOBs to store docs and have a few DB's in the 50+gb range with total client DB's around the 1TB size. ₈₅% is documents.

Performance is fine. Migration is a pain but not really a big a deal. Long-term (for commercial software) I think cost is one critical reason to move docs out of DB blobs. We run the DB storage on Fiber Channel SANs which cost way more than disk space on a NAS. So we're spending big money for storage which is major overkill for file serving, plus we have a warm DR so we have to buy the hardware twice!

In our next major maintenance cycle we will move to use MS SQL's Filestream feature. Nice feature which is unfortunately MS SQL only, but renders this question basically moot and would be transparent to Aware. Hopefully MYSQL will implement it soon.

ACDC

Thanks for this information, storing files outside the db has its issues and I think filestream MS SQL is a good solution, but the question is does AwareIM support this

nlarson

Good question, I had just kind of assumed Aware used blobs for documents. If so FileStream should be A-OK as it is managed as part of the database file system fabric.

Kahuna

Be very useful to know from AwareIM if filestream is supported in MSSQL. I'm about to develop the document handling process for our test build in the system?

Currently in all our developments we use the file system on a secure server location to store all documents and images with a link back to the relevant record.

This causes issues in our current development environment because we need two code bases - one for Web Start Client and another for Web Client. Complications we could do without - so saving with automated Db support would be the ideal - especially considering the compression built in.

Ian