Issue with ODBC and RealTime asterisk


#1

I don’t understand what’s going on here. I’m trying to do Asterisk Real Time configuration. So, I have CentOS + Asterisk 14.6 installed.

This is my res_odbc.conf (passwords are hidden for security reasons)

[asterisk]
enabled		=> yes
dsn		        => asterisk
username	=> asterisk
password	        => xxxxxxx
pre-connect	=> yes
port	       	        => 3310
max_connections	=> 100

This is my res_mysql.conf

[general]
dbhost        = localhost
dbname      = asterisk_test
dbuser        = asterisk
dbpass        = xxxxxxxx
dbport          = 3310
dbsock         = /var/lib/mysql/mysql.sock
requirements=warn

This is func_odbc.conf

[LANG]
dsn=asterisk
readsql=SELECT language FROM sipfriends WHERE name='${SQL_ESC(${ARG1})}'

Here is my test extensions.conf

[general]
static=yes
writeprotect=no
autofallthrough=no
clearglobalvars=no
priorityjumping=no

[lookupdundi]
switch => DUNDi/priv

[internal]

exten => 119,1,NoOp()
exten => 119,2,WaitExten(3)
exten => 119,3,GotoIf($["${ODBC_LANG}"="en"]?Test_en,1:Test_br,1)


exten => Test_en,1,NoOp()
exten => Test_en,2,Playback(hello-world)
exten => Test_en,3,Hangup()

exten => Test_br,1,NoOp()
exten => Test_br,2,Playback(hello-world)
exten => Test_br,3,Hangup()


include => dundiextens

exten => _[a-z].,1,Set(SAFE_EXTEN=${FILTER(0-9,${EXTEN})})
same => n,Voicemail(${SAFE_EXTEN}@VoiceMail)
same => n,Playback(Goodbye)
same => n,Hangup

include => lookupdundi

switch => Realtime

[incomingdundi]

exten => _[a-z].,1,Set(SAFE_EXTEN=${FILTER(0-9,${EXTEN})})
same => n,Voicemail(${SAFE_EXTEN}@VoiceMail)
same => n,Playback(Goodbye)
same => n,Hangup

This is my extconfig.conf

[settings]
sipusers => mysql,general,sipfriends
sippeers => mysql,general,sipfriends
voicemail => mysql,general,voicemail

ODBC is connected when I check it in asterisk CLI. mysql show status also connected. When I call when dialplan reach the ODBC LANG function e.g. querying database the whole asterisk got restarted,

This is from CLI

*CLI>
== Using SIP RTP CoS mark 5
– Executing [119@internal:1] NoOp(“SIP/1001-00000000”, “”) in new stack
– Executing [119@internal:2] WaitExten(“SIP/1001-00000000”, “3”) in new stack
– Timeout on SIP/1001-00000000, continuing…
– Executing [119@internal:3] GotoIf(“SIP/1001-00000000”, “0?Test_en,1:Test_br,1”) in new stack
– Goto (internal,Test_br,1)
– Executing [Test_br@internal:1] NoOp(“SIP/1001-00000000”, “”) in new stack
*CLI>
Disconnected from Asterisk server
Asterisk cleanly ending (0).
Executing last minute cleanups
Asterisk ending (0).

The full log is really big one but this is the beginning of it and some suspicious parts

[Nov 30 15:18:21] DEBUG[39935] chan_sip.c: Allocating new SIP dialog for uht4h83mzoz84u023p6suph6o8oo95sp@xxx.yyy.zzz.aaa - INVITE (No RTP)
[Nov 30 15:18:21] DEBUG[39935][C-00000001] chan_sip.c: **** Received INVITE (5) - Command in SIP INVITE
[Nov 30 15:18:21] DEBUG[39935][C-00000001] res_config_mysql.c: MySQL RealTime: Connection okay.
[Nov 30 15:18:21] DEBUG[39935][C-00000001] res_config_mysql.c: MySQL RealTime: Retrieve SQL: SELECT * FROM sipfriends WHERE name = ‘1002’ AND host = ‘dynamic’
[Nov 30 15:18:21] DEBUG[39935][C-00000001] chan_sip.c: Not an IPv4 nor IPv6 address, cannot get port.
[Nov 30 15:18:21] DEBUG[39935][C-00000001] chan_sip.c: Bah, we’re expired (1512047901/0/1512047901)!
[Nov 30 15:18:21] DEBUG[39935][C-00000001] db.c: Unable to find key ‘1002’ in family ‘SIP/Registry’
[Nov 30 15:18:21] DEBUG[39935][C-00000001] chan_sip.c: -REALTIME- loading peer from database to memory. Name: 1002. Peer objects: 0
[Nov 30 15:18:21] DEBUG[40633][C-00000001] pbx.c: Launching ‘NoOp’
[Nov 30 15:18:21] DEBUG[40633][C-00000001] pbx.c: Launching ‘WaitExten’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Result of ‘ODBC_LANG’ is NULL
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Expression result is ‘0’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx.c: Launching ‘GotoIf’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx.c: Launching ‘NoOp’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Function CALLERID(num) result is ‘1002’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Evaluating ‘SQL_ESC(${ARG1})’ (from ‘SQL_ESC(${ARG1})}’’ len 16)
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Evaluating ‘ARG1’ (from ‘ARG1})’ len 4)
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Result of ‘ARG1’ is ‘1002’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] pbx_variables.c: Function SQL_ESC(1002) result is ‘1002’
[Nov 30 15:18:24] DEBUG[40633][C-00000001] res_odbc.c: Reusing ODBC handle 0x2483b88 from class ‘asterisk’
[Nov 30 15:18:29] Asterisk 14.6.1 built by root @ mq65 on a x86_64 running Linux on 2017-11-28 13:20:32 UTC

