Обсуждение: mysql_fdw crash

Hi,

I do have a reproductible crash with mysql_fdw when executing a plpgsql function. I'm running pg 11.1 with current
mysql_fdw,but I had the same crash with the pg 9.6 and mysql_fdw provided with ubuntu packages.
 

From psql side :

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

In syslog :

Nov 20 10:52:58 sact2Dev kernel: [322982.294765] postgres[10364]: segfault at 0 ip 00007fc8ab7b5350 sp 00007ffc4312a4f0
error6 in mysql_fdw.so[7fc8ab7ac000+d000]
 

I've located the crash cause on the line :

*param_types[i] = exprType(param_expr);

( file mysql_fdw.c ; function prepare_query_params() ; in the forEach() loop)

I've recompiled the fdw with a -g option. Could you please tell me (or point me a documentation) how to have a core
dumpfrom the segfaulted lib ; so that I can open it in a debugger to inspect variable contents ?
 

Or any advice ?

Thank you.

Hi,


>When gdb will be active, then use command c, and then run query in session. gdb should to catch segfault. 



Thank you very much. It's been helpfull.

BTW behaviour is strange. When I'm executing following, I do have always a SEGV :

psql (11.1)
Type "help" for help.

herve=# CREATE OR REPLACE FUNCTION public.test_bug2(text,integer,timestamp with time zone)
herve-#  RETURNS integer
herve-# 
herve-# AS '
herve'# 
herve'#     select coalesce(max(id),1) from sact_v1.autocalls where  label=$1 and machine_id=$2 and created_date=$3;
herve'#     '
herve-#  LANGUAGE sql;
CREATE FUNCTION
herve=# select test_bug2('BSM_CRITICAL_SYSLOG',18843,now());

The GDB session :

Continuing.

Program received signal SIGSEGV, Segmentation fault.
prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0,
numParams=3,fdw_exprs=0x1c6b5b8, node=0x1c792d8) at mysql_fdw.c:2139
 
2139                    *param_types[i] = exprType(param_expr);
(gdb) bt
#0  prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0,
numParams=3,fdw_exprs=0x1c6b5b8, node=0x1c792d8)
 
    at mysql_fdw.c:2139
#1  mysqlBeginForeignScan (node=0x1c792d8, eflags=<optimized out>) at mysql_fdw.c:503
#2  0x000000000062ae94 in ExecInitForeignScan ()
#3  0x00000000006077bf in ExecInitNode ()
#4  0x000000000061117d in ExecInitAgg ()
#5  0x0000000000607717 in ExecInitNode ()
#6  0x0000000000601cf4 in standard_ExecutorStart ()
#7  0x000000000060d6ec in fmgr_sql ()
#8  0x00000000005fd504 in ExecInterpExpr ()
#9  0x00000000006258fb in ExecResult ()
#10 0x00000000006009aa in standard_ExecutorRun ()
#11 0x000000000073eaec in PortalRunSelect ()
#12 0x000000000073fede in PortalRun ()
#13 0x000000000073bd82 in exec_simple_query ()
#14 0x000000000073d249 in PostgresMain ()
#15 0x000000000047cff6 in ServerLoop ()
#16 0x00000000006cf7b3 in PostmasterMain ()
#17 0x000000000047ded1 in main ()

What is confusing, is that if I do the same with a pl/pgsql function (see below) I can run it 5 times, and the 6th exec
hitthe same SEGV...
 


CREATE OR REPLACE FUNCTION public.test_bug(text,text)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    plabel ALIAS FOR $1;
    spmachine_id ALIAS FOR $2;
    rid integer;
    lnow timestamp with time zone;
    pmachine_id INTEGER;

BEGIN
    pmachine_id := cast(spmachine_id as INTEGER);
    lnow:=now();

    select max(id) into rid from sact_v1.autocalls where  label=plabel and machine_id=pmachine_id and
created_date=lnow;
    rid := coalesce(rid,-1);

    return  rid;
