memory size error from time to time

hzhong

Hello,

I have a datatable editor php script used for server side processing. Sometimes, especially the first time requested by the front end, it reports "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in ......" But after refreshing the page, it successfully responds with json data which is just around 15 kb.

Does anyone have any idea why memory size error reports the first time requested, and then works well?

Thanks.

allan

Without seeing the script it is impossible to say. Perhaps it is doing a lot of reading of data? Or perhaps the parameters for server-side processing aren't being seen and it thus processes everything? Are you POSTing the DataTables Ajax request?

If you link to the page in question and show the code that would help address some of the questions I would need answers to.

Allan

hzhong

Thank you Allan. Below is the original code. It reports the same error when the URL of server-side script is put directly into the browser as a get request so I believe the problem is with the server-side script itself. The error reports from time to time. It sometimes responds with data successfully.

<?php

// DataTables PHP library
include( "Datatables-1.10.18/Editor-PHP-1.7.4/php/DataTables.php" );

$project_id = $_GET['project_id'];

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

/*
 * Define SQL
 */

      $data = Editor::inst( $db, 'daily_inspection', 'di_id' )
          ->field(
              Field::inst( 'daily_inspection.di_id' ),
              Field::inst( 'daily_inspection.project_id' ),
              Field::inst( 'daily_inspection.hour' ),
              Field::inst( 'daily_inspection.inspect_date' ),
                Field::inst( 'daily_inspection.fdate' ),
        Field::inst( 'daily_inspection.step_id' ),
        Field::inst( 'daily_inspection.app_id' ),
        Field::inst( 'application_type.application' ),
        Field::inst( 'application_step.app_step' ),
              Field::inst( 'daily_inspection.prep_surface_sa' )  ->getFormatter( function ( $val, $data ) {
                return $val
                    ? $val
                    : '';
    } ),
              Field::inst( 'daily_inspection.prep_surface_st' )  ->getFormatter( function ( $val, $data ) {
               return $val
                   ? $val
                   : '';
   } ),

        Field::inst( 'daily_inspection.tank_id' ),
        Field::inst( 'daily_inspection.notes' ),
        Field::inst( 'daily_inspection.ncrSubmit' ),
        Field::inst( 'daily_inspection.ncr_closed' ),
        Field::inst( 'vessel_tanks.vessel_tank_id' ),
        Field::inst( 'vessel_tanks.tank_number' ),
        Field::inst( 'vessel_tanks.tank_status' ),
        Field::inst( 'projects.projectname' )

          )
          ->join(
            Mjoin::inst( 'inspection_document' )
                ->link( 'daily_inspection.di_id', 'inspection_document.di_id' )
                ->fields(
                    Field::inst( 'inspection_document_id' ) ->set(false)
                )
        )
        ->join(
            Mjoin::inst( 'ncr_document' )
                ->link( 'daily_inspection.di_id', 'ncr_document.ncr_id' )
                ->fields(
                    Field::inst( 'ncr_document_id' ) ->set(false)
                )
        )

          ->leftJoin( 'projects', 'projects.projectid', '=', 'daily_inspection.project_id' )
      ->leftJoin( 'vessel_tanks', 'vessel_tanks.vessel_tank_id', '=', 'daily_inspection.tank_id' )
      ->leftJoin( 'application_step', 'application_step.step_id', '=', 'daily_inspection.step_id' )
      ->leftJoin( 'application_type', 'application_type.app_id', '=', 'daily_inspection.app_id' )
      ->where( 'daily_inspection.project_id', $project_id )


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

allan

And is your DataTables ajax object configured for POST (i.e. type: 'post')?

Editor-PHP-1.7.4

Is quite old. Editor 2.3.2 is the current release. There was a change in how the Mjoin was handled when server-side processing is enabled. It might be worth updating your server-side libraries and seeing if that has an impact.

Allan

hzhong

Thank you Allan. The ajax uses GET. The serverSide is disabled as the default setting.

I managed to make the script work by adding where for Mjoin which limits the data read in Mjoin

          ->join(
            Mjoin::inst( 'inspection_document' )
                ->link( 'daily_inspection.di_id', 'inspection_document.di_id' )
                ->where( 'daily_inspection.project_id', $project_id )
                ->fields(
                    Field::inst( 'inspection_document_id' ) ->set(false)
                )
        )
        ->join(
            Mjoin::inst( 'ncr_document' )
                ->link( 'daily_inspection.di_id', 'ncr_document.ncr_id' )
                ->where( 'daily_inspection.project_id', $project_id )
                ->fields(
                    Field::inst( 'ncr_document_id' ) ->set(false)
                )
        )

          ->leftJoin( 'projects', 'projects.projectid', '=', 'daily_inspection.project_id' )
      ->leftJoin( 'vessel_tanks', 'vessel_tanks.vessel_tank_id', '=', 'daily_inspection.tank_id' )
      ->leftJoin( 'application_step', 'application_step.step_id', '=', 'daily_inspection.step_id' )
      ->leftJoin( 'application_type', 'application_type.app_id', '=', 'daily_inspection.app_id' )
      ->where( 'daily_inspection.project_id', $project_id )


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

Wanted to make sure Editor 2 is backwards compatible with Editor 1.7.4, correct?

allan

The serverSide is disabled as the default setting.

Okay - so you aren't using server-side processing then.

I doubt that the upgrade will make much difference then, since the way you have it setup you are requesting the full amount of data on each request.

How many rows of data do you have (both in the main table and the two Mjoined tables)? My guess is that you are close to hitting the memory bound, and sometimes there is just enough to trigger it.

You could either switch to server-side processing mode, or increase the max RAM allowed in PHP.

Allan

hzhong

Thank you Allan. The main table has about 57k rows. One of the Mjoined table has 169k rows. Another one has less than 100 rows.

Is it right to say that when requesting full amount of data in client-side processing, the Mjoin is performed before filtering data, while in server-side processing, the Mjoin is performed after filtering data?

allan

Kind of... This is the part of the code that is of particular interest.

What happens is that the main table is queried first, and then if there are less than 1000 rows, Mjoin will do a WHERE IN condition to limit how many records it returns.

When you are client-side processing, that condition won't be met. So you'll be loading the full 57K rows, plus then the 169K from the Mjoin - that will eat up the PHP memory no question. You might want to look at increasing the PHP limit if you are getting errors about it, but performance is still acceptable.

With server-side processing enabled, only the first 10 (or however) records will be read from the main table, so the if condition in the Mjoin will be met, and this also speed things up there.

With that many rows, if you don't yet have server-side processing enabled, you might want to consider enabling it. The downside to server-side processing is that you cannot search or order on the Mjoined data.

Allan