Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 32 bytes)

mf

Dear all, this error probably means I'm trying to load too much data right?

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 32 bytes) in /var/www/asc/php/Database/Driver/Mysql/Result.php on line 58
Call Stack

Time Memory Function Location

1 0.0001 235608 {main}( ) ../table.bezoekverslagen.php:0
2 0.0027 433904 DataTables\Editor->process( ) ../table.bezoekverslagen.php:95
3 0.0027 434248 DataTables\Editor->_process( ) ../Editor.php:677
4 0.0029 436504 DataTables\Editor->_get( ) ../Editor.php:883
5 2.5043 33546040 DataTables\Database\DriverMysqlResult->fetch( ) ../Editor.php:1014
6 2.5043 33546136 fetch ( ) ../Result.php:58

allan

Yes. Try enabling server-side processing which should help.

The other option is to increase the PHP memory allocation.

Allan

mf

It is a table of 36211 rows and I am using a where statement (that would result in only about ten records) but the error is here

    public function fetch ()
    {
        return $this->_stmt->fetch( \PDO::FETCH_ASSOC );
    }

this is my php code

<?php
session_start();
/*
 * Editor server script for DB table bezoekverslagen
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'bezoekverslagen', 'bezoekverslagen_id' )
    ->fields(
        Field::inst( 'bezoekverslagen.beschrijving' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'bezoekverslagen.afspraak' ),
        Field::inst( 'bezoekverslagen.dealernr' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'dealercontacts.firstname' ),
        Field::inst( 'dealercontacts.initials' ),
        Field::inst( 'dealercontacts.lastname' ),
        Field::inst( 'dealercontacts.xstop' ),
        Field::inst( 'dealers.naam' ),
        Field::inst( 'dealers.bezoek_plaats' ),
        Field::inst( 'gespreksonderwerp.onderwerp' ),
        Field::inst( 'bezoekverslagen.registered' )->set( Field::SET_CREATE ),
        Field::inst( 'gespreksonderwerp.zichtbaar' ),
        Field::inst( 'bezoekverslagen.associate_id' )->set( Field::SET_CREATE ),
        Field::inst( 'bezoekverslagen.updated_associate_id' )->set( Field::SET_EDIT ),
        Field::inst( 'bezoekverslagen.opvolgen' )
            ->validator( 'Validate::dateFormat', array( 'format'=>Format::DATE_ISO_8601 ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
        Field::inst( 'bezoekverslagen.voltooid' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'bezoekverslagen.gespreksonderwerp_id' )->validator( 'Validate::notEmpty' )
            ->options( Options::inst()
                ->table( 'gespreksonderwerp' )
                ->value( 'gespreksonderwerp_id' )
                ->label( 'onderwerp')
                ->where( function ($q) {
                    $q->where( 'zichtbaar', 0 );
                })
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'bezoekverslagen.dealercontacts_id' )
            ->options( Options::inst()
                ->table( 'dealercontacts' )
                ->value( 'dealercontacts.dealercontacts_id' )
                ->label( array('dealercontacts.initials', 'dealercontacts.firstname', 'dealercontacts.lastname') )
                ->where( function ($q) {
                    $q->where( 'xstop', 0 );
                })
            )
            ->validator( 'Validate::dbValues' )
    )

    ->on( 'preCreate', function ( $editor, $values ) {   
        $editor->field( 'bezoekverslagen.associate_id' )->setValue( $_SESSION['user_session'] );
        $editor->field( 'bezoekverslagen.registered' )->setValue( date("Y-m-d H:i:s") );
    } )
  
    ->on( 'preEdit', function ( $editor, $id, $values ) {
        $editor
            ->field( 'bezoekverslagen.updated_associate_id' )
            ->setValue( $_SESSION['user_session'] );
    } )
  
  
    ->leftJoin( 'gespreksonderwerp', 'gespreksonderwerp.gespreksonderwerp_id', '=', 'bezoekverslagen.gespreksonderwerp_id' )
    ->leftJoin( 'dealers', 'dealers.dealernr', '=', 'bezoekverslagen.dealernr' )
    ->leftJoin( 'dealercontacts', 'dealercontacts.dealercontacts_id', '=', 'bezoekverslagen.dealercontacts_id' )

    /*->where('bezoekverslagen.associate_id', $_SESSION['user_session']) <- why is this not working?*/ 
    ->where('bezoekverslagen.voltooid', 0)
    ->process( $_POST )
    ->json();

I have also tried with a database that has about 3000 rows and I don't have any problems.

Is it really because the amount of rows?

Thanks!

allan

Yes - although if you are using a where statement to reduce it to only 10 rows, that really shouldn't be causing an out of memory issue. My guess is that the statement isn't operating quite as it should and all of the rows (or perhaps a limited set, but enough to trigger the memory error) are being read.