END;
$function$;

CREATE FUNCTION
herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
----------
       -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
----------
       -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
----------
       -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
----------
       -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
----------
       -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');



Program received signal SIGSEGV, Segmentation fault.
prepare_query_params (param_types=0x1ca3558, param_values=0x1ca3550, param_exprs=0x1ca3548, param_flinfo=0x1ca3540,
numParams=3,fdw_exprs=0x1ca8638, node=0x1cade28) at mysql_fdw.c:2139
 
2139                    *param_types[i] = exprType(param_expr);
(gdb) bt
#0  prepare_query_params (param_types=0x1ca3558, param_values=0x1ca3550, param_exprs=0x1ca3548, param_flinfo=0x1ca3540,
numParams=3,fdw_exprs=0x1ca8638, node=0x1cade28)
 
    at mysql_fdw.c:2139
#1  mysqlBeginForeignScan (node=0x1cade28, eflags=<optimized out>) at mysql_fdw.c:503
#2  0x000000000062ae94 in ExecInitForeignScan ()
#3  0x00000000006077bf in ExecInitNode ()
#4  0x000000000061117d in ExecInitAgg ()
#5  0x0000000000607717 in ExecInitNode ()
#6  0x0000000000601cf4 in standard_ExecutorStart ()
#7  0x0000000000632946 in _SPI_execute_plan ()
#8  0x0000000000632d0b in SPI_execute_plan_with_paramlist ()
#9  0x00007ffb349aba22 in exec_stmt_execsql () from /usr/local/pgsql/lib/plpgsql.so
#10 0x00007ffb349ace43 in exec_stmts () from /usr/local/pgsql/lib/plpgsql.so
#11 0x00007ffb349af6d3 in exec_stmt_block () from /usr/local/pgsql/lib/plpgsql.so
#12 0x00007ffb349af88f in plpgsql_exec_function () from /usr/local/pgsql/lib/plpgsql.so
#13 0x00007ffb349a3375 in plpgsql_call_handler () from /usr/local/pgsql/lib/plpgsql.so
#14 0x00000000005fd504 in ExecInterpExpr ()
#15 0x00000000006258fb in ExecResult ()
#16 0x00000000006009aa in standard_ExecutorRun ()
#17 0x000000000073eaec in PortalRunSelect ()
#18 0x000000000073fede in PortalRun ()
#19 0x000000000073bd82 in exec_simple_query ()
#20 0x000000000073d249 in PostgresMain ()
#21 0x000000000047cff6 in ServerLoop ()
#22 0x00000000006cf7b3 in PostmasterMain ()
#23 0x000000000047ded1 in main ()

On 11/20/18 3:06 PM, 066ce286@free.fr wrote:
> Hi,
> 
>> When gdb will be active, then use command c, and then run query in session. gdb should to catch segfault.
> 
> Thank you very much. It's been helpfull.
> 
> BTW behaviour is strange. When I'm executing following, I do have always a SEGV :
> 
> psql (11.1)
> Type "help" for help.
> 
> herve=# CREATE OR REPLACE FUNCTION public.test_bug2(text,integer,timestamp with time zone)
> herve-#  RETURNS integer
> herve-#
> herve-# AS '
> herve'#
> herve'#     select coalesce(max(id),1) from sact_v1.autocalls where  label=$1 and machine_id=$2 and created_date=$3;
> herve'#     '
> herve-#  LANGUAGE sql;
> CREATE FUNCTION
> herve=# select test_bug2('BSM_CRITICAL_SYSLOG',18843,now());
> 
> The GDB session :
> 
> Continuing.
> 
> Program received signal SIGSEGV, Segmentation fault.
> prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0,
numParams=3,fdw_exprs=0x1c6b5b8, node=0x1c792d8) at mysql_fdw.c:2139
 
> 2139                    *param_types[i] = exprType(param_expr);
> (gdb) bt

So which part of that expression triggers the segfault? Try printing the 
different parts, i.e.

     p i
     p param_types[i]

