Issue with large 16000+ tables loading. PHP Fatal error: Allowed memory size of 2147483648

ipgod · January 2016

Hi Everyone,

I have run into a strange issue. I have found that DB tables that have several hundred entries load and function properly. But if I try and load larger tables with something like 16,000 entities no data is displayed and I see that PHP is throwing a memory error on my server. I suspect I have something coded wrong or a feature I need to utilize to handle the larger tables. Below is a sample of what I believe to be the relevant code. Thanks for any help.

DataTables Live example:
'<a class="jsbin-embed" href="http://live.datatables.net/wojimaje/1/embed">DataTables - JS Bin</a>'

Debugger# ekemex

'var table = $('#IPAddressTable').DataTable({
scrollY: '50vh',
scrollCollapse: true,
deferRender: true,
scroller: {
loadingIndicator: true
},

        "order": [[ 0, "asc" ]],
        "columnDefs": [
        {
            "type": 'ip-address',
            "targets": [ 1 ],
            },
        {
            "targets": [ 5 ],
            "visible": false,
            },
        {
            "targets": [ 7 ],
            "visible": false,
            },
        {
            "targets": [ 8 ],
            "visible": false,
            },
        {
            "targets": [ 9 ],
            "visible": false,
            }
    ],
    dom: '<lf<t>B<"clear">i>',
    buttons: [
            'colvis',
            'copy',
            'print',
            'excel'
    ],
    processing: true,
    serverSide: true,
    select: {
        style: 'single'
    },
    "ajax":{
        url :"/IPv4Table-processing.php?tableName=" + IPtable + "&custSiteID=" + custSiteID,
        //type: "post",  // method  , by default get
        error: function(){  // error handling
                $(".IPAddressTable-error").html("");
                $("#IPAddressTable").append('<tbody class="IPAddressTable-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                $("#IPAddressTable_processing").css("display","none");

                }
        }
} );'

ipgod · January 2016

IPv4Table-processing.php

'<?php

// Create connection
$Conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($Conn->connect_errno) {
echo '

There was an error connecting to the database!

';
if ($mysqliDebug) {
echo $mysqli->connect_error;
}

// since there is no database connection your queries will fail,
// quit processing
die();

}

// Table's primary key
$primaryKey = 'id';

// storing request (ie, get/post) global array to a variable
$requestData= $_GET;

$custSiteID = htmlspecialchars($requestData['custSiteID']);
$table = htmlspecialchars($requestData['tableName']);

$columns = array(
// datatable column index => database column name
array(
'db' => 'id',
'dt' => 'DT_RowId'
),
array( 'db' => 'IPaddress', 'dt' => 0 ),
array( 'db' => 'IPmask', 'dt' => 1 ),
array( 'db' => 'type', 'dt' => 2 ),
array( 'db' => 'status', 'dt' => 3 ),
array( 'db' => 'hostName', 'dt' => 4 ),
array( 'db' => 'VPNname', 'dt' => 5 ),
array( 'db' => 'csid', 'dt' => 6 ),
array( 'db' => 'custShortName', 'dt' => 7 ),
array( 'db' => 'custSiteID', 'dt' => 8 ),
array( 'db' => 'notes', 'dt' => 9 ),
);
// getting total number records without any search
$sql = "SELECT id, IPaddress, IPmask, type, status, hostName, VPNname, csid, custShortName, custSiteID, notes ";
$sql.=" FROM $table";
$result = $Conn->query($sql) or die ('Could not perform query: ' . mysql_error());
$totalData = $result->num_rows;

// when there is no search parameter then total number rows = total number filtered rows.
$totalFiltered = $totalData;

$sql = "SELECT id, IPaddress, IPmask, type, status, hostName, VPNname, csid, custShortName, custSiteID, notes ";
$sql.=" FROM $table WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][1]['search']['value']) ){ //IP Address

$IPSearch = $requestData['columns'][1]['search']['value'];

$ipArray = explode(".", $IPSearch);
$ipArrayLength = count($ipArray);
$lastValue = $ipArray[$ipArrayLength - 1];


if ( $ipArrayLength == 4) {

    if ( $lastValue <> "" and $lastValue > 255) {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = $ipArray[2];
        $ipArray[3] = "255";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = $ipArray[2];
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } elseif ( $lastValue == "" ) {

        $ipArray[0] = $ipArray[0];
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = $ipArray[2];
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = $ipArray[2];
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } else {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = $ipArray[2];
        $ipArray[3] = $lastValue;

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = $ipArray[2];
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    }


} elseif ($ipArrayLength == 3) {

    if ( $lastValue <> "" and $lastValue > 255) {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = "255";
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } elseif ( $lastValue == "" ) {

        $ipArray[0] = $ipArray[0];
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } else {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = $ipArray[1];
        $ipArray[2] = $lastValue;
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = $ipArray[1];
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    }

} elseif ($ipArrayLength == 2) {

    if ( $lastValue <> "" and $lastValue > 255) {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = "255";
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } elseif ( $lastValue == "" ) {

        $ipArray[0] = $ipArray[0];
        $ipArray[1] = "0";
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = "255";
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } else {

        $ipArray[0] = $ipArray[0];  
        $ipArray[1] = $lastValue;
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = $ipArray[0];
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    }

} else {

    if ( $lastValue <> "" and $lastValue > 255) {

        $ipArray[0] = "255";
        $ipArray[1] = "0";
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = "255";
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } elseif ( $lastValue == "" ) {

        $ipArray[0] = "0";
        $ipArray[1] = "0";
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = "255";
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);

    } else {

        $ipArray[0] = $lastValue;
        $ipArray[1] = "0";
        $ipArray[2] = "0";
        $ipArray[3] = "0";

        $ipArrayMax[0] = "255";
        $ipArrayMax[1] = "255";
        $ipArrayMax[2] = "255";
        $ipArrayMax[3] = "255";

        $IPAddress = join ('.', $ipArray);
        $IPAddressMax = join ('.', $ipArrayMax);


    }

}

