Bad gateway error on create new records

nlooije

Hi,

I am using the following PHP code to get data into a DataTable:

$editor = Editor::inst( $db, 'tbl', 'id' )
    ->fields(
        Field::inst( 'tbl.id' ),
        Field::inst( 'tbl.description' ),
        Field::inst( 'tbl.language' )
    )

This renders the data in the Datatable just fine.

But I when using the Editor to create new records in the table, it gives me 'Bad gateway' errors:
*12068790 recv() failed (104: Connection reset by peer) while reading response header from upstream
Unfortunately the error is very vague, all i was able to find on google is that too many request are made.

Now, if I remove the table reference from the fields:

$editor = Editor::inst( $db, 'tbl', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'description' ),
        Field::inst( 'language' )
    )

then i can successfully create the records.

Is this a bug or intended functionality?

If i were to join another table with similar field names then I would need to use table references so my guess is it is a bug.

allan

If you check your http server's error logs (or php-fpm if you are using that) does it have any other information. It looks to me like you might be using a reverse proxy (nginx with php-fpm?) and the PHP process is crashing for some reason. We need to know what that is.

Allan

nlooije

Hi Allan,

We are using Nginx but the only (vague) error i get is:
*12082905 recv() failed (104: Connection reset by peer) while reading response header from upstream
found in /var/log/nginx/error.log. Nothing else as far as i can see.
The response in the developer console is just 'bad gateway'.

Googling the error I get alot of increase 'memory' or 'max_requests' solutions
but given that the issue goes away when removing the the table references it doesn't seem like that is the root cause.

Any other way to find out why nginx is crashing? Google mainly points me to /var/log/nginx/error.log

Niels

nlooije

Just to follow up, I also checked the /var/log/php5.6-fpm logs:

[24-Feb-2022 20:57:17] WARNING: [pool www] child 5122 exited on signal 11 (SIGSEGV) after 38.295552 seconds from start
[24-Feb-2022 20:57:17] NOTICE: [pool www] child 5263 started

It says it is taking 38 seconds and (I guess) timing out, but this is weird as the error is generated as soon as I press the update button not after 38 seconds. See below for demonstration:

allan

It might be worth adding:

error_reporting(E_ALL);
ini_set('display_errors', '1');

At the top of your PHP file (just inside the <?php tag. I'm wondering if some logging is disabled.

Allan

nlooije

It is not giving any additional error messages unfortunately.
I also checked with the server admin and he also couldn't retrieve a more useful error message.

Side note: I just realized that the 38 seconds is not a timeout but how long the process was alive before it crashed. As you can see in GIF, after it crashes, a new process is started right away. So apparantly the process was started 38 seconds before it crashed when i press the Create button. This still doesn't give any useful info I think.

Just to check I updated to the latest version of Editor v2.06 and the issue occurs there too.

Now I figured out that adding a redundant leftJoin also solves the problem

So this does not work:

$editor = Editor::inst( $db, 'tbl', 'id' )
    ->fields(
        Field::inst( 'tbl.id' ),
        Field::inst( 'tbl.description' ),
        Field::inst( 'tbl.language' )
    )

but this works:

$editor = Editor::inst( $db, 'tbl', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'description' ),
        Field::inst( 'language' )
    )

and so does this:

$editor = Editor::inst( $db, 'tbl', 'id' )
    ->fields(
        Field::inst( 'tbl.id' ),
        Field::inst( 'tbl.description' ),
        Field::inst( 'tbl.language' )
    )
    ->leftJoin( 'tbl2', 'tbl2.id', '=', 'tbl.id')

but it seems akward to join a redundant table which isn't used just so the CREATE query works.

I would prefer that the first query also works.
Can you confirm if it should work?
Or is the Editor not designed so that table references are allowed if no joins are done?

Niels

allan

I've just tried this:

Editor::inst( $db, 'datatables_demo' )
    ->fields(
        Field::inst( 'datatables_demo.first_name' ),
        Field::inst( 'datatables_demo.last_name' ),
        Field::inst( 'datatables_demo.position' ),
        Field::inst( 'datatables_demo.email' ),
        Field::inst( 'datatables_demo.office' ),
        Field::inst( 'datatables_demo.extn' ),
        Field::inst( 'datatables_demo.age' ),
        Field::inst( 'datatables_demo.salary' ),
        Field::inst( 'datatables_demo.start_date' )
    )
    ->debug(true)
    ->process( $_POST )
    ->json();