It might be helpful to also install the debug package, which would give 
us more readable backtraces.

BTW, considering the failure is in mysql_fdw.c, this very much seems 
like a bug in mysql_fdw - have you tried reporting it through the 
project github repository?

     https://github.com/EnterpriseDB/mysql_fdw/issues

That's probably more likely to help, and even if we find a bug here we 
can't really commit that (perhaps some of the mysql_fdw authors are 
watching this list, but I'm not sure about that).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

066ce286@free.fr writes:
> What is confusing, is that if I do the same with a pl/pgsql function (see below) I can run it 5 times, and the 6th
exechit the same SEGV... 

That probably reflects switching from a custom plan to a generic plan
on the sixth execution.

            regards, tom lane

On Tue, Nov 20, 2018 at 7:59 PM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
>
> On 11/20/18 3:06 PM, 066ce286@free.fr wrote:
> > Hi,
> >
> >> When gdb will be active, then use command c, and then run query in session. gdb should to catch segfault.
> >
> > Thank you very much. It's been helpfull.
> >
> > BTW behaviour is strange. When I'm executing following, I do have always a SEGV :
> >
> > psql (11.1)
> > Type "help" for help.
> >
> > herve=# CREATE OR REPLACE FUNCTION public.test_bug2(text,integer,timestamp with time zone)
> > herve-#  RETURNS integer
> > herve-#
> > herve-# AS '
> > herve'#
> > herve'#     select coalesce(max(id),1) from sact_v1.autocalls where  label=$1 and machine_id=$2 and
created_date=$3;
> > herve'#     '
> > herve-#  LANGUAGE sql;
> > CREATE FUNCTION
> > herve=# select test_bug2('BSM_CRITICAL_SYSLOG',18843,now());
> >
> > The GDB session :
> >
> > Continuing.
> >
> > Program received signal SIGSEGV, Segmentation fault.
> > prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0,
numParams=3,fdw_exprs=0x1c6b5b8, node=0x1c792d8) at mysql_fdw.c:2139
 
> > 2139                    *param_types[i] = exprType(param_expr);
> > (gdb) bt
>
> So which part of that expression triggers the segfault? Try printing the
> different parts, i.e.
>
>      p i
>      p param_types[i]
>
> It might be helpful to also install the debug package, which would give
> us more readable backtraces.
>
> BTW, considering the failure is in mysql_fdw.c, this very much seems
> like a bug in mysql_fdw - have you tried reporting it through the
> project github repository?
>
>      https://github.com/EnterpriseDB/mysql_fdw/issues
>
> That's probably more likely to help, and even if we find a bug here we
> can't really commit that (perhaps some of the mysql_fdw authors are
> watching this list, but I'm not sure about that).

Thanks for reporting,

                     Oid **param_types)
{
    int         i;
    ListCell   *lc;

    Assert(numParams > 0);

    /* Prepare for output conversion of parameters used in remote query. */
    *param_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * numParams);

    *param_types = (Oid *) palloc0(sizeof(Oid) * numParams);

    i = 0;
    foreach(lc, fdw_exprs)
    {
        Node       *param_expr = (Node *) lfirst(lc);
        Oid         typefnoid;
        bool        isvarlena;

        *param_types[i] = exprType(param_expr);

Seems some basic mistake I think it should as below
(*param_types)[i] = exprType(param_expr);

After this it works
postgres=#  select test_bug2('BSM_CRITICAL_SYSLOG',18843,now());
 test_bug2
-----------
         1
(1 row)


-- 
Thanks and Regards
Mithun Chicklore Yogendra
EnterpriseDB: http://www.enterprisedb.com

Hi,

>Seems some basic mistake I think it should as below
>(*param_types)[i] = exprType(param_expr);
>
>After this it works


Seems to work fine from my side.

Thank you very much, it'd painful for me to find the bug, I've been too far away from C coding for a too long time :-(

--
Hervé LEFEBVRE