File Upload - MySQL server has gone away

Question

I have the following file upload script. It is working great and my files are uploading perfectly. I am able to work with it. But this issue is not about file uploading, but the query being processed after the uploading. It throws error MySQL server has gone away. When I upload small files, the script works as expected and insert is working. Only when I upload big files, this issues happens. Even those big files are saved successfully, and the error only happens with MySQL Insert.

<?php
include('utils.php');
$result = array();
log_event("info", "Upload script initiated...");
log_event("info", "POST: ".json_encode($_POST, JSON_PRETTY_PRINT));
if(!isset($_FILES['file']['name'])){
    $result['error'] = "File not present";  echo json_encode($result); exit();
}
if(isset($_POST['auth'])){
    $is_valid = cookie_login($_POST['auth']);
    if($is_valid){
        log_event("info", "Authenticated for uploading.");
        if(isset($_POST['id'])){
            $exist = get_project($_POST['id']);
            if(!$exist){
                $result['error'] = "Project not specified";
            } else{
                log_event("info", "Project loaded. Analysing files for upload.");
                log_event("info", "FILES: ".json_encode($_FILES, JSON_PRETTY_PRINT));
                $file_name = $_FILES['file']['name'][0];
                $file_size = $_FILES['file']['size'][0];
                $file_tmp = $_FILES['file']['tmp_name'][0];
                $file_type = $_FILES['file']['type'][0];
                $file_name = mysql_real_escape_string($file_name);
                $file_ext=$ext = pathinfo($file_name, PATHINFO_EXTENSION);
                //print_r($_FILES['file']);

                $token = md5($file_name. $file_size.$file_tmp.$file_type. time() . rand(0, 99999) . rand(0, 99999));
                log_event("info", "Token Generated: $token");
                $filePath = "files/".$token;
                log_event("info", "Movable file path named after token: $filePath");
                if(!move_uploaded_file($file_tmp, $filePath)){
                    $result['error'] = "File Copying Error";
                    log_event("error", "File copying error");
                } else {
                    $sql = mysql_query("INSERT INTO files (name, path, package_name, token, username) VALUES ('$file_name', '$filePath', '$exist[package_name]', '$token', '$is_valid')");
                    if(!$sql){
                        $result['error'] = "Internal Server Error. Contact Administrator.";
                        log_event("error", mysql_error());
                    }
                }


            }
        } else {
            $result['error'] = "Project not specified";
        }
    } else {
        $result['error'] = "Authenticaton Failure";
    }
} else {
    $result['error'] = "Authenticaton Failure";
}

echo json_encode($result);

To analyse what is wrong, I created a log_event() method which just logs what the events to a file. These are the results.

