Uploading Larger Files into database

[melissa820]

I have written a file upload script that allows the user to upload files using a web form, and the file is saved into this database:

table myfiles:
id int(11) PRI auto_increment
file_title varchar(255)
file_type varchar(255)
file longblob
submittedby varchar(20)

Here is my script:

PHP Code:

//transfer file to temporary directory
$filename="/tmp/".$_FILES['file_browse']['name'];
$file_type = $_FILES['file_browse']['type'];
move_uploaded_file($_FILES['file_browse']['tmp_name'], $filename);    
$file_data = fread(fopen($filename, "r"), filesize($filename));

//upload file into database
$query = "INSERT INTO myfiles (file_title, file_type, file, submittedby) "
        ."VALUES ('".$_FILES['file_browse']['name']."', '".$file_type."', '".$file_data."', '".$session[user]."')";
$result = mysql_query($query); 


I'm using mysql_connect("localhost", $username, $password) to connect.

I'm getting this error: 1153: Got a packet bigger than 'max_allowed_packet', but only with files over 1MB in size. Is there a way to change this max_allowed packet to allow for larger files? If so how?

Thanks in advance,
Melissa

[Tangerine Dream]

Originally Posted by melissa820

I'm getting this error: 1153: Got a packet bigger than 'max_allowed_packet', but only with files over 1MB in size. Is there a way to change this max_allowed packet to allow for larger files? If so how?

Hi, you can either try to set max_allowed_packet parameter using SET statement

/*
You must increase this value if you are using big BLOB columns or long strings.
It should be as big as the biggest BLOB you want to use. The protocol limit
for max_allowed_packet is 16MB before MySQL 4.0 and 1GB thereafter.
*/
-- set to 16 MB
SET @@max_allowed_packed=16777216;
-- or
SET @@session.max_allowed_packed=16777216;

or modify default client connection parameters (if your hosting provider allows it through MySQL Admin panel or if you can modify my.ini/my.cnf settings), or can also use mysqli_stmt_send_long_data function (PHP5 only!):

Allows to send parameter data to the server in pieces (or chunks), e.g. if the size of a blob exceeds the size of max_allowed_packet. This function can be called multiple times to send the parts of a character or binary data value for a column, which must be one of the TEXT or BLOB datatypes.

[melissa820]

Originally Posted by Tangerine Dream

/*
You must increase this value if you are using big BLOB columns or long strings.
It should be as big as the biggest BLOB you want to use. The protocol limit
for max_allowed_packet is 16MB before MySQL 4.0 and 1GB thereafter.
*/
-- set to 16 MB
SET @@max_allowed_packed=16777216;
-- or
SET @@session.max_allowed_packed=16777216;

Thanks.
But how exactly do I do this? Can I send it through mysql_query like below?

mysql_query("SET @@max_allowed_packed=16777216", $link)

I don't have permission to modify any .ini files. I'm using MySQL version 4.0.20. PHP4 I think.

[Tangerine Dream]

But how exactly do I do this? Can I send it through mysql_query like below?

mysql_query("SET @@max_allowed_packed=16777216", $link)

Yes, mysql_query() can be used to issue MySQL statements like SET, SHOW, DESCRIBE, COMMIT, ROLLBACK, etc. no only DML or DDL statements

[melissa820]

Thanks. Do I have to set this every time I open a connection? Or do I just set it once and it will stay like that forever?

Never mind, I read about the global vs. session. I don't have access to the global variable. So I figure I can just sent the SET command right before I try to do the INSERT.

[melissa820]

This doesn't seem to be working. Or rather, it doesn't solve my problem. I set the max_allowed_packet to 16777216, and verified that it worked by doing "SELECT @@max_allowed_packet". But I still get that same error message when trying to insert the file: Got a packet bigger than max_allowed_packet.

Now, the max_allowed_packet size is = 16777216.
The size of the file I'm uploading is = 1092946.

