[Home]

Summary:ASTERISK-29506: res_pjsip: Not able to update existing contact entry in realtime
Reporter:Ivan Vynogradov (ipvinner7)Labels:
Date Opened:2021-07-02 05:40:34Date Closed:2021-07-28 12:00:09
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Channels/chan_pjsip Resources/res_config_odbc
Versions:16.19.0 Frequency of
Occurrence		Constant
Related
Issues:
Environment:centos 7, Asterisk 16.19.0, 1 CPU, 2Gb RamAttachments:
Description: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@d8ecf55d261259430438629aa0c32576
uri                  | sip:8001@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@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@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@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@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

Comments:By: Asterisk Team (asteriskteam) 2021-07-02 05:40:38.329-0500

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. Please note that log messages and other files should not be sent to the Sangoma Asterisk Team unless explicitly asked for. All files should be placed on this issue in a sanitized fashion as needed.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].

Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur.

Please note that by submitting data, code, or documentation to Sangoma through JIRA, you accept the Terms of Use present at [https://www.asterisk.org/terms-of-use/|https://www.asterisk.org/terms-of-use/].
By: Joshua C. Colp (jcolp) 2021-07-02 05:48:46.972-0500

Thank you for taking the time to report this bug and helping to make Asterisk better. Unfortunately, we cannot work on this bug because your description did not include enough information. Please read over the Asterisk Issue Guidelines [1] which discusses the information necessary for your issue to be resolved and the format that information needs to be in. We would be grateful if you would then provide a more complete description of the problem. At a minimum, we need:

1. The specific steps or actions you took that caused you to encounter the problem.
2. The behavior you expected and the location of documentation that led you to that expectation.
3. The behavior you actually encountered.

To demonstrate the issue in detail, please include Asterisk log files generated per the instructions on the wiki [2]. If applicable, please ensure that protocol-level trace debugging is enabled, e.g., 'sip set debug on' if the issue involves chan_sip, and configuration information such as dialplan and channel configuration.

Thanks!

[1] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines

[2] https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information

Additionally we need the res_odbc.conf configuration file, as well as details about what the database usage is like. As well - are you using triggers or anything like that?
By: Ivan Vynogradov (ipvinner7) 2021-07-02 05:55:46.210-0500

Hello.
[ics-postgresql]
enabled=>yes
dsn=>PostgreSQL-icsdb
pre-connect=>yes
username=>ics
password=>pass
logging=>yes
max_connections=>100

No, I don't use trigger at ps* table
postgres (PostgreSQL) 12.6


By: Benjamin Keith Ford (bford) 2021-07-06 12:41:12.729-0500

Did this error start happening after an upgrade? If so, what version were you on previously?
By: Ivan Vynogradov (ipvinner7) 2021-07-07 03:59:36.857-0500

There was version 16.14. and problem was the same there. I've upgrade to 16.19. Asterisk installed from src.
By: Benjamin Keith Ford (bford) 2021-07-08 12:13:12.730-0500

There's a few things that could be going on in the code, but before trying to do a debug patch, let's just make sure ODBC is set up correctly with Asterisk.

What is the output of the following?
# {{odbcinst -q -d}}
# {{echo "select 1" | isql -v asterisk-connector}} (where {{asterisk-connector}} is whatever you have in odbc.ini)
By: Ivan Vynogradov (ipvinner7) 2021-07-09 05:18:56.264-0500

Hello.
output are:
odbcinst -q -d
[PostgreSQL]
[MySQL]

echo "select 1" | isql -v PostgreSQL-icsdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
+------------+
| ?column?   |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

By: Joshua C. Colp (jcolp) 2021-07-09 08:26:01.451-0500

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?
By: Ivan Vynogradov (ipvinner7) 2021-07-14 03:25:49.292-0500

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

By: George Joseph (gjoseph) 2021-07-14 08:10:17.444-0500

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}

By: Asterisk Team (asteriskteam) 2021-07-28 12:00:06.879-0500

Suspended due to lack of activity. This issue will be automatically re-opened if the reporter posts a comment. If you are not the reporter and would like this re-opened please create a new issue instead. If the new issue is related to this one a link will be created during the triage process. Further information on issue tracker usage can be found in the Asterisk Issue Guidlines [1].

[1] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines