Allowed memory size exhausted

davidjmorin

I have a DB table that has over 1M rows. When I try to use serverSide so that it doesn't pull it all I still get the error and the page doesnt load. Anyone know what I am missing here?

<b>Fatal error</b>: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 4096 bytes) in <b>/var/www/prod/portal/salesByproduct/php/lib/Database/Driver/MysqlQuery.php</b> on line <b>105</b><br />

<?php

session_start();
$location = $_SESSION['district'];

require '../../../vendor/autoload.php'; //Dont forget to edit this
include( "lib/DataTables.php" );

use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;

$editor = Editor::inst( $db, 'sales_by_product' )
        ->fields(
            Field::inst( 'sales_by_product.Invoice_' ),
            Field::inst( 'sales_by_product.Invoiced_At' ),
            Field::inst( 'sales_by_product.Sold_By' ),
            Field::inst( 'sales_by_product.Sold_On' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
             ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
             ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
            Field::inst( 'sales_by_product.Invoice_Comments' ),
            #Field::inst( 'sales_by_product.Customer' ),
            Field::inst( 'sales_by_product.Product_SKU' ),
            Field::inst( 'sales_by_product.Tracking_' ),
            Field::inst( 'sales_by_product.Sold_As_Used' ),
            Field::inst( 'sales_by_product.Contract_' ),
            Field::inst( 'sales_by_product.Product_Name' ),
            Field::inst( 'sales_by_product.Refund' ),
            Field::inst( 'sales_by_product.Quantity' ),
            Field::inst( 'sales_by_product.Unit_Cost' ),
            Field::inst( 'sales_by_product.Total_Cost' ),
            Field::inst( 'sales_by_product.List_Price' ),
            Field::inst( 'sales_by_product.Selling_Price' ),
            Field::inst( 'sales_by_product.Original_Price' ),
            Field::inst( 'sales_by_product.Adjusted_Price' ),
            Field::inst( 'sales_by_product.Net_Profit' ),
            Field::inst( 'sales_by_product.Maine_Sales_Tax' ),
            Field::inst( 'sales_by_product.Mass_Phone_Sales_Tax' ),
            Field::inst( 'sales_by_product.Mass_Accessories_Sales_Tax' ),
        )
   ->leftJoin( 'locations', 'sales_by_product.Invoiced_At', '=','locations.RQ_Name'  );


         if($_SESSION['role'] != "Admin" ) {
             $editor->where( 'locations.district', $location );

}



$editor
    ->process( $_POST )
    ->json();

(function($) {

  var date = new Date();
  var firstDay = new Date(date.getFullYear(), date.getMonth() - 2, 1);
  var lastDay = new Date(date.getFullYear(), date.getMonth() - 1, 0);
  var firstDay = firstDay.toISOString().slice(0,10);
  var lastDay = lastDay.toISOString().slice(0,10);


    $(document).ready( function () {
  var table = $('#activations').DataTable({
        ajax: 'php/table.activations.php',
        dom: 'QBlfrtip',
        select: true,
    serverSide: true,
    Processing: true,
        pageLength: 50,
    lengthChange: false,
        scrollX: true,
    language: {
            searchBuilder: {
                button: 'Filter',
                title: 'Add your search filter below.  Options are date and location'
            }
        },

        buttons: [

                {
                        extend: "excelHtml5",
                        text: "Export Results"
                },


        ],

    searchBuilder: {
            columns: [1,10],
            preDefined: {
              criteria: [
                {

                    data: 'Sold_On',
                    condition: 'between',
                    value: [firstDay, lastDay]
                },

            ]
        },
        },


        columns: [
      {data: "sales_by_product.Invoice_"},
      {data: "sales_by_product.Invoiced_At"},
      {data: "sales_by_product.Sold_By"},
      {data: "sales_by_product.Sold_On"},
      {data: "sales_by_product.Invoice_Comments"},

      {data: "sales_by_product.Product_SKU"},
      {data: "sales_by_product.Tracking_"},
      {data: "sales_by_product.Sold_As_Used"},
      {data: "sales_by_product.Contract_"},
      {data: "sales_by_product.Product_Name"},
      {data: "sales_by_product.Refund"},
      {data: "sales_by_product.Quantity"},
      {data: "sales_by_product.Unit_Cost"},
      {data: "sales_by_product.Total_Cost"},
      {data: "sales_by_product.List_Price"},
      {data: "sales_by_product.Selling_Price"},
      {data: "sales_by_product.Original_Price"},
      {data: "sales_by_product.Adjusted_Price"},
      {data: "sales_by_product.Net_Profit"},
      {data: "sales_by_product.Maine_Sales_Tax"},
      {data: "sales_by_product.Mass_Phone_Sales_Tax"},
      {data: "sales_by_product.Mass_Accessories_Sales_Tax"},

        ],

  });
  table.searchBuilder.container().prependTo(table.table().container());
} );



}(jQuery));

<b>Fatal error</b>:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 4096 bytes) in <b>/var/www/prod/portal/salesByproduct/php/lib/Database/Driver/MysqlQuery.php</b> on line <b>105</b><br />

davidjmorin

~~Nevermind... Solved.~~

Actually... Its working now but search is only limited to the first page. Also, the search conditions are not filtering for the dates requested. Most likely because they are not loaded. Any suggestions here?

davidjmorin

Updated my query to this for it to work

    $(document).ready( function () {
  var table = $('#activations').DataTable({
    ajax: {
            url: "php/table.activations.php",
            type: "POST"
        },
        serverSide: true,
        dom: 'QBlfrtip',
        select: true,
        pageLength: 50,
    lengthChange: false,
        scrollX: true,

Still cant search though

colin

As you've got serverSide, the search will be performed on the server, so it'll be something in the script there. Could you post that script, please (assuming it's been updated since your first post). Also, can you post the response from the server to a search. If possible, can you link to your page so we can look at it there.

Colin