[SOLVED] BLOB download being truncated at 1MB

[jpdbaugh]

I just recently asked and solved a question pertaining to uploading .PDF files that are greater than 2 MB into a MySQL database as BLOBS. I had to change some settings in my php.ini file and MySQLs maximum packet setting. However, fixing this issue has led me to discover a new issue with my script.

 

Now since I can upload files to my BLOB database I attempted to download the file for testing purposes. Much to my dismay when I went to open the .PDF file I received the following error: Failed to load document (error 3) 'file:///tmp/test-13.pdf'. Upon further investigation I found out that the file being downloaded, test.pdf, was only 1 MB, a little less than half of its supposed size in the database of a little more than 2 MB. This is obviously the reason for the error.

 

The following piece of code is the part of my script I am using for downloading files from the database. It is is at the very top of of script and works Flawlessly for files that are less than 1 MB.

 

foreach($_REQUEST as $key => $value)
{
if ($value == 'Open')
   {
    header();
        session_start();
        $dbh = new PDO('mysql:host='.$_SESSION['OpsDBServer'].'.ops.tns.its.psu.edu;  
           dbname='.$_SESSION['OpsDB'], $_SESSION['yoM'], $_SESSION['aMa']);
        $id = $key;
        $sqlDownload = "SELECT name, type, content, size  FROM upload WHERE 
    id='".$id."'";
        $result = $dbh->query($sqlDownload);

    $download = $result->fetchAll();
        $type = $download[0]['type'];
        $size = $download[0]['size'];
        $name = $download[0]['name'];
        $content = $download[0]['content'];

    header("Content-type: $type");
        header("Content-Disposition: inline; filename=$name");
        header("Content-length: $size");
        header("Cache-Control: maxage=1");
        header("Pragma: public");

        echo $content;

        exit;
   }
}

 

I am thinking that maybe I have some header statements wrong? I am very confused about what to do. I have searched through php.ini and I have found no settings that I think need to changed and my maximum packet setting for MySQL is 4 MB so a 2 MB should download.

 

Thanks for any help.

[PFMaBiSmAd]

Investigate at what point the size is correct and at what point it is not.

 

Is the whole file actually present in the database? What is your column definition? Can it hold a file of the size you are using? Does echoing strlen($content) given the correct value? (comment out the headers to see the result of the echo.)

 

This empty header statement is not doing anything and should be removed - header();

[jpdbaugh]

here is the ddl I used to create the table:

 

CREATE TABLE upload (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(50),

documentType VARCHAR(20),

type VARCHAR(30),

content MEDIUMBLOB,

size INT,

networkKey CHAR(20),

modifiedBy CHAR(20),

modifiedDate DATETIME,

active TINYINT,

PRIMARY KEY(id)

)

 

$type, $size, and $name are giving the values, application/pdf, 2205887, and test.pdf respectively.  These values are expected and correct, however strlen($content)) is giving a value of 1048576, which shows that the upload is capped to 1M right?

 

[jpdbaugh]

Also, I have already changed the MySQL setting max_allowed_packet to 4M, and max_upload_size in php.ini to 4M as well as max_post_size in php.ini to 8M and I am still having the same issues.  Also, this script is part of internal application to so timeouts are not really an issue. 

[jpdbaugh]

So it is a couple of days later and I am still having the the same issue.  I have discovered some new things about the problem, however.

 

The problem is not with downloading.  The problem is still with uploading.  The file I am uploading is being truncated when it is inserted into the database.  I have checked the size of the file I am uploading within in the $_FILE array using strlen().  This leads me to believe that the php.ini file is set up correctly.  If I query the database after the file is supposedly uploaded and perform the same strlen() test I get the 1M value back.  This is why I think the problem is either with a setting in MySQL or the PDO extension I am using with MySQL  I do not think the problem is with MySQL but with PDO.  My code for the upload part of my script is below.  Is there something I am doing wrong with PDO and my insert command.  I have read that PDO can be used to send the BLOB in chunks that can be less than 1M  Could this be a potential work around?

 

Here is the code:

