asterisk users - Jan 2014 - Does cdr adaptive odbc re-connect automatically after a long idle time?

Hi all,

I use astersk 11.7.0 on Ubuntu 12.04.01 TLS (i386). I use cdr_adaptive_odbc
to write CDR to my MySQL's cdr table.
After my testing, this scenario is working well.

After a long idle time, I didn't make any call to the asterisk server.
When I try to make a call again after 8 hours, I found that the cdr lost.
It cannot be inserted to cdr table.
Also, I could not find the insert CDR messages in the CLI at this period.

Could you please tell me which settings are wrong? Why dose my odbc
connection not re-connect to MySQL automatically?


I checked the setting below:

CLI:
ubuntu*CLI> cdr show status

Call Detail Record (CDR) settings
----------------------------------
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       Yes
  Log congestion:             Yes

* Registered Backends
  -------------------
    cdr-custom
    Adaptive ODBC
    csv

ubuntu*CLI> odbc show all

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

  Name:   asterisk
  DSN:    asterisk-connector
    Last connection attempt: 2014-01-11 18:16:40
  Pooled: Yes
  Limit:  1000
  Connections in use: 0


-------------- /etc/asterisk/cdr.conf lists below:
[general]
enable=yes
unanswered = yes
congestion = yes
endbeforehexten=yes

[csv]
usegmtime=no    ; log date/time in GMT.  Default is "no"
loguniqueid=yes  ; log uniqueid.  Default is "no"
loguserfield=yes ; log user field.  Default is "no"
accountlogs=yes  ; create separate log file for each account code. Default
is "yes"

-------------- /etc/odbc.ini
[asterisk-connector]
Description           = MySQL connection to 'asterisk' database
Driver                = MySQL
Database              = mydatabase
Server                = localhost
UserName              = root
Password              = mypassword
Port                  = 3306
Socket                = /var/run/mysqld/mysqld.sock


-------------- /etc/asterisk/res_odbc.conf lists below:
[ENV]

[asterisk]
enabled => yes
dsn => asterisk-connector
password => mypassword
pre-connect => yes
sanitysql => select 1
pooling => yes
idlecheck => 30
share_connections => yes
limit => 1000
connect_timeout => 60
negative_connection_cache => 600


-------------- /etc/asterisk/cdr_adaptive_odbc.conf lists below:
[cdr]
connection=asterisk
table=cdr
alias start => calldate
alias phoneno => phoneno
alias userid => userid
alias callerid => callerid


-- 
Best Regards
Charles
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20140112/e88d8638/attachment.html>

On Sat, Jan 11, 2014 at 4:56 PM, Charles Wang <lazy.charles at gmail.com>
wrote:
> Hi all,
>
> I use astersk 11.7.0 on Ubuntu 12.04.01 TLS (i386). I use cdr_adaptive_odbc
> to write CDR to my MySQL's cdr table.
> After my testing, this scenario is working well.
>
> After a long idle time, I didn't make any call to the asterisk server.
> When I try to make a call again after 8 hours, I found that the cdr lost.
It
> cannot be inserted to cdr table.
> Also, I could not find the insert CDR messages in the CLI at this period.
>
> Could you please tell me which settings are wrong? Why dose my odbc
> connection not re-connect to MySQL automatically?
>
>
> I checked the setting below:
>
> CLI:
> ubuntu*CLI> cdr show status
>
> Call Detail Record (CDR) settings
> ----------------------------------
>   Logging:                    Enabled
>   Mode:                       Simple
>   Log unanswered calls:       Yes
>   Log congestion:             Yes
>
> * Registered Backends
>   -------------------
>     cdr-custom
>     Adaptive ODBC
>     csv
>
> ubuntu*CLI> odbc show all
>
> ODBC DSN Settings
> -----------------
>
>   Name:   asterisk
>   DSN:    asterisk-connector
>     Last connection attempt: 2014-01-11 18:16:40
>   Pooled: Yes
>   Limit:  1000
>   Connections in use: 0
>
>
> -------------- /etc/asterisk/cdr.conf lists below:
> [general]
> enable=yes
> unanswered = yes
> congestion = yes
> endbeforehexten=yes
>
> [csv]
> usegmtime=no    ; log date/time in GMT.  Default is "no"
> loguniqueid=yes  ; log uniqueid.  Default is "no"
> loguserfield=yes ; log user field.  Default is "no"
> accountlogs=yes  ; create separate log file for each account code. Default
> is "yes"
>
> -------------- /etc/odbc.ini
> [asterisk-connector]
> Description           = MySQL connection to 'asterisk' database
> Driver                = MySQL
> Database              = mydatabase
> Server                = localhost
> UserName              = root
> Password              = mypassword
> Port                  = 3306
> Socket                = /var/run/mysqld/mysqld.sock
>
>
> -------------- /etc/asterisk/res_odbc.conf lists below:
> [ENV]
>
> [asterisk]
> enabled => yes
> dsn => asterisk-connector
> password => mypassword
> pre-connect => yes
> sanitysql => select 1
> pooling => yes
> idlecheck => 30
> share_connections => yes
> limit => 1000
> connect_timeout => 60
> negative_connection_cache => 600
>
>
> -------------- /etc/asterisk/cdr_adaptive_odbc.conf lists below:
> [cdr]
> connection=asterisk
> table=cdr
> alias start => calldate
> alias phoneno => phoneno
> alias userid => userid
> alias callerid => callerid
>
I would be inclined to check the database side over asterisk. We use
almost the same setup and don't have any issues. We go some time 12
hours between calls.  Once thing you could do is enable debug logs and
see what Asterisk is doing when the odbc connection is down.  EG: it
should be attempting to reconnect.

-- 
Paul Belanger | PolyBeacon, Inc.
Jabber: paul.belanger at polybeacon.com | IRC: pabelanger (Freenode)
Github: https://github.com/pabelanger | Twitter: https://twitter.com/pabelanger