[asterisk-bugs] [JIRA] (ASTERISK-30341) cdr_adaptive_odbc fails to write CDRs after database reload

[Asterisk Team (JIRA)]

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

Asterisk Team commented on ASTERISK-30341:
------------------------------------------

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. Please note that log messages and other files should not be sent to the Sangoma Asterisk Team unless explicitly asked for. All files should be placed on this issue in a sanitized fashion as needed.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].

Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur.

Please note that by submitting data, code, or documentation to Sangoma through JIRA, you accept the Terms of Use present at [https://www.asterisk.org/terms-of-use/|https://www.asterisk.org/terms-of-use/].

> cdr_adaptive_odbc fails to write CDRs after database reload
> -----------------------------------------------------------
>
>                 Key: ASTERISK-30341
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-30341
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: CDR/cdr_adaptive_odbc
>    Affects Versions: 18.15.1
>         Environment: Asterisk 18.15.1 on Unbuntu 18.04.1
> MySQL 8.0.31-0ubuntu0.20.04.2 on Ubuntu 20.04.2
> Both servers on same physical network and subnet.
>            Reporter: Gregory Massel
>            Severity: Major
>
> CDRs log perfectly to MySQL using cdr_adaptive_odbc up until the database shuts down and restarts.
> Occasionally Ubuntu will release an update version package for MySQL. When it does so, the database server automatically downloads the update, stops MySQL, installs the update and then restarts MySQL. When this happens, cdr_adaptive_odbc loses connection to the MySQL server and fails to ever reconnect.
> Asterisk logs countless messages like the following:
> [Dec  7 00:00:04] WARNING[28054] cdr_adaptive_odbc.c: SQL Prepare failed![INSERT INTO cdr_vpbx5 (start, clid, src, dst, dnid, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, peeraccount, linkedid, sequence) VALUES ({ ts '2022-12-06 23:59:45' }, '"Control" <0123456789>', '0123456789', '0123456789', '0123456789', 'swvpbx-internal', 'PJSIP/xxx202-0007b59d', 'PJSIP/yyy-0007b59e', 'Dial', 'PJSIP/yyy/sip:0123456789 at 10.1.2.3:5060,120,', 18, 0, 'NO ANSWER', 3, 'xxx', '1670363985.509608', 'xxx', '1670363985.509608', 717979)]
> [Dec  7 00:00:04] WARNING[28054] cdr_adaptive_odbc.c: SQL Execute returned an error -1: HY000: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.31-0ubuntu0.20.04.1] (59)
> I do NOT believe that the issue lies with the ODBC configuration, because func_odbc continues to work perfectly.
> my res_odbc.conf file looks like:
> ;;; odbc setup file
> [asterisk-cdr-connector]
> enabled => yes
> dsn => asterisk-cdr-connector
> username => asterisk
> password => redacted
> pre-connect => yes
> sanitysql => select 1
> ;idlecheck => 180
> ;share_connections => yes
> ;limit => 20
> max_connections => 20
> [clientzone-connector]
> enabled => yes
> dsn => clientzone-connector
> username => redacted
> password => redacted
> pre-connect => yes
> sanitysql => select 1
> isolation => repeatable_read
> ;idlecheck => 180
> ;share_connections => yes
> ;limit => 20
> max_connections => 20
> My cdr_adaptive_odbc.conf file looks like:
> [db]
> connection=asterisk-cdr-connector
> table=cdr
> Restarting Asterisk solves the issue. Issuing a "module reload res_odbc.so" and "module reload cdr_adaptive_odbc.so" also solves the issue.
> It is not just updates to MySQL that will cause this. Anything that causes the database to temporarily become inaccessible, e.g. a brief connectivity glitch, will also cause it.
> This also does not affect all Asterisk servers connected to the database; it would appear that it only affects those that make/receive a call and therefore attempt to write a CDR during the time that the database is briefly inaccessible.
> The correct functionality should be to attempt reconnection ~60 seconds later and then write the CDR, not to get caught in a frozen state.



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