[asterisk-bugs] [JIRA] (ASTERISK-25281) PJSIP, ODBC and Oracle - case sensitive field name checks in sorcery break Oracle compatibility

HZMI8gkCvPpom0tM (JIRA) noreply at issues.asterisk.org
Wed Aug 5 08:43:32 CDT 2015


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

HZMI8gkCvPpom0tM edited comment on ASTERISK-25281 at 8/5/15 8:43 AM:
---------------------------------------------------------------------

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 at 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'='77.61.215.187' (1/177)
[2015-08-05 15:33:44.890] DEBUG[14327]: res_config_odbc.c:126 custom_prepare: Skipping field 'fullcontact'='sip:99900000001 at 77.61.215.187: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}


was (Author: y2fbo4ievym5ve9u):
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
{code}

> PJSIP, ODBC and Oracle - case sensitive field name checks in sorcery break Oracle compatibility
> -----------------------------------------------------------------------------------------------
>
>                 Key: ASTERISK-25281
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-25281
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Channels/chan_pjsip, Core/Sorcery, Resources/res_odbc
>    Affects Versions: 13.4.0
>         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+Realtime
>            Reporter: HZMI8gkCvPpom0tM
>            Assignee: Unassigned
>
> 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}



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



More information about the asterisk-bugs mailing list