Large .PDF Files Not Uploading To MySQL Database a

Question

I am developing a PHP script for uploading .PDF documents as medium BLOBs into a MySQL database via PHP. The script also allows users to search for files and open/download them but I do not think that part of the script is relevant to my issue. The script works fine with files less than 2 MB but as soon as I try and upload a file that is more than 2 MB nothing is going into my content(Medium BLOB) column and there is no value for the mime type. I have already tried increasing the max_packet_size for the MySQL server to 4 MB from its default value of 1 MB. I have also updated php.ini to the correct values, I think. I set upload_max_size to 4MB, post_max_size to 4 MB, and memory_limit to 16 MB. I haven't really experimented with the max_input_time though because it is defaulted to 60 seconds which seems like plenty considering this script is for an internal application on an intranet.

I have also tried to catch errors from the PDO object in my script with errorInfo() and errorCode() and both through 0s (no error).

Here is the upload section of my script:

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;


    }
}

And Here is the front end of the application showing that a smaller file works and a larger file does not. The file sample.pdf is less than 1 MB and the file test.pdf is 2.5 MB.

http://www.imagechicken.com/viewpic.php?p=1255628359017775300&x=png

I would have used img tags but alas I am a new user and not allowed.

The next thing I am going to look for is a setting in Apache that limits file sizes. I am new to LAMP so any tips would be great. Thanks

Answer 1

You might be running into a mySQL limit.

Run the following in mySQL:

show variables like 'max_allowed_packet'  

That will show you your current setting. The default is 1047552 bytes.

This can be changed with your my.cnf file. Simply add

set-variable = max_allowed_packet=2M

or what ever size is required and restart mySQL.

EDIT:
What about

<input type="hidden" name="MAX_FILE_SIZE" value="xxx" />

Might that be part of the problem?

Answer 2

I actually fixed the issue. I changed all of the values that were recommended here in php.ini and my.cnf but I also needed to change a setting for PDO.

I changed: PDO::MYSQL_ATTR_MAX_BUFFER_SIZE (integer) Maximum buffer size. Defaults to 1 MiB.

This has to be set when the PDO object is created to work though. All is good now.

Answer 3

If you're trying to do this on shared hosting (where you can't make changes to MySQL conf files, etc), Google 'mysqli prepared statements'.

This allows you to upload large queries in predefined byte chunks, circumventing any buffer restrictions.

Answer 4

YOu might be running up against the limitations of the HTTP request (which I think is 4MB), or your HTTP request might be timming out. I have run into this issue before. You might want to look @ your php.ini file for maximum upload size.

Disclaimer: I am not a PHP developer :D

Answer 5

So to recap all the things I believe I had to set in order to allow file uploads >1MB on something I was working on:

php.ini

• Changed post_max_size (I wanted 15MB file uploads, so I set it to 20M just in case)
• Changed upload_max_filesize to 15M
• Left max_execution_time at 60
• Left max_input_time at 60
• Left default_socket_timeout at 60

my.ini

• Changed max_allowed_packet to 15M

PHP Code

• changed my PDO call to:

  new PDO("mysql:host=$host;dbname=$db_name", $db_username, $db_password, array (PDO::MYSQL_ATTR_MAX_BUFFER_SIZE=>15*1024*1024))