And it does the insert no problem. What happens if you have the ->debug(true) flag in (before ->process(...))? It might not make any difference, or you might already have it, but worth a shot.

What database type is it connecting to?

Allan

nlooije

OK that is hopeful

So I am actually connecting to SQL Server 2008.
Datatables doesn't come with drivers for 2008 so I provided my own extending the SqlserverQuery lib file.
Main difference is that I can't do ROW OFFSETS etc to limit the number of records, I have to use a convoluted ROW_NUMBER() subquery.

Perhaps the problem is in there somewhere.

<?php
/**
 * SQL Server driver for DataTables PHP libraries
 *
 *  @author    SpryMedia
 *  @copyright 2013 SpryMedia ( http://sprymedia.co.uk )
 *  @license   http://editor.datatables.net/license DataTables Editor
 *  @link      http://editor.datatables.net
 */

namespace DataTables\Database\Driver;
if (!defined('DATATABLES')) exit();

/**
 * SQL Server driver for DataTables Database Query class
 *  @internal
 */
class Sqlserver2008Query extends SqlserverQuery {

    protected function _build_alias()
    {
        $a = array();

        foreach ($this->_field as $field) {

            if(strpos($field, ' as ') !== false) {
                $f = explode(' as ', $field);
                $a[] = "[{$f[1]}]"; 
            } else {
                $a[] = "[$field]";
            }

        }

        return ' '.implode(', ', $a).' ';
    }

    protected function _build_limit()
    {
        $out = '';

        if ( $this->_offset ) {
            $out .= "WHERE RowNum >= {$this->_offset}";
        }

        if ( $this->_limit ) {
            if ( ! $this->_offset ) {
                $out .= "WHERE RowNum >= 0";
            }
            $out .= " AND RowNum < {$this->_offset} + {$this->_limit}";
        }

        return $out; 
    }

    protected function _select()
    {
        $AsAlias = $this->_supportsAsAlias ? 'as' : '';

        $order = $this->_build_order();
        $_order = $order ? $order : 'ORDER BY (SELECT NULL)';

        $_distinct = $this->_distinct ? 'DISTINCT' : '';

        $_query = "
            WITH T AS (
                SELECT 
                    ROW_NUMBER() OVER ($_order) $AsAlias RowNum,
                    {$this->_build_field( true )}
                FROM {$this->_build_table()}
                {$this->_build_join()}
                {$this->_build_where()}
                {$this->_build_group_by()}
            )
            SELECT $_distinct {$this->_build_alias()}
            FROM T
            {$this->_build_limit()}
        ";

        // $_query = trim(preg_replace('/\s\s+/', ' ', $_query));
        $this->_prepare( $_query );

        return $this->_exec();
    }

}

nlooije

I do already use debug() in the query, it doesn't give additional debugging info because the process crashes before it can return actual data

allan

Ah! Server-side processing with our PHP libraries and SQL Server older than 2012 won't work I'm afraid. How many row do you have in the table - if less than 10'000, disable the serverSide option.

Allan

nlooije

Hi Allan,

I understand that it is not supported 'out-of-the-box' by the libraries.
However, with the above custom driver I am able to get the data into a table.
Furthermore as I showed above atleast in two cases (without sending table references and with a redundant join) I am able to also update tables.
It is just that with sending table references it doesn't seem to work even though it should (as you have shown).
So i would disagree that the PHP library doesn't work with SQL Server 2008.
Unfortunately I am not in a position to upgrade to atleast version 2012 so I am stuck for now with version 2008.
Is there any other aspect of the PHP library which doesn't work with version 2008 other than the driver file? Perhaps i can find the reason for the crash there

Niels

allan

No - I'm not aware of any other limitation with SQL Server 2008, but we also haven't tested against that version (since we support 2012 and newer) so it is possible I've missed something.

If you disable server-side processing - does it then work (just taking a while to load the data - how many rows are you working with?). That would at least narrow it down to the server-side processing code branch.

Allan