[asterisk-bugs] [JIRA] (ASTERISK-29506) res_pjsip: Not able to update existing contact entry in realtime

[George Joseph (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-29506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

George Joseph updated ASTERISK-29506:
-------------------------------------

    Assignee: Ivan Vynogradov  (was: Unassigned)
      Status: Waiting for Feedback  (was: Triage)

I've been trying to reproduce this issue but can't.  Does this happen constantly or only occasionally?  Also can you provide your sorcery.conf file?

One suggestion though,  use the native PostgreSQL module (res_config_pgsql) instead of ODBC.  It's configuration (res_pgsql.conf) is quite simple and it cuts out the middle-man between Asterisk and the database.  Here's a sample config...

res_pgsql.conf:
{code}
[general]
;dbhost=127.0.0.1
;dbport=5432
dbsock=/tmp
dbname=asterisk   ; Set as appropriate 
dbuser=asterisk   ; Set as appropriate
dbpass=asterisk   ; Set as appropriate
requirements=warn
{code}

extconfig.conf:
{code}
;ps_aors = pgsql,ignored
;ps_auths = pgsql,ignored
ps_contacts = pgsql,ignored
;ps_domain_aliases = pgsql,ignored
;ps_endpoint_id_ips = pgsql,ignored
;ps_endpoints = pgsql,ignored
;ps_globals = pgsql,ignored
;ps_registrations = pgsql,ignored
;ps_systems = pgsql,ignored
;ps_transports=pgsql,ignored
{code}


> res_pjsip: Not able to update existing contact entry in realtime
> ----------------------------------------------------------------
>
>                 Key: ASTERISK-29506
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-29506
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Channels/chan_pjsip, Resources/res_config_odbc
>    Affects Versions: 16.19.0
>         Environment: centos 7, Asterisk 16.19.0, 1 CPU, 2Gb Ram
>            Reporter: Ivan Vynogradov
>            Assignee: Ivan Vynogradov
>
> pjsip enitties are in realtime
> softphone is connected success and add record to ps_contacts: so insert to postgresql db work correct
> select * from asterisk.ps_contacts;
> -[ RECORD 1 ]--------+--------------------------------------------------------------------------
> id                   | 8001^3B at d8ecf55d261259430438629aa0c32576
> uri                  | sip:8001 at 94.158.154.84:64679^3Bob^3Bx-ast-orig-host=192.168.244.252:64679
> expiration_time      | 1625056306
> qualify_frequency    | 60
> outbound_proxy       | 
> path                 | 
> user_agent           | MicroSIP/3.20.5
> qualify_timeout      | 3
> reg_server           | 
> authenticate_qualify | no
> via_addr             | 192.168.244.252
> via_port             | 64679
> call_id              | ddd78192a6ee4bbbbbc7db2a436c86b9
> endpoint             | 8001
> prune_on_boot        | no
> DB update fail with wrong sql
> [Jun 30 15:31:41] WARNING[74152]: res_config_odbc.c:540 update_odbc: Key field 'id' does not exist in table 'ps_contacts at ics-postgresql'.  Update will fail
> [Jun 30 15:31:41] WARNING[74152]: res_odbc.c:538 ast_odbc_print_errors: SQL Execute returned an error: 42601: ERROR: syntax error at or near "WHERE";
> Error while preparing parameters
> [Jun 30 15:31:41] WARNING[74152]: res_odbc.c:433 ast_odbc_prepare_and_execute: SQL Execute error -1!
> [Jun 30 15:31:41] ERROR[74152]: res_pjsip_registrar.c:842 register_aor_core: Failed to update contact 'sip:8001 at 94.158.154.84:64679;ob;x-ast-orig-host=192.168.244.252:64679' expiration time to 300 seconds.
> [Jun 30 15:31:41]     -- Removed contact 'sip:8001 at 94.158.154.84:64679;ob;x-ast-orig-host=192.168.244.252:64679' from AOR '8001' due to registration failure
> [Jun 30 15:31:41]   == Contact 8001/sip:8001 at 94.158.154.84:64679;ob;x-ast-orig-host=192.168.244.252:64679 has been deleted
> [Jun 30 15:31:41]   == Endpoint 8001 is now Unreachable
> Log of postgresql
> 2021-06-30 15:31:41.029 EEST,"ics","ics",70000,"127.0.0.1:56260",60dc3138.11170,3008,"PARSE",2021-06-30 11:54:16 EEST,11/6988,0,ERROR,42601,"syntax error at or near ""WHERE""",,,,,,"UPDATE ps_contacts SET WHERE id=$1",25,,""
> so looks like update sql request is missed db fields



--
This message was sent by Atlassian JIRA
(v6.2#6252)