So how can it have gotten a packet that is too big??

Here's my updated code:

PHP Code:

//transfer file to temporary directory
$filename="/tmp/".$_FILES['file_browse']['name'];
$file_type = $_FILES['file_browse']['type'];
move_uploaded_file($_FILES['file_browse']['tmp_name'], $filename);    
$file_data = fread(fopen($filename, "r"), filesize($filename));

//allow for a bigger packet size
$bigger = mysql_query("SET @@max_allowed_packet=16777216");

//make sure it worked
$x = mysql_query("SELECT @@max_allowed_packet");
$y = mysql_fetch_array($x);
$m1 = $y[0];
print "Max allowed packet is: ".$m1;
        
//upload file into database
$query = "INSERT INTO myfiles (file_title, file_type, file, submittedby) "
        ."VALUES ('".$_FILES['file_browse']['name']."', '".$file_type."', '".$file_data."', '".$session[user]."')";
$result = mysql_query($query); 


[melissa820]

OK I devised a bit of a work-around for this problem. If anyone else is interested, here it is below.

PHP Code:

//note: $err is an array for errors.  It is not set unless there is an error.

//transfer file to temporary directory
$filename="/tmp/".$_FILES['file_browse']['name'];
$file_type = $_FILES['file_browse']['type'];
move_uploaded_file($_FILES['file_browse']['tmp_name'], $filename);    
$file_data = fread(fopen($filename, "r"), filesize($filename));

//allow for a bigger packet size
$bigger = mysql_query("SET @@max_allowed_packet=16777216");
if (!$bigger) {
    $err[] = "<br>The query to allow larger files failed.!!!!\n";  
}

//check the file size, work appropriately
$oneMeg = 1047552;
if ($_FILES['file_browse']['size'] > $oneMeg) {
    //do the extra steps to upload the file in pieces
    $numMegs = ceil($_FILES['file_browse']['size']/$oneMeg);    //the uppermost number of megs in this file
    $numChars = strlen($file_data);                        //the number of characters in the data string
    $len = floor($numChars/$numMegs);                    //how many characters per section
    $fd_temp = $file_data;                                //placeholder
    $i = 0;                                                //array index incrementer
    while (strlen($fd_temp) > 0) {
        $section[$i] = substr($fd_temp, 0, $len);            //takes the first LEN characters
        $fd_temp = substr($fd_temp, $len);                    //is everything after the first substring is taken off.
        $i++;                                                //increment the array index
    }
    //insert the first chunk of file, get back the insert id.
    $query = "INSERT INTO myfiles (id, file_title, file_type, file, submittedby) "
            ."VALUES (NULL, '".$_FILES['file_browse']['name']."', '".$file_type."', '".$section[0]."', '".$session[user]."')";
    $result = mysql_query($query);
    $theID = mysql_insert_id();
    if (!$result) {
        $err[] = "Error: the first part of the file could not be inserted";
    }
    else if (!$theID) {
        $err[] = "Error: the last insert ID could not be retrieved.";
    }
    else {        //only run the next parts if the first part went in OK
        for ($i = 1; $i < count($section); $i++) {
            //update the row concatenating the next section on to the file data.
            $query = "UPDATE myfiles SET file = CONCAT(file, \"".$section[$i]."\") WHERE id = '".$theID."'";
            $result = mysql_query($query);
            if (!$result) {
                $err[] = "Error inserting a part of the file.  Please delete the incomplete file and try again.";
                break;
            }//end if
        }//end for
    }//end else
}//end for large files
else {
    //upload file into database all in one piece (it is smaller than the size limit)
    $query = "INSERT INTO myfiles (file_title, file_type, file, comments, submittedby) "
            ."VALUES ('".$_FILES['file_browse']['name']."', '".$file_type."', '".$file_data."', '".$session[user]."')";
    $result = mysql_query($query);
    if (!$result) {
        $err[] = "There was an error uploading this file: ".mysql_error();
    }
}