if ($key == 'upload')
{
        set_time_limit(0);
        $_SESSION['upload'] = $value;

        if ($_FILES['userfile']['name'])
        {
                $fileName = $_FILES['userfile']['name'];
                $tmpName  = $_FILES['userfile']['tmp_name'];
                $fileSize = $_FILES['userfile']['size'];
                $fileType = $_FILES['userfile']['type'];
                $docType = $_POST['docType'];
                $netKey = $_POST['netKey'];
                $fp       = fopen($tmpName, 'r');
                $content  = fread($fp, filesize($tmpName));
                $content  = addslashes($content);
        fclose($fp);

        $_SESSION['filetype'] = $fileType;

                if(!get_magic_quotes_gpc())
                {
                $fileName = addslashes($fileName);
                }
                $sqlCheck = "SELECT id, name, documentType, networkKey FROM upload WHERE active='1'";
                foreach ($dbh->query($sqlCheck) as $row)
                {
                        if (($row['name'] == $fileName) && ($row[networkKey] == $netKey) && ($row['documentType'] == $docType)) 
                        {
                                $_SESSION['updateRow'] = $row['id'];
                        }

                }

                if ($_SESSION['updateRow'])
                        {
                                $deactivateDuplicate = "UPDATE upload SET active = 0, modifiedBy = '".strtoupper($_SERVER['REMOTE_USER'])."', modifiedDate = Now() WHERE id = '".$_SESSION['updateRow']."' AND active ='1'";
                                $dbh->query($deactivateDuplicate);
                                $_SESSION['sql'] = "SELECT id, name, documentType, type, size, networkKey, modifiedBy, modifiedDate, active FROM upload WHERE active = '1'";
                                $_SESSION['uploadSearch'] =     1;
                                $_SESSION['updateRow'] = 0;
                        }
                        $values = "'".$fileName."','".$docType."','".$fileType."','".$content."','".$fileSize."','".$netKey."','".strtoupper($_SERVER['REMOTE_USER'])."', Now(), 1";
                        $sqlUpload = "INSERT INTO upload (name, documentType, type, content, size, networkKey, modifiedBy, modifiedDate, active) VALUES (".$values.")";
                        $dbh->query($sqlUpload);

                        $_SESSION['sql'] = "SELECT id, name, documentType, type, size, networkKey, modifiedBy, modifiedDate, active FROM upload WHERE active = '1'";
                        $_SESSION['uploadSearch'] =     1;


        }
}

[corbin]

Hmmmm, you're representing the blob value as a string which means that it could be getting messed up with that.  (Although really I think it's something else since that wouldn't explain why it would cut off at exactly 1MB.)

 

It might be worth a try to try using the bind method of PDO to make a prepared statement.

 

Something like:

 

$q = $db->prepare("INSERT INTO someTable VALUES(?)");

$q->bind($content, PDO::PARAM_LOB);

 

 

Other than that, max_allowed_packet has been set to something above 1MB, right?  (To make sure the change is registering, you could do "SHOW VARIABLES WHERE Variable_name = 'max_allowed_packet'".)

[PFMaBiSmAd]

PDO::MYSQL_ATTR_MAX_BUFFER_SIZE (integer)

Maximum buffer size. Defaults to 1 MiB.

 

You do realize that databases are intended to store data, not files, and that file systems were designed to efficiently store files.

 

[jpdbaugh]

Yes, trust me this isn't my idea.  It is a script I am writing for my boss of my internship.  I would much rather use a file system.

 

Also, my max_allowed_packets is set at 4M.  I checked by logging into mysql via the terminal program and using show variables like.

[jpdbaugh]

Thanks a lot PFMaBiSmAd, that worked great for me.  Problem solved.

 

[corbin]

Oh wow....  Never knew that PDO cared how big the MySQL buffer size was lol.

[dionrowney]

Can someone be more specific about how this BUFFER_SIZE variable was changed?  Maybe a snipped of the php code of what was done?  or the my.cnf file?

 

Please assist.  I am not a noob to this, just to PDO.

 

thanks

[Maq]

This thread is about 1 1/2 years old.  You may have better luck starting a fresh thread, and if you want, a reference link to this one.

[gizmola]

$pdo = new PDO ("connection_settings", "user", "pass", array
(PDO::MYSQL_ATTR_MAX_BUFFER_SIZE=>1024*1024*10));

 

Would up the limit to 5 megabyte.

 

In the future, please make a new thread, and just add a link to the old one.

[dionrowney]

Thanks for the help!  That appeared to work and actually appears to have gave me a 10 MB limit.

 

I appreciate the help as I would never have tried it that way!.... many thanks and a great way to end the day.

 

Dion