Large result sets with editor server side processing returning memory error

johnymutton

I have a mysql database with close to 1 million records.

Using the datatables Editor framework and server side processing, I am able to retrieve about 200k records without an error. Once I try to retrieve more, I get an out of memory error coming from MySql.

"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 403196984 bytes)"

I also have filter fields at the bottom of each column that perform filters using AND filtering on those columns.

Ultimately, I assume that I need to reject queries that return more than 200k records.

All the posts I have seen for Editor and server side processing don't mention an upper limit to the results set.

I have two ideas about how to handle this situation.
1. return a message asking for filters if no filter field has a value
2. return a message asking for more filters if the current filters are returning more than 200k records.

Questions:
1. Should I be experiencing a memory error while using the Editor and server side processing since I don't see any mention of an upper limit to the results set.
2. If there is an upper limit to the results set, what is the best way to implement either of the two ideas listed above.

Here is the table definition:

// begin javascript for footer filters
    $('#lightning tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );
// end javascript for footer filters

var editor = new $.fn.dataTable.Editor( {
    ajax: 'editor_php/table.lightning.php',
    table: '#lightning',
    fields: [
        {
            "label": "prefix:",
            "name": "prefix"
        },
        {
            "label": "first_name:",
            "name": "first_name"
        },
        {
            "label": "middle_name:",
            "name": "middle_name"
        },
        {
            "label": "last_name:",
            "name": "last_name"
        },
        {
            "label": "suffix:",
            "name": "suffix"
        },
        {
            "label": "name:",
            "name": "name"
        },
        {
            "label": "title:",
            "name": "title"
        },
        {
            "label": "title_full:",
            "name": "title_full"
        }
    ]
} );

var table = $('#lightning').DataTable( {
    //"pageLength":25,
    iDisplayLength: 50,
    // hide some columns initially
    // targets are the index of the columns. -1 is the last column, 0 is the first column
    "columnDefs": [
        {
            "targets": [ 
                ,9// npi
                ,12// phone_cell
                ,13// phone_alt                 
                ,25// website
                ,26// employees
                ,27// interests
                ,28// industry
                ,29// sector
                ,30// contact_type
                ,33// notes
                ,34// notes_maint
                ,35// created
                ,36// added
                ,37// lead_owner
                ,38// lead_source
                ,39// lead_file
                ,40// lead_score
                ,41// lead_status
                ,42// bounces
                ,44// email_result
                ,45// email_reason
                ,46// email_unsubscribe_reason
                ,47// email_sendex
                ,48// email_delivered
                ,49// email_opened
                ,50// email_clicked
                ,51// email_role
                ,52// email_free
                ,53// has_opportunity
                ,54// qualified
                ,55// contact
                ,57// optin
                ,58// ss_id
                ,59// ibcces_id
                ,60// cog_id
                 ],
            "visible": false,
            "searchable": true,
            "autoWidth": true
        }],
    // end hide some columns initially
    //dom: 'Blfrtip',
    dom: "Bp<'clear'>lrtip",
    ajax: {
        url: 'editor_php/table.lightning.php',
        type: "POST"
    },
    serverSide:true,
    //"order": [[ 3, "asc" ]], //initial sort on last name ascending

    columns: [
        {
            "data": "prefix"
        },
        {
            "data": "first_name"
        },
        {
            "data": "middle_name"
        },
        {
            "data": "last_name"
        },
        {
            "data": "suffix"
        },
        {
            "data": "name"
        },
        {
            "data": "title"
        }
    ],
    select: true,
    lengthChange: true,
    buttons: [
        { extend: 'create', text: 'New', editor: editor },
        { extend: 'edit',   editor: editor },
        { extend: 'remove', editor: editor },
        {
            extend: 'colvis',
            collectionLayout: 'fixed four-column'
        },
        // 'copy',
        // 'excel',
        // 'csv',
        // 'pdf',
        // 'print',
        {
            extend: 'collection',
            text: 'Export',
            buttons: [
                'copy',
                'excel',
                //'csv',
                {
                    text: 'CSV',
                    extend: 'csv',
                    fieldSeparator: ',',
                    //fieldBoundary:'',
                    extension: '.csv'
                },
                {
                    text: 'TSV',
                    extend: 'csv',
                    fieldSeparator: '|',
                    fieldBoundary:'',
                    extension: '.tsv'
                },
                'pdf',
                'print'
            ]
        },
        //"selectRows",
        //"selectColumns",
        //"selectCells",
        //"selectNone"
    ]
}

Here is the server side code of "editor_php/table.lightning.php" noted as the ajax call above.

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'lightning', 'id' )
    ->fields(
        Field::inst( 'prefix' )->setFormatter( Format::ifEmpty( "" ) ),
        Field::inst( 'first_name' ),
        Field::inst( 'middle_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'suffix' ),
        Field::inst( 'name' ),
        Field::inst( 'title' )
    )
    ->process( $_POST )
    ->json();

tangerine

I get an out of memory error coming from MySql.

That error message is generated from PHP. You can use ini_set() to increase the available memory. For example, "ini_set('memory_limit','16M');"

DataTables and the Editor do not impose an upper limit to the number of records.

allan

As @tangerine says, there is no upper limit. There can however be a variety of factors that would cause this. The most likely one here is that server-side processing is not running for some reason so its trying to pull the entire record set out and that will run out of memory.

I don't see anything wrong with your initialisation though - it looks like server-side processing should be implemented there.

Are you able to add ->debug( true ) immediately before the ->process( $_POST ) call please? Also, you'll probably need to clone your table and reduce it to say 100 records, just to allow the script to complete (otherwise we won't see the debug information!). Then can you show me the JSON that is returned when the table.lightning.php page is called via Ajax?

Thanks,
Allan

johnymutton

Thank you for your replies.

I must have had a caching issue, because when I loaded the page up this morning after putting in the ->debug(true) line, it worked. Another issue I had was the client wanted to be able to export and since server side processing only exports what it shows, I ended up finding a post where you could add more options in the table definition for display such as

    "lengthMenu": [[25, 50, 100, 250, 500, 1000, 2000, 5000, 10000, 15000, -1], [25, 50, 100, 250, 500, 1000, 2000, 5000, 10000, 15000, 'All']],

I found this solution from another post
https://datatables.net/forums/discussion/42277/server-side-processing-invalid-json-response

Thanks again for your help!

allan

The other option for when using server-side processing and exporting is to make use of this plug-in which will call the server with the latest server-side processing parameters, and the server would then create the Excel or CSV file as needed.

Allan