Storing Files in a MySQL Database

[milner]

Hi,

I want to be able to store files on my database server. I need to be able to control access to the files (Store, retrieve) in the same way that I control access to records in the database.

I'd like to actually store the data within database records. How big a file can be stored in a database field?? The files may be of any length, some being quite large (>>50 MB). Is this possible?

Thanks,

Milner

 

[Guest_imported]

I know for a fact that it is technically possible to store up to 4GB of info in a field by using BLOB type. However, the realistic number due to web server and packet constraints should be about 100MB. So yes, you can store files as a BLOB type.

However, I have yet to figure out how to use INSERT OR UPDATE statements to store files. I have not figured out a way to retrieve such files either. Hope at least the info will offer a ray of hope.

 

[Hokey]

I found this in the MySQL manual:

LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the file privilege. The file must be readable by all and be smaller than max_allowed_packet. If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL:

Example:
mysql> UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture&quot
WHERE id=1;

I think to get the files back out, you need to use the "into outfile" on our SELECT statement.

If you are using this to store web data the it is really not worth doing this for performance reasons. The best way to actually store files is in your file system and then only store the path to these files in the database. The OS's filesystem and Apache is much much quicker at caching and retrieving files than the MySQL.

 

[milner]

I'm actually making an application with Visual C++ and I want to use MySQL as the back end database.

The application is for an arbitrary amount of users. I want to allow them to store a file in the database from their own computer that will be protected by MY programs user login. Then, anyone with the appropriate permissions can download the file to their own computer for viewing or editing.

I'm thinking of, if the file is bigger than about 100 MB, breaking it up into smaller chunks. My Program would then be able to reconnect them when the file is retrieved.

 

[Hokey]

You could probably have your program UUencode the files to and split the UUencoded file into segments ... simmular to how news groups store uploaded binary files. When the user wants their file back, your program would then have to combine and decode.

Might be more problem than it is worth, but just an idea.

 

[Guest_imported]

One addition regarding retrieving files from MySQL BLOB fields. The file you retrieved will be corrupted if you use the SELECT INTO OUTFILE option. I know because I just tried it. According to the MySQL manual, you should use the following syntax instead:

SELECT blob_name INTO DUMPFILE "file_name" FROM table_name;

That will work.

 

[LocalHero]

Why store files in the database. Do like this and you wont wast so much cpu flushing blobs in and out from mysql.

Create a table with cols like this

create table files (ID int primary key auto_increment not null, filename text)

Now you create a dir for example /mysql/files/

And when you insert a file into the db you just insert a row like this.
insert into files values (null, "autoexec.bat&quot
and then you take the last inserted id and just do a
move /tmp/autoexec.bat /mysql/files

And tada! you got a real fast mysql file system

 

[doddo]

I want to use MySQL to store files. I want people to select the file they want to put into the DB or view from the DB, via the browser. So, they would see a list of files via the browser and click on the one they want to open.
Pressumably, (once you pulled a file from a database) to open the file( eg word document) if you were running perl or something you would use the system command with the filename as an arguement.
We have the command for dumping files out of the DB, I don't suppose there is a command for opening the file is there?

 

[PghMikeH]

I agree with LocalHero. I would prefer to use the filesystem to store the files. Let MySQL store a pointer to that file.
Mike Hillwig
mike@hypermike.com

http://www.hypermike.com

I've done more R5 deployments than I care to think about.

 

[milner]

The reason I want to store the files in the database is for access control. My application uses MySQL as a backend. I'd like to keep the files in the database so I can control who uses them.

The users are not on the server machine, they access it from windows platforms all over the building through my front end application.

Can anyone suggest another way to restrict access to these files?

It must be controlled from within my application.

Thanks.

 

[LocalHero]

Hmm, Ok. But you can still exstend the database with for example.
create table files (ID int primary key auto_increment not null, filename text,categori int not null)

Now you can fake a directory like this.
select * from files where categori=3; for example. And then you have another talbe like this

create table catinfo (ID int primary key auto_increment not null,categori text)

and then you make a query like this.
select categori from catinfo where ID=3 and then you get the name.

So if you had this info in the database
files
1,autoexec.bat,1
2,configu.sys,1
3,hhdk.tmp,2
4,win.com,3
5,win.ini,3

and then catinfo
1,c:2,c:\temp
3,c:\windows
And you make the query above you will get the info
win.com and win.ini in the dir c:\windows. It is you who specify the permissions etc. Nobody will know that all files are in one huge dir with strange names.

Uhh, hope you understand any of this above . Cause when i looked at it after writing it i dubt that you get any info from it

 

[Hokey]

Storing the files in the file system is faster, but storing them in the database does have good points.

I have set up an Apache/MySQL/PHP web server for my PR offices to store images (tiff and jpeg formats). Originally I used the file pointer method for this project and it worked pretty well. When PR put up the funds to purchase a dedicated server for the project I decided to just try storing the files in the database to see how well it would work ... and I loved it. My PR office has about 40,000 files and growing several 1000 per month. Storing the files in the database completely eliminates file management and naming. It does takes an extra second to bring up the picture, but with a dedicated server I am not too concerned about what it is doing to the CPU load.

Now if this were on my main web server ... I would go back to using a file pointer. But there are only 6 people who access this image server on a very sporadic basis.

 

[milner]

My project is also pretty small.

Currently there are only aruond 3 people who use these files. The problem is, they have the files on their own computers. I want to get them in the database so they'll be in a centralized location (easier to backup and protect versioning) and so I can control read write permissions (by controling who can download and who can overwrite). The problem is, these files can be arbitrarily large.

So, what I was thinking is to have one database with all the filenames in it. (ID, FILENAME, PIECES) Then a second database (ID, FILEID, PIECENUM, BLOB) would contain all the chunks (maybe 10MB each). When a file is requested, my client program would get all the pieces and put them together into one logical file on the user's harddrive.

Any thoughts?

 

[LocalHero]

Hmm, i can only say that i have done a image library using php->mysql and on my machine that is rather slow (P75) i could only get abou 6kb/s when i had it in the database uuencoded.

The best thing you could do is to test and see. First you try to put it in the db. If it is a to heavy task you should consider using the filesystem

/Alexander