ipgod · January 2016

IPv4Table-processing.php Continued----

$IPAddressLong = ip2long($IPAddress);
$IPAddressMaxLong = ip2long($IPAddressMax);

$sql.=" AND ( IPaddress >= '".$IPAddressLong."' AND  IPaddress <= '".$IPAddressMaxLong."' ) ";

}
if( !empty($requestData['columns'][2]['search']['value']) ){ //Subnet Mask
$sql.=" AND IPmask LIKE '".ip2long($requestData['columns'][2]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][3]['search']['value']) ){ //Address Type
$sql.=" AND type LIKE '".$requestData['columns'][3]['search']['value']."%' ";
}
if( !empty($requestData['columns'][4]['search']['value']) ){ //Status of IP
$sql.=" AND status LIKE '".$requestData['columns'][4]['search']['value']."%' ";
}
if( !empty($requestData['columns'][5]['search']['value']) ){ //VPN Name
$sql.=" AND VPNname LIKE '".$requestData['columns'][5]['search']['value']."%' ";
}
if( !empty($requestData['columns'][6]['search']['value']) ){ //Customer Short Name
$sql.=" AND CustShortName LIKE '".$requestData['columns'][6]['search']['value']."%' ";
}
if( !empty($requestData['columns'][7]['search']['value']) ){ //Device Name
$sql.=" AND hostName LIKE '".$requestData['columns'][7]['search']['value']."%' ";
}
if( !empty($requestData['columns'][8]['search']['value']) ){ //CSID
$sql.=" AND csid LIKE '".$requestData['columns'][8]['search']['value']."%' ";
}
if( !empty($requestData['columns'][9]['search']['value']) ){ //Notes
$sql.=" AND notes LIKE '".$requestData['columns'][9]['search']['value']."%' ";
}
if( !empty($requestData['columns'][10]['search']['value']) ){ //Notes
$sql.=" AND notes LIKE '".$requestData['columns'][10]['search']['value']."%' ";
}

$result = $Conn->query($sql) or die ('Could not perform query: ' . mysql_error());

// when there is a search parameter then we have to modify total number filtered rows as per search result.
$totalFiltered = $result->num_rows;

// adding length
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']+1]['db']." ".$requestData['order'][0]['dir']." ";

$result = $Conn->query($sql) or die ('Could not perform query: ' . mysql_error());

$data = array();

while( $row = $result->fetch_row() ) { // preparing an array
$nestedData=array();

$nestedData['DT_RowId'] =   "id_".$row[0];

$nestedData['0'] = long2ip($row[1]);
$nestedData['1'] = long2ip($row[2]);
$nestedData['2'] = $row[3];
$nestedData['3'] = $row[4];
$nestedData['4'] = $row[5];
$nestedData['5'] = $row[6];
$nestedData['6'] = $row[7];
$nestedData['7'] = $row[8];
$nestedData['8'] = $row[9];
$nestedData['9'] = $row[10];

$data[] = $nestedData;
}

$json_data = array(
// for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"draw" => intval( $requestData['draw'] ),
// total number of records
"recordsTotal" => intval( $totalData ),
// total number of records after searching, if there is no searching then totalFiltered = totalData
"recordsFiltered" => intval( $totalFiltered ),
// total data array
"data" => $data
);

// send data as json format
echo json_encode($json_data);

<?php >' ?>

allan · January 2016

Sounds like you are just requesting more data than your server (PHP specifically) has available.

Are the query statements being created correctly? That's where I would suggest starting.

ipgod · January 2016

Thanks for your suggestion Allan. I will take a look at what the select statement looks for the larger tables. The tables are identical in the columns that they have ect. The difference is just in the number of entries. So I would assume the select statement should be okay given it has no issues with the smaller tables... But I will differently look into it. :)

ipgod · January 2016

Thanks Allan. I think I see where I need to go now. Looks like I will need to build out my IPv4Table-processing.php to take advantage of iDisplayStart and iDisplayLength. With this I should be able to limit the amount of data I'm feeding back into DataTables. Thanks again for your suggestion.

Issue with large 16000+ tables loading. PHP Fatal error: Allowed memory size of 2147483648

Issue with large 16000+ tables loading. PHP Fatal error: Allowed memory size of 2147483648

Answers

Howdy, Stranger!

Categories

DataTables

Issue with large 16000+ tables loading. PHP Fatal error: Allowed memory size of 2147483648

Issue with large 16000+ tables loading. PHP Fatal error: Allowed memory size of 2147483648

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables