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

[Joshua C. Colp (JIRA)]

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

Joshua C. Colp updated ASTERISK-29506:
--------------------------------------

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

The reason why the SQL is not correct is because it's not able to find any of the columns. That's the core issue here, and so far is not being experienced by anyone else.

Can you provide the actual database definition for the table? Are there any errors at startup about being able to query database columns? Is this a local Postgresql instance?

> 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)