Page 1 of 1

Storing PDF in MySQL

Posted: Wed Jun 08, 2011 7:39 am
by alex.barylski
I used MySQLYog to upload the 3M PDF file into a record. Reretrieving it from MySQLYog works fine but when I try to retreive that file from PHP in my application it comes out corrupt:

Code: Select all

// ... controller:action starts
$response->setHeader('Content-Description', 'File Transfer');
$response->setHeader('Content-Disposition', 'attachment; filename=diagram.pdf');

$response->setHeader('Content-type', 'application/pdf');
$response->setHeader('Cache-Control', '');
$response->setHeader('Pragma', '');

$response->setContent($buff);
return true;
// ... controller:action finished
When I debug the damn thing and send the output to error_log() it appears only a portion of the file is being returned, no where near the 3M.

The field type is BLOG. I'm using a simple SELECT data_buffer from table type query - is there special handing required for BLOB's? The PDF magic bytes appear to be present in the first 5 of so bytes of the output so i'm assuming the corrupt is being caused by missing data - not why or how to fix this anyone have any similar experience?

Cheers,
Alex

Re: Storing PDF in MySQL

Posted: Wed Jun 08, 2011 8:02 am
by alex.barylski
I echo'ed the strlen() of the $output variable and it's reporting 65535 - so it seems the SELECT is not returning all the data or I am missing something in the MySQL API. Do you need to build up the output with concatenation or something first?

Re: Storing PDF in MySQL

Posted: Thu Jun 09, 2011 7:12 am
by Peter Kelly
Are you sure all the data is actually being stored? As I have never done anything like this but I thought MySQL had limits on the amount of data in a certain field?

Re: Storing PDF in MySQL

Posted: Thu Jun 09, 2011 8:24 am
by eskio
I have never done this before, but I handle pdf file in another way. Instead of storing pdf file into database, I upload the file on the server and save only the name of the file (and/or path).

Re: Storing PDF in MySQL

Posted: Thu Jun 09, 2011 11:08 am
by Jade
That's a better solution anyways... think of how many resources you'll be wasting by trying to store/retrieve 3M files all the time when the filesystem can do it better and faster!

Re: Storing PDF in MySQL

Posted: Fri Jun 10, 2011 3:57 pm
by pickle
Your problem is you're storing files that are too large for the field. As you've discovered, the max size for the BLOB type is 65K. Changing the column type to MEDIUMBLOB should fix it.

That said though, ~Jade has a point - use the filesystem for storing files. Its faster and much more efficient.