[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