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

[Ivan Vynogradov (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-29506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=255615#comment-255615 ] 

Ivan Vynogradov commented on ASTERISK-29506:
--------------------------------------------

Hello. yes. it's local postgresql instance. No errors at the startup and insert to that table is success.
ics=# \d+ ps_contacts;
                                              Table "asterisk.ps_contacts"
        Column        |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
----------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id                   | character varying(255) |           | not null |         | extended |              | 
 uri                  | character varying(511) |           |          |         | extended |              | 
 expiration_time      | bigint                 |           |          |         | plain    |              | 
 qualify_frequency    | integer                |           |          |         | plain    |              | 
 outbound_proxy       | character varying(40)  |           |          |         | extended |              | 
 path                 | text                   |           |          |         | extended |              | 
 user_agent           | character varying(255) |           |          |         | extended |              | 
 qualify_timeout      | double precision       |           |          |         | plain    |              | 
 reg_server           | character varying(20)  |           |          |         | extended |              | 
 authenticate_qualify | yesno_values           |           |          |         | plain    |              | 
 via_addr             | character varying(40)  |           |          |         | extended |              | 
 via_port             | integer                |           |          |         | plain    |              | 
 call_id              | character varying(255) |           |          |         | extended |              | 
 endpoint             | character varying(40)  |           |          |         | extended |              | 
 prune_on_boot        | yesno_values           |           |          |         | plain    |              | 
Indexes:
    "ps_contacts_id_key" UNIQUE CONSTRAINT, btree (id)
    "ps_contacts_uq" UNIQUE CONSTRAINT, btree (id, reg_server)
    "ps_contacts_id" btree (id)
    "ps_contacts_qualifyfreq_exp" btree (qualify_frequency, expiration_time)
Access method: heap


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