As I can see the INVITE is sent, also the query is made and the CALLERID number is correctly returned but the function ODBC_LANG isn’t executed and return NULL. I’m sure that this:
exten => 119,3,GotoIf($["${ODBC_LANG}"=“en”]?Test_en,1:Test_br,1) has EN in database.

Also another interesting part is

[root@mq65 asterisk]# echo "select 1" | isql -v asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
**Segmentation fault (core dumped)**

I’ve tried everything what I’ve found on the internet. I have no idea what can be the problem.

Sorry for the long post but I wanted to be descriptive as much as possible in order to get some help.


#2

I have added trace to odbcinst.ini and dialed again.

[ODBC]
Trace=yes
TraceFile=/tmp/unixodbc.log

This is what I’ve received:

[ODBC][23539][1512397063.114214][SQLGetConnectAttr.c][292]
		Entry:
			Connection = 0x2238450
			Attribute = 1209
			Value = 0x7f94cb9a5b60
			Buffer Length = 0
			StrLen = (nil)
[ODBC][23539][1512397063.114429][SQLGetConnectAttr.c][758]
		Exit:[SQL_SUCCESS]
[ODBC][23539][1512397063.114495][SQLAllocHandle.c][540]
		Entry:
			Handle Type = 3
			Input Handle = 0x2238450
[ODBC][23539][1512397063.114594][SQLAllocHandle.c][1081]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x7f94a0001230
[ODBC][23958][1512397067.528673][__handles.c][460]
		Exit:[SQL_SUCCESS]
			Environment = 0x17d3940
[ODBC][23958][1512397067.528719][SQLSetEnvAttr.c][189]
		Entry:
			Environment = 0x17d3940
			Attribute = SQL_ATTR_ODBC_VERSION
			Value = 0x3
			StrLen = 0
[ODBC][23958][1512397067.528747][SQLSetEnvAttr.c][363]
		Exit:[SQL_SUCCESS]
[ODBC][23958][1512397067.528792][SQLAllocHandle.c][375]
		Entry:
			Handle Type = 2
			Input Handle = 0x17d3940
[ODBC][23958][1512397067.528816][SQLAllocHandle.c][493]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x17d4220
[ODBC][23958][1512397067.528838][SQLSetConnectAttr.c][396]
		Entry:
			Connection = 0x17d4220
			Attribute = SQL_ATTR_LOGIN_TIMEOUT
			Value = 0xa
			StrLen = 0
[ODBC][23958][1512397067.528857][SQLSetConnectAttr.c][681]
		Exit:[SQL_SUCCESS]
[ODBC][23958][1512397067.528867][SQLSetConnectAttr.c][396]
		Entry:
			Connection = 0x17d4220
			Attribute = SQL_ATTR_CONNECTION_TIMEOUT
			Value = 0xa
			StrLen = 0
[ODBC][23958][1512397067.528876][SQLSetConnectAttr.c][681]
		Exit:[SQL_SUCCESS]
[ODBC][23958][1512397067.528891][SQLConnect.c][3700]
		Entry:
			Connection = 0x17d4220
			Server Name = [asterisk][length = 8 (SQL_NTS)]
			User Name = [asterisk][length = 4 (SQL_NTS)]
			Authentication = [********][length = 8 (SQL_NTS)]
		UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][23958][1512397067.534109][SQLConnect.c][4273]
		Exit:[SQL_SUCCESS]

this is right after the asterisk is got restarted. I guess this is load of ODBC after asterisk restart…


#3

Hi Jasper, I had this issue with CentOS and unixODBC as well when I was using Percona’s MySQL on the same server. It turned out to be an issue with the libmysql libraries and the version of unixODBC that comes with CentOS. You can either try changing the symlink in your /usr/lib64/ as per (https://www.linuxquestions.org/questions/linux-server-73/mysql-connector-odbc-rhel7-incompatible-via-unixodbc-2-3-1-to-percona-5-6-a-4175573005/) or downloading the latest unixODBC and using that instead of the package version.

I’d recommend the latter as there are quite a few bugs fixed in the upstream version


#4

Hey bpbp,

Thanks. This was the problem. Exactly the same problem as on the link you provided. Problem seems to be fixed now.

Btw, I’ve tried to install latest unixODBC from source but seems like the issue wasn’t resolved. Maybe I didn’t removed correctly previous installation of unixODBC and they got messed who knows…