[asterisk-bugs] [JIRA] (ASTERISK-28956) res_odbc: ODBC connection does not always reconnect

[Luke Escude (JIRA)]

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

Luke Escude commented on ASTERISK-28956:
----------------------------------------

Hello,

Well it appears this happens to the Voicemail ODBC connection as well... The only output I get is the following:

Jun 29 16:34:03] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:03] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:03] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:03] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:03] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:974 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fe9d8000c60 from class 'sql'
[Jun 29 16:34:04] DEBUG[20439]: res_odbc.c:817 ast_odbc_release_obj: Releasing ODBC handle 0x7fe9d8000c60 into pool


Could there be an error in how I have odbc.ini set up:

[sql]
Driver=MySQL
Driver64=MySQL
Server=SERVERADDR
UserName=USER
Password=PASSWORD
readtimeout=1
Database=DB

[vmsql]
Driver=MySQL
Driver64=MySQL
Server=SERVERADDR
UserName=USER
Password=PASSWORD
readtimeout=1
Database=DB

I think "readtimeout" helps with preventing dial plan from hanging if the SQL connection is down.

res_odbc.conf:

[sql]
enabled => yes
dsn => sql
username => USER
password => PASSWORD
pre-connect => yes
connect_timeout => 1

[vmodbc]
enabled => yes
dsn => vmsql
username => USER
password => PASSWORD
pre-connect => yes
connect_timeout => 1

Here I believe connect_timeout should allow the connection to continue re-attempting if it ever goes down.

> res_odbc: ODBC connection does not always reconnect
> ---------------------------------------------------
>
>                 Key: ASTERISK-28956
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-28956
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: CDR/cdr_adaptive_odbc
>    Affects Versions: 16.8.0
>         Environment: CentOS 7 x64
>            Reporter: Luke Escude
>            Assignee: Luke Escude
>
> So I am not too sure how to diagnose this one, but I've seen it happen too many times to not report it.
> Environment: Our Asterisk instances use an ODBC connection for CDR (adaptive_odbc) as well as an ODBC connection for Voicemail. The DSNs for each of these is separate, since they point to separate MySQL servers.
> Failure Scenario: A SQL server goes down for some X minutes. During those X minutes, all Asterisk instances that are supposed to connect to that particular one are aware of the issue, and odbc show will indicate as such. This is expected behavior, and happens for both the Voicemail ODBC and the CDR ODBC connection.
> Recovery Scenario: When a Voicemail SQL server comes back up, ALL Asterisk instances see it and reconnect. This is expected. However, when the CDR SQL server comes back up, only a majority of Asterisk instances reconnect. The remaining ones will say 1 out of 1 (meaning ODBC is connected) but will not log CDR at all until performing a module reload res_adaptive_odbc command. Some of them will still say 0 out of 1, indicating the connection is indeed dead, but they never attempt to reconnect.
> While the issue is happening, are there any other useful commands I can run to assist it diagnosing this? I don't want to have to restart asterisk since obviously that will fix it.



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