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

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

mfmf Posts: 41Questions: 11Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    The other option is to increase the PHP memory allocation.

    Allan

  • mfmf Posts: 41Questions: 11Answers: 0

    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!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mfmf Posts: 41Questions: 11Answers: 0
    edited October 2017

    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));
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mfmf Posts: 41Questions: 11Answers: 0
    edited October 2017

    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?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • mfmf Posts: 41Questions: 11Answers: 0

    Thanks!!

This discussion has been closed.