[Home]

Summary:ASTERISK-25281: PJSIP, ODBC and Oracle - case sensitive field name checks in sorcery break Oracle compatibility
Reporter:HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u)Labels:
Date Opened:2015-07-28 03:56:38Date Closed:
Priority:MajorRegression?
Status:Open/NewComponents:Channels/chan_pjsip Channels/chan_sip/General Core/Sorcery Resources/res_odbc
Versions:13.4.0 13.5.0 13.6.0 13.7.0 Frequency of
Occurrence
Constant
Related
Issues:
is related toASTERISK-26106 sorcery: PJSIP driver completely broken with oracle using ODBC
Environment:any debian x64 with odbc configured. all configrations was done in accordance with document https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+RealtimeAttachments:
Description:Look like that pjsip funtionality completely broken in case of obdc driver used.
After configuration like discribed in https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+Realtime

after loading asterisk
{code}
vpbxdev*CLI> odbc show

ODBC DSN Settings
-----------------

 Name:   VPBXORACLE
 DSN:    sqlrelay
   Last connection attempt: 1970-01-01 01:00:00
 Pooled: No
 Connected: Yes
{code}

{code}
*vpbxdev*CLI> pjsip show endpoints
No objects found.*
{code}

{noformat}
[2015-07-28 10:52:51.740] DEBUG[11310]: res_config_odbc.c:113 custom_prepare: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE id LIKE ? ORDER BY id
[2015-07-28 10:52:51.740] DEBUG[11310]: res_config_odbc.c:129 custom_prepare: Parameter 1 ('id LIKE') = '%'
[2015-07-28 10:52:51.754] DEBUG[11310]: res_odbc.c:1057 odbc_release_obj2: odbc_release_obj2(0x1cda0c8) called (obj->txf = (nil))
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'ID' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'TRANSPORT' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'AORS' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'AUTH' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'CONTEXT' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'DISALLOW' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'ALLOW' from retrieval
[2015-07-28 10:52:51.755] DEBUG[11310]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'DIRECT_MEDIA' from retrieval
{noformat}

If i do manually same query using isql (odbc) i get data . So all created ok inside tables.


SQL>  SELECT * FROM ps_endpoints WHERE id LIKE '%' ORDER BY id
.....
9990..... transport-udp| 999.....| 999000.....| customers| all     | alaw | no          |  
....
SQLRowCount returns 0
1 rows fetched


contents of file sorcery.conf
{code}
[res_pjsip]
endpoint=realtime,ps_endpoints
auth=realtime,ps_auths
aor=realtime,ps_aors
domain_alias=realtime,ps_domain_aliases
contact=realtime,ps_contacts

[res_pjsip_endpoint_identifier_ip]
identify=realtime,ps_endpoint_id_ips
{code}
contents of extconfig.conf

{code}
[settings]
ps_endpoints => odbc,VPBXORACLE
ps_auths => odbc,VPBXORACLE
ps_aors => odbc,VPBXORACLE
ps_domain_aliases => odbc,VPBXORACLE
ps_endpoint_id_ips => odbc,VPBXORACLE
ps_contacts => odbc,VPBXORACLE
{code}
Comments:By: Asterisk Team (asteriskteam) 2015-07-28 03:56:40.721-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.

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].

By: Joshua C. Colp (jcolp) 2015-07-28 10:48:51.454-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

What database is in use? Oracle? What does the table layout show? Does normal pjsip.conf configuration work?

As well - I know of a few deployments using ODBC and I helped someone last week set it up. It doesn't seem to be broken for everyone so this may be isolated to your environment (for example - Oracle).

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-28 11:59:49.008-0500