The issue is that the rows are being read into PHP's memory buffer, and its too small to cope with the amount of data.

Allan

mf

Dear Allan, thanks! the server side processing is verry cool!! I can't get it to work together with "always shown textbox" Is that an error in my code or expectable?

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.bezoekverslagen.php',
        table: '#bezoekverslagen',
        "fields": [ 
            {   "label": "onderwerp:",
                "name": "bezoekverslagen.gespreksonderwerp_id",
                "type":  "select" },
            {   "label": "beschrijving:",
                "name": "bezoekverslagen.beschrijving",
                "type": "textarea" },
            {   "label": "contact:",
                "name": "bezoekverslagen.dealercontacts_id",
                "type":  "select" },
            {   "label": "afspraak:",
                "name": "bezoekverslagen.afspraak" },
            {   "label": "opvolgen:",
                "name": "bezoekverslagen.opvolgen",
                "type": "datetime",
                "def": "0000-00-00",
                "format": "YYYY-MM-DD" },
            {   "label": "voltooid:",
                "name": "bezoekverslagen.voltooid",
                "type": "checkbox",
                "separator": "|",
                "options": [ { "label": '', "value": 1 } ] }
        ]
    } );

    var table = $('#bezoekverslagen').DataTable( {
        //ajax: 'php/table.bezoekverslagen.php',
        dom: "Bfrtip",
        ajax: {
            url: "php/table.bezoekverslagen.php",
            type: "POST"
        },
            serverSide: true,
        columns: [
            //{ data: "bezoekverslagen.bezoekverslagen_id" },
            { data: null, render: function ( data, type, row ) { return data.bezoekverslagen.dealernr+' '+data.dealers.naam+' '+data.dealers.bezoek_plaats;} },
            { data: "gespreksonderwerp.onderwerp" },
            { data: "bezoekverslagen.beschrijving" },
            { data: null, render: function ( data, type, row ) { return data.dealercontacts.initials+' '+data.dealercontacts.firstname+' '+data.dealercontacts.lastname;} },
            { data: "bezoekverslagen.afspraak" },
            { data: "bezoekverslagen.opvolgen" },
            { data: "bezoekverslagen.voltooid", render: function ( data, type, row ) { if ( type === 'display' ) { return '<input type="checkbox" class="editor-active">'; } return data; }, className: "dt-body-center" }
        ],
        lengthChange: false,
        select: {
            style: 'os',
            selector: 'td:not(:last-child)' // no row selection on last column
        },
        rowCallback: function ( row, data ) {
            $('input.editor-active', row).prop( 'checked', data.bezoekverslagen.voltooid == 1 );
          
            //var date = new Date();
            //year = date.getFullYear();
            //month = date.getMonth()+1;
            //dt = date.getDate();
            //if (dt < 10) { dt = '0' + dt; }
            //if (month < 10) { month = '0' + month; }
            //if ( data.bezoekverslagen.opvolgen < year+'-' + month + '-'+dt && data.bezoekverslagen.voltooid == 0) {        
            //  $(row).css('color', 'red').css('font-style', 'italic'); 
            //} else {
            //  $(row).css('color', 'black').css('font-style', 'normal'); 
            //}
        }
   

    } );
    
   $('#bezoekverslagen').on( 'change', 'input.editor-active', function () {
        editor
            .edit( $(this).closest('tr'), false )
            .set( 'bezoekverslagen.voltooid', $(this).prop( 'checked' ) ? 1 : 0 )
            .submit();
    } );  
  
    new $.fn.dataTable.Buttons( table, [
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-sm-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

allan

Can you link to the page showing the issue so I can attempt to help debug it and resolve the issue.

I don't know of any reason why the always shown checkbox wouldn't work with server-side processing.

Allan

mf

Dear Alan, the site is not public so I dont know how to share but thanks for your offer. Maybe I found it though, this is the error I am getting:

{"fieldErrors":[],"error":"Unknown field: (index 0)","data":[],"ipOpts":[],"cancelled":[]}

It does not seem to understand the value of column[0], when I comment this one

{ data: null, render: function ( data, type, row ) { return data.bezoekverslagen.dealernr+' '+data.dealers.naam+' '+data.dealers.bezoek_plaats;} },

instead of this one

//{ data: "bezoekverslagen.bezoekverslagen_id" },

it loads but column[3] is a problem too

The problem is because rendered data is just a calculation again?

allan

Ah! Thanks for the error message.

Set the client-side columns to not be orderable or searchable (columns.orderable and columns.searchable) and also change the default ordering (order) to be something other than the first column.

That should do it.

What is happening is that the server is attempting to process a column it knows nothing about.

Allan

mf

Thanks!!