[Wednesday 1st of June 2016 09:08:42 PM][info][http://www.example.com/bucket/upload.php] cookie_login(db64f68dee27eb08d29117c7da678f81): Success 
[Wednesday 1st of June 2016 09:08:42 PM][info][http://www.example.com/bucket/upload.php] Project loaded. Analysing files for upload. 
[Wednesday 1st of June 2016 09:08:42 PM][info][http://www.example.com/bucket/upload.php] FILES: {
    "file": {
        "name": [
            "Sibidharan_Software_Engineer_Android 3.zip"
        ],
        "type": [
            "application\/zip"
        ],
        "tmp_name": [
            "\/tmp\/phpTJJcQz"
        ],
        "error": [
            0
        ],
        "size": [
            97226470
        ]
    }
} 
[Wednesday 1st of June 2016 09:08:42 PM][info][http://www.example.com/bucket/upload.php] Token Generated: 599293ad3d30fd0d981f967df9d0f61f 
[Wednesday 1st of June 2016 09:08:42 PM][info][http://www.example.com/bucket/upload.php] Movable file path named after token: files/599293ad3d30fd0d981f967df9d0f61f 
[Wednesday 1st of June 2016 09:09:05 PM][error][http://www.example.com/bucket/upload.php] MySQL server has gone away

If you see the time between the last 2 process, it took almost 23 seconds to process the query. I have no idea why it is happening. **

As I said above

When I upload small files, the script works as expected. Only when I upload big files, this issues happens.

** Moreover, the issue is not with my localhost, but only with the live server.

My PHP_MAX_UPLOAD is set to 128M and uploaded file size is around 98M which you can see in the log.

UPDATE - Here is how I connect to database

$db = NULL;
$logs_enabled = "yes";

function dbConnect(){

    $DB_SERVER = "localhost";
    $DB_USER = "root";
    $DB_PASSWORD = "";
    $DB = "bucket";

    $GLOBALS['db'] = mysql_pconnect($DB_SERVER,$DB_USER,$DB_PASSWORD);

    if (!$GLOBALS['db']){
        log_event("error", "mysql_pconnect(): ".mysql_error());
        return false;
    }
    if($GLOBALS['db']){
        log_event("success", "mysql_pconnect(): Connection obtained");
        $check = mysql_select_db($DB,$GLOBALS['db']);
        if($check){
            log_event("success", "mysql_pconnect(): Database selected");
             mysql_query ("set character_set_client='utf8'"); 
             mysql_query ("set character_set_results='utf8'"); 
             mysql_query ("set collation_connection='utf8_general_ci'");
        } else {
            log_event("error", "mysql_pconnect(): Couldn't select database. Error: ".mysql_error());
        }
        return true;
    }
}

This is done in the utils.php file which is included in the above script.

UPDATE 2 As said by Sasha Pachev I tried putting `dbConnect() before the query and I got the following log.

[Thursday 2nd of June 2016 12:41:57 AM][info][http://www.example.com/bucket/upload.php] Project loaded. Analysing files for upload. 
[Thursday 2nd of June 2016 12:41:57 AM][info][http://www.example.com/bucket/upload.php] FILES: {
    "file": {
        "name": [
            "Sibidharan_Software_Engineer_Android 3.zip"
        ],
        "type": [
            "application\/zip"
        ],
        "tmp_name": [
            "\/tmp\/php3Gtac3"
        ],
        "error": [
            0
        ],
        "size": [
            97226470
        ]
    }
} 
[Thursday 2nd of June 2016 12:41:57 AM][info][http://www.example.com/bucket/upload.php] Token Generated: f2131640ca97468b62e4ab2b2eed25f9 
[Thursday 2nd of June 2016 12:41:57 AM][info][http://www.example.com/bucket/upload.php] Movable file path named after token: files/f2131640ca97468b62e4ab2b2eed25f9 
[Thursday 2nd of June 2016 12:42:20 AM][success][http://www.example.com/bucket/upload.php] mysql_pconnect(): Connection obtained 
[Thursday 2nd of June 2016 12:42:20 AM][error][http://www.example.com/bucket/upload.php] mysql_pconnect(): Couldn't select database. Error: MySQL server has gone away 
[Thursday 2nd of June 2016 12:42:20 AM][error][http://www.example.com/bucket/upload.php] MySQL server has gone away

Result: The error still persists.

You are vulnerable to [sql injection attacks](http://bobby-tables.com). — Marc B, Jun 01 '16 at 18:26
That is not a problem now. I know how to handle them. Please help me with the `MySQL server has gone away ` error — Sibidharan, Jun 01 '16 at 18:27
if you know how to handle them, why are you NOT handling them in the above code? — Marc B, Jun 01 '16 at 18:28
@MarcB they are already handled inside the `cookie_login()` method. — Sibidharan, Jun 01 '16 at 18:28
@MarcB for file names, i just stuck with this error.. so didnt care about the injection now! — Sibidharan, Jun 01 '16 at 18:29
Try a bigger number than 128 (like 256). Did you change `load_max_filesize` and `post_max_size` ? — Jose Manuel Abarca Rodríguez, Jun 01 '16 at 18:30
Is base64 encoding going on anywhere? 98 megs base64 encoded is ~130 megs. — Mike, Jun 01 '16 at 18:31
@Mike No i am not encoding anything.. Just inserting them into database. File is successfully saved in the server. — Sibidharan, Jun 01 '16 at 18:35
I insist : did you change both `load_max_filesize` as well as `post_max_size` ? Try assigning `999M` to those guys, then restart all. And don't forget `max_allowed_packet = 64M` in my.ini !!! — Jose Manuel Abarca Rodríguez, Jun 01 '16 at 18:46
I am saying, file is getting uploaded. No issues in that. The SQL Query is having the issue now — Sibidharan, Jun 01 '16 at 18:48

Sasha Pachev · Answer 1 · 2016-06-01T18:37:14.267

0

I think what is happening is that your file upload takes a long time, and the server times you out due to a low setting of wait_timeout. If you connect immediately after you have finished the upload and immediately before your insert, the "gone away" problem will likely be resolved. So put a call to dbConnect() right before mysql_query() with the insert.

edited Jun 01 '16 at 18:37

answered Jun 01 '16 at 18:31

Sasha Pachev

5,162
3
20
20

I am adding my `db_connect()` method. – Sibidharan Jun 01 '16 at 18:32
PLEASE CHECK, I UPDATED THE QUESTION – Sibidharan Jun 01 '16 at 18:34
WIll check and let u know - THanks – Sibidharan Jun 01 '16 at 18:47
PHP doesn't start executing and then if it comes to a `$_FILES` reference, wait for the file to be uploaded before continuing with the rest of the script. The web server sends everything to PHP when everything is ready and only then does the script start being executed. – Mike Jun 01 '16 at 18:55
@Mike I am trying your solution now. – Sibidharan Jun 01 '16 at 19:14
Please check @Sasha I updated the results of what you said – Sibidharan Jun 01 '16 at 19:17
Can you double-check that the code really calls `dbConnect()` immediately before `mysql_query()`. If yes, can you double check that `mysql_select_db()` indeed happens successfully inside `dbConnect()`? It would be rather odd for `mysql_select_db()` to succeed and immediately after for `mysql_query()` to fail with "server gone away" on the same connection. Also, what happens if you replace `mysql_pconnect()` with `mysql_connect()`? – Sasha Pachev Jun 01 '16 at 19:23
@SashaPachev It is working for small files.. Even a file of 60 MB works.. I tried a 97.5 MB file and it is failing...! Ok now lemme check with `mysql_connect()`. However `mysql_select_db()` is failing. – Sibidharan Jun 01 '16 at 19:31
I am guessing `mysql_pconnect()` the second time around does not really connect, but instead gives you a stale connection that already timed out. You can either replace it with `mysql_connect()`, or you can make your `dbConnect()` more robust by making it close the connection and retry once if `mysql_select_db()` failed. – Sasha Pachev Jun 01 '16 at 19:46

File Upload - MySQL server has gone away

1 Answers1