views:

534

answers:

2

Hi!

I am uploading files to a MySql DB through PHP. I am able to upload files upto 1MB size (found out by trial and error). Files greater than 1 MB in size are not getting uploaded. The MySql error printed by mysql_error() function in PHP is: MySQL server has gone away

Can anybody please help me with this? The MySql server is up and running only for requests > 1MB it is giving this error.

Regards, Mayank.

P.S.: I am using a form to upload the file. <FORM METHOD="post" ACTION="fileUpload.php" ENCTYPE="multipart/form-data"> <INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="300000000"> <INPUT TYPE="hidden" NAME="action" VALUE="upload"> Description: <TEXTAREA NAME="txtDescription" ROWS="1" COLS="80"></TEXTAREA> <INPUT TYPE="file" NAME="binFile" ID="binFile"> <INPUT TYPE="submit" NAME="Upload" VALUE="Upload"> </FORM>

+1  A: 

Your sql query probably exceeds the max_allowed_packet size in which case the server will disconnect.
You might be interested in mysqli_stmt::send_long_data which allows you to send parameters longer than max_allowed_packet in chunks.

Update: "How can i change it? Is using mysqli is the only option?"
Afaik the value can't be altered on a per-session base, i.e. if you cannot change the server configuration (my.cnf or startup parameters) the value will be read-only. edit: As the comment suggests you can change the global value of the mysql server after it has been started if you have the proper permissions. PDO/PDO_MYSQL (as of phpversion 5.3.0) doesn't seem to support send_long_data, but I'm not sure about that either. That would leave mysqli as the only option. I've recently noticed that Wez Furlong joined stack overflow. Since he is one of the authors of the PDO implementation he might know (though he did not write the pdo_mysql module).

(Completely untested and ugly) example

// $mysqli = new mysqli(....
$fp = fopen($_FILES['binFile']['tmp_name'], 'rb') or die('!fopen');

//$result = $mysqli->query('SELECT @@max_allowed_packet') or die($mysqli->error);
//$chunkSize = $result->fetch_all();
//$chunkSize = $maxsize[0][0];
$chunkSize = 262144; // 256k chunks

$stmt = $mysqli->prepare('INSERT INTO foo (desc, bindata) VALUES (?,?)') or die($mysqli->error);
// silently truncate the description to 8k
$desc = 8192 < strlen($_POST['txtDescription']) ? $_POST['txtDescription'] : substr($_POST['txtDescription'], 0, 8192);
$stmt->bind_param('sb', $desc, null);

while(!feof($fp)) {
  $chunk = fread($fp, $chunkSize);
  $stmt->send_long_data(1, $chunk) or die('!send_long_data.'.$stmt->error);
}
$result = $stmt->execute();
VolkerK
Hi Volker,max_allowed_packet size is 1MB by default, I think that is the problem.How can i change it? Is using mysqli is the only option?Regards,Mayank.
mkamthan
@mkamthan: see update
VolkerK
Hi Volker, I did it by altering the max_allowed_packet by using mysql> SET GLOBAL max_allowed_packet=16*1024*1024; // sets to 16MBThanks for the help.Regards,Mayank.
mkamthan
+2  A: 

In order to upload large files to your server with PHP, you need to change 2 parameters in your php.ini file.

; Maximum allowed size for uploaded files.
upload_max_filesize = 50M

; Maximum size of POST data that PHP will accept.
post_max_size = 50M

50M = 50Mb

Gary Willoughby
That doesn't account for the "MySQL server has gone away" message.
VolkerK
True. But i'd start with this first.
Gary Willoughby
This would also manifest itself as either UPLOAD_ERR_INI_SIZE or UPLOAD_ERR_PARTIAL in $_FILES['binFile']['error'], see http://php.net/features.file-upload.errors
VolkerK