Thanks for very helpful post.
Database oracle.
This is tables struct like you want
{noformat}
SQL>  SELECT * FROM ps_endpoints WHERE id LIKE '%' ORDER BY id
+------------+--------------+------------+------------+----------+---------+------+-------------+----------------------+--------------------+------------------------------+----------------------------+----------+-----------------------+------------+------------+------------+----------+------------+--------------+---------------+----------------+---------+--------------+---------------+---------+----------+--------------+-------+--------------------+---------+-----------------+-------------+-------+--------------+-----------------+------------------+----------+---------+-----------------+----------------+-----------+-------------+-----------------+-------------------+---------------------+-----------+----------+-------------+----------------------+--------------+---------------+----------+---------+--------------------+------------------+-------------------+-----------+---------------+----------------+----------+------------+----------+----------+---------------+------------+----------+--------------+------------+-----------+---------------+-----------------+------------+-------------+-------------+-----------+------------+--------------+----------------+--------+----------+----------+----------------+----------+-----------------------------+------------+--------------+----------------+----------------------------+---------------+------------------+--------------+
| ID         | TRANSPORT    | AORS       | AUTH       | CONTEXT  | DISALLOW| ALLOW| DIRECT_MEDIA| CONNECTED_LINE_METHOD| DIRECT_MEDIA_METHOD| DIRECT_MEDIA_GLARE_MITIGATION| DISABLE_DIRECT_MEDIA_ON_NAT| DTMF_MODE| EXTERNAL_MEDIA_ADDRESS| FORCE_RPORT| ICE_SUPPORT| IDENTIFY_BY| MAILBOXES| MOH_SUGGEST| OUTBOUND_AUTH| OUTBOUND_PROXY| REWRITE_CONTACT| RTP_IPV6| RTP_SYMMETRIC| SEND_DIVERSION| SEND_PAI| SEND_RPID| TIMERS_MIN_SE| TIMERS| TIMERS_SESS_EXPIRES| CALLERID| CALLERID_PRIVACY| CALLERID_TAG| 100rel| AGGREGATE_MWI| TRUST_ID_INBOUND| TRUST_ID_OUTBOUND| USE_PTIME| USE_AVPF| MEDIA_ENCRYPTION| INBAND_PROGRESS| CALL_GROUP| PICKUP_GROUP| NAMED_CALL_GROUP| NAMED_PICKUP_GROUP| DEVICE_STATE_BUSY_AT| FAX_DETECT| T38_UDPTL| T38_UDPTL_EC| T38_UDPTL_MAXDATAGRAM| T38_UDPTL_NAT| T38_UDPTL_IPV6| TONE_ZONE| LANGUAGE| ONE_TOUCH_RECORDING| RECORD_ON_FEATURE| RECORD_OFF_FEATURE| RTP_ENGINE| ALLOW_TRANSFER| ALLOW_SUBSCRIBE| SDP_OWNER| SDP_SESSION| TOS_AUDIO| TOS_VIDEO| SUB_MIN_EXPIRY| FROM_DOMAIN| FROM_USER| MWI_FROM_USER| DTLS_VERIFY| DTLS_REKEY| DTLS_CERT_FILE| DTLS_PRIVATE_KEY| DTLS_CIPHER| DTLS_CA_FILE| DTLS_CA_PATH| DTLS_SETUP| SRTP_TAG_32| MEDIA_ADDRESS| REDIRECT_METHOD| SET_VAR| COS_AUDIO| COS_VIDEO| MESSAGE_CONTEXT| FORCE_AVP| MEDIA_USE_RECEIVED_TRANSPORT| ACCOUNTCODE| USER_EQ_PHONE| MOH_PASSTHROUGH| MEDIA_ENCRYPTION_OPTIMISTIC| RPID_IMMEDIATE| G726_NON_STANDARD| RTP_KEEPALIVE|
+------------+--------------+------------+------------+----------+---------+------+-------------+----------------------+--------------------+------------------------------+----------------------------+----------+-----------------------+------------+------------+------------+----------+------------+--------------+---------------+----------------+---------+--------------+---------------+---------+----------+--------------+-------+--------------------+---------+-----------------+-------------+-------+--------------+-----------------+------------------+----------+---------+-----------------+----------------+-----------+-------------+-----------------+-------------------+---------------------+-----------+----------+-------------+----------------------+--------------+---------------+----------+---------+--------------------+------------------+-------------------+-----------+---------------+----------------+----------+------------+----------+----------+---------------+------------+----------+--------------+------------+-----------+---------------+-----------------+------------+-------------+-------------+-----------+------------+--------------+----------------+--------+----------+----------+----------------+----------+-----------------------------+------------+--------------+----------------+----------------------------+---------------+------------------+--------------+
| 99900000001| transport-udp| 99900000001| 99900000001| customers| all     | alaw | no          |                      |                    |                              |                            |          |                       |            |            |            |          |            |              |               |                |         |              |               |         |          |              |       |                    |         |                 |             |       |              |                 |                  |          |         |                 |                |           |             |                 |                   |                     |           |          |             |                      |              |               |          |         |                    |                  |                   |           |               |                |          |            |          |          |               |            |          |              |            |           |               |                 |            |             |             |           |            |              |                |        |          |          |                |          |                             |            |              |                |                            |               |                  |              |
+------------+--------------+------------+------------+----------+---------+------+-------------+----------------------+--------------------+------------------------------+----------------------------+----------+-----------------------+------------+------------+------------+----------+------------+--------------+---------------+----------------+---------+--------------+---------------+---------+----------+--------------+-------+--------------------+---------+-----------------+-------------+-------+--------------+-----------------+------------------+----------+---------+-----------------+----------------+-----------+-------------+-----------------+-------------------+---------------------+-----------+----------+-------------+----------------------+--------------+---------------+----------+---------+--------------------+------------------+-------------------+-----------+---------------+----------------+----------+------------+----------+----------+---------------+------------+----------+--------------+------------+-----------+---------------+-----------------+------------+-------------+-------------+-----------+------------+--------------+----------------+--------+----------+----------+----------------+----------+-----------------------------+------------+--------------+----------------+----------------------------+---------------+------------------+--------------+
SQLRowCount returns 0
1 rows fetched
SQL> exit
{noformat}
If i configure to use static config in pjsip.conf everything works fine. As i told it is not working only over ODBC. If you help also to me i ll be really happy.


