Strange behavior

aarontharker

I can't put up the debug as I get an out of memory error when I try to run it. I'm building a table that is drawing from MariaDB via the PHP library. The table has 30 columns out of which 14 are joins. When the table opens the request is sent to the php about 5 seconds later I get a 200 response code but the response is empty. Over the next 10-15 seconds the response size grows by 2.5MB but the response remains empty. The table also remains empty.

This is where it gets strange, if I leave the leave the page open for 10-15 minutes eventually the table will populate with data. I can't give you access to the page unfortunately as it houses sensitive data. Below is a copy of the php script for retrieving the data.

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, 'members', 'id' )
    ->fields(
        Field::inst( 'members.name_prefix' )
            ->options( Options::inst()
                ->table( 'prefixes' )
                ->value( 'id' )
                ->label( 'prefix' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'prefixes.prefix' ),
        Field::inst( 'members.first_name' ),
        Field::inst( 'members.last_name' ),
        Field::inst( 'members.middlenames' ),
        Field::inst( 'members.name_suffix' )
            ->options( Options::inst()
                ->table( 'suffixes' )
                ->value( 'id' )
                ->label( 'suffix' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'suffixes.suffix' ),
        Field::inst( 'members.preferred_name' ),
        Field::inst( 'members.dob' )
            ->validator( Validate::dateFormat( 'm-d-y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'm-d-y' ) )
            ->setFormatter( Format::dateFormatToSql( 'm-d-y' ) ),
        Field::inst( 'members.nationality' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'nationality.name' ),
        Field::inst( 'members.phone' ),
        Field::inst( 'members.email' ),
        Field::inst( 'members.line' ),
        Field::inst( 'members.wechat' ),
        Field::inst( 'members.password' ),
        Field::inst( 'members.emer_name' ),
        Field::inst( 'members.emer_phone' ),
        Field::inst( 'members.emer_email' ),
        Field::inst( 'members.degree' )
            ->options( Options::inst()
                ->table( 'degree' )
                ->value( 'id' )
                ->label( 'shortcode' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'degree.shortcode' ),
        Field::inst( 'members.institution' )
        ->options( Options::inst()
            ->table( 'institution' )
            ->value( 'id' )
            ->label( 'shortname' )
        )
        ->validator( 'Validate::dbValues' ),
        Field::inst( 'institution.shortname' ),
        Field::inst( 'members.addresscheck' ),
        Field::inst( 'members.address1' ),
        Field::inst( 'members.address2' ),
        Field::inst( 'members.city' )
            ->options( Options::inst()
                ->table( 'cities' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'pcity.name' ),
        Field::inst( 'members.state' )
            ->options( Options::inst()
                ->table( 'states' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'pstate.name' ),
        Field::inst( 'members.country' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'pcountry.name' ),
        Field::inst( 'members.postcode' ),
        Field::inst( 'members.res_address1' ),
        Field::inst( 'members.res_address2' ),
        Field::inst( 'members.city_id' )
            ->options( Options::inst()
                ->table( 'cities' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'rcity.name' ),
        Field::inst( 'members.state_id' )
            ->options( Options::inst()
                ->table( 'states' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'rstate.name' ),
        Field::inst( 'members.country_id' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'rcountry.name' ),
        Field::inst( 'members.res_postcode' ),
        Field::inst( 'members.role' )
            ->options( Options::inst()
                ->table( 'roles' )
                ->value( 'id' )
                ->label( 'role' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'roles.role' ),
        Field::inst( 'members.activate' ),
        Field::inst( 'members.updated_at' )
            ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
            ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
            ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
        Field::inst( 'members.created_at' )
            ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
            ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
            ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
        Field::inst( 'members.gender' )
            ->options( Options::inst()
                ->table( 'gender' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'gender.name' ),
        Field::inst( 'members.partner_id' )
            ->options( Options::inst()
                ->table( 'partners' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'partners.name' ),
        Field::inst( 'members.student_id' )
    )
    ->leftJoin('prefixes', 'prefixes.id', '=', 'members.name_prefix')
    ->leftJoin('suffixes', 'suffixes.id', '=', 'members.name_suffix')
    ->leftJoin('degree', 'degree.id', '=', 'members.degree')
    ->leftJoin('institution', 'institution.id', '=', 'members.institution')
    ->leftJoin('partners', 'partners.id', '=', 'members.partner_id')
    ->leftJoin('roles', 'roles.id', '=', 'members.role')
    ->leftJoin('gender', 'gender.id', '=', 'members.gender')
    ->leftJoin('countries as rcountry', 'rcountry.id', '=', 'members.country_id')
    ->leftJoin('countries as pcountry', 'pcountry.id', '=', 'members.country')
    ->leftJoin('states as rstate', 'rstate.id', '=', 'members.state_id')
    ->leftJoin('states as pstate', 'pstate.id', '=', 'members.state')
    ->leftJoin('cities as rcity', 'rcity.id', '=', 'members.city_id')
    ->leftJoin('cities as pcity', 'pcity.id', '=', 'members.city')
    ->leftJoin('countries as nationality', 'nationality.id', '=', 'members.country')
    ->process( $_POST )
    ->json();

aarontharker

OK so I've tracked the source of the problem to the editor obtaining options for the country, city and state fields. Once I remove the code for that it works perfectly. So is there any other way I can populate those options fields?

allan

It sounds like the data is still streaming from the server and the debugger doesn't show the data until it is fully downloaded (which is why it appears blank initially).

So without these three statements:

            ->options( Options::inst()
                ->table( 'states' )
                ->value( 'id' )
                ->label( 'name' )
            )

(and the matching ones for country and city), how long does it take to load?

How large are each of those tables (i.e. does cities have millions of rows?). Also how large is your members table?

Allan