By: Rusty Newton (rnewton) 2015-07-30 10:22:12.909-0500

I have a co-worker who did some testing, following the same instructions that you linked (but using MySQL instead) and everything appears to work fine.

Can you test with MySQL as well so we can narrow down the issue to only occurring with Oracle?

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-08-03 04:30:15.146-0500

I recreated the issue on mysql as well. Look like that somewhere in sources exist mistake which comparing table names with strcmp functions  instead of strcasecmp. As a theory in Oracle issue appears because all tables and columns names in upper case .
I recreated issue with simple renaming of column in mysql from 'auth' to 'AUTH' and it immediately stopped to work.
I think good candidate for one of such conditions is
line  
if (!strcmp(field->name, UUID_FIELD)) {
in sorcery_realtime_filter_objectset ( res_sorcery_realtime.c )



By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-08-05 08:39:38.747-0500

Chan_sip also broken. Well done. Asterisk completely cannot work with oracle. As for me issue can be promoted to Critical.

{code}
[2015-08-05 15:33:44.605] DEBUG[14327]: res_config_odbc.c:113 custom_prepare: Skip: 0; SQL: SELECT * FROM sippeers WHERE name = ? AND host = ?
[2015-08-05 15:33:44.605] DEBUG[14327]: res_config_odbc.c:129 custom_prepare: Parameter 1 ('name') = '99900000001'
[2015-08-05 15:33:44.605] DEBUG[14327]: res_config_odbc.c:129 custom_prepare: Parameter 2 ('host') = 'dynamic'
[2015-08-05 15:33:44.614] DEBUG[14327]: res_odbc.c:1057 odbc_release_obj2: odbc_release_obj2(0x30b75d8) called (obj->txf = (nil))
[2015-08-05 15:33:44.614] DEBUG[14327]: chan_sip.c:30430 build_peer: -REALTIME- peer built. Name: 99900000001. Peer objects: 1
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:31035 build_peer: Not an IPv4 nor IPv6 address, cannot get port.
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:31038 build_peer: Not an IPv4 nor IPv6 address, cannot set port.
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:31040 build_peer: Not an IPv4 nor IPv6 address, cannot get port.
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:31043 build_peer: Not an IPv4 nor IPv6 address, cannot set port.
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:31060 build_peer: Bah, we're expired (1438781624/0/1438781624)!
[2015-08-05 15:33:44.615] DEBUG[14327]: db.c:376 db_get_common: Unable to find key '99900000001' in family 'SIP/Registry'
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:5453 realtime_peer: -REALTIME- loading peer from database to memory. Name: 99900000001. Peer objects: 1
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:3695 __sip_xmit: Trying to put 'SIP/2.0 401' onto UDP socket destined for X.X.X.X:1041
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:5029 sip_destroy_peer: Destroying SIP peer 99900000001
[2015-08-05 15:33:44.615] DEBUG[14327]: chan_sip.c:5067 sip_destroy_peer: -REALTIME- peer Destroyed. Name: 99900000001. Realtime Peer objects: 0
[2015-08-05 15:33:44.615] DEBUG[14358]: taskprocessor.c:484 tps_taskprocessor_destroy: destroying taskprocessor 'e890c669-2b44-4154-9b9a-85c035464d13'
[2015-08-05 15:33:44.616] DEBUG[14327]: res_config_odbc.c:113 custom_prepare: Skip: 0; SQL: SELECT * FROM sippeers WHERE name = ? AND host = ?
[2015-08-05 15:33:44.616] DEBUG[14327]: res_config_odbc.c:129 custom_prepare: Parameter 1 ('name') = '99900000001'
[2015-08-05 15:33:44.616] DEBUG[14327]: res_config_odbc.c:129 custom_prepare: Parameter 2 ('host') = 'dynamic'
[2015-08-05 15:33:44.625] DEBUG[14327]: res_odbc.c:1057 odbc_release_obj2: odbc_release_obj2(0x30b75d8) called (obj->txf = (nil))
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:30430 build_peer: -REALTIME- peer built. Name: 99900000001. Peer objects: 1
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:31035 build_peer: Not an IPv4 nor IPv6 address, cannot get port.
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:31038 build_peer: Not an IPv4 nor IPv6 address, cannot set port.
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:31040 build_peer: Not an IPv4 nor IPv6 address, cannot get port.
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:31043 build_peer: Not an IPv4 nor IPv6 address, cannot set port.
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:31060 build_peer: Bah, we're expired (1438781624/0/1438781624)!
[2015-08-05 15:33:44.625] DEBUG[14327]: db.c:376 db_get_common: Unable to find key '99900000001' in family 'SIP/Registry'
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:5453 realtime_peer: -REALTIME- loading peer from database to memory. Name: 99900000001. Peer objects: 1
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:5029 sip_destroy_peer: Destroying SIP peer 99900000001
[2015-08-05 15:33:44.625] DEBUG[14327]: chan_sip.c:5067 sip_destroy_peer: -REALTIME- peer Destroyed. Name: 99900000001. Realtime Peer objects: 0
....
.....
[2015-08-05 15:33:44.889] DEBUG[14327]: chan_sip.c:5453 realtime_peer: -REALTIME- loading peer from database to memory. Name: 99900000001. Peer objects: 1
[2015-08-05 15:33:44.889] DEBUG[14327]: chan_sip.c:16278 parse_register_contact: Store REGISTER's Contact header for call routing.
[2015-08-05 15:33:44.889] DEBUG[14327]: chan_sip.c:16464 build_path: build_path: do not use Path headers
   -- Registered SIP '99900000001' at X.X.X.X:1107
[2015-08-05 15:33:44.890] WARNING[14327]: res_config_odbc.c:509 update_odbc: Key field 'name' does not exist in table 'sippeers@VPBXORACLE'.  Update will fail
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:113 custom_prepare: Skip: 127; SQL: UPDATE sippeers SET  WHERE name=?
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'ipaddr'='X.X.X.X' (1/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'fullcontact'='sip:99900000001@X.X.X.X:1107;line=cxftszxp' (2/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'lastms'='0' (4/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'useragent'='snom320/7.3.30' (10/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'defaultuser'='99900000001' (20/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'regseconds'='1438785224' (40/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'port'='1107' (100/177)
[2015-08-05 15:33:44.893] WARNING[14327]: res_odbc.c:649 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HY000: ORA-01747: invalid user.table.column, table.column, or column specification (75)
{code}

By: Joshua C. Colp (jcolp) 2015-08-05 08:46:24.929-0500

chan_sip does not use the new sorcery infrastructure, that would be a separate issue.

By: Walter Doekes (wdoekes) 2016-06-15 15:56:21.938-0500

Looks like a dupe of ASTERISK-16577 ?

Sounds like you create columns in lowercase but oracle represents them in uppercase.

Isn't the fix to create them in fixed-case? In MySQL that would be using double quotes. In MS-SQL with square brackets.

Looks like oracle uses double quotes too, according to this:
https://community.oracle.com/message/4553754#4553754

By: Walter Doekes (wdoekes) 2016-06-15 15:58:51.337-0500

Okay, perhaps not.

{quote}
Of course, once a table or a column is created with a case-sensitive name, it can be hard to break free, as you're discovering--you can't just alter the table and rename the column, as your existing code will break. (transactionid is equal to "TRANSACTIONID", but not "TransactionID".) So if you're not willing to go through your entire codebase to fix the case-sensitivity (and most of us don't have time to), you're going to have to get used to putting quotes on the things that need it.
{quote}

That would become a problem.

By: Abhay Gupta (agupta) 2020-02-18 02:42:25.157-0600

I have seen that in ODBC driver where ever asterisk try to use SQLDescribeCol() the column names are returned in UPPER case . So by changing the same to lower case it works with all Databases . I can submit the patch if desired and it make sense to have column names only in lower case for comparison .

In case you create table using double quotes then query has to be run with double quotes so it is better just to change the change the case with SQLDescribeCol()