[asterisk-users] problem with inserting records into cdr

[David Backeberg]

On Thu, Jun 3, 2010 at 3:56 PM,  <covici at ccs.covici.com> wrote:
> Hi.  For several months now asterisk will mysteriously stop inserting
> records into cdr database.  I am using mysql and the asterisk addons
> 1.6.2 to accomplish this.  Sometimes there is a strange error about
> column names, but often there is no error, it just stops.  I just have
> to restart asterisk to get things going again, so I am stumped as to
> what is happening, or even how to troubleshoot.  I usually run in
> verbosity 4, but am not seeing anything of interest.

So... I've seen this before, and I can't decide how exactly the
'problem' should be attacked.

In my case, I was trying to centralize cdrs from multiple systems,
down to a single cohesive cdr repository. This would give me a nice
single log point I could dig through. Especially helpful in situations
where you use multiple asterisks for load balancing, but then want a
cohesive set of logs.

Anyway, in my case, I knew for a fact that my mysql db target was
sometimes flaky. Sometimes the underlying i/o layer would freak out.
Or there would be a network hiccup.

Here's my assessment of how cdr_mysql is behaving...

* asterisk sent a db insert to the mysql database for a particular cdr
* but never got that insert acknowledged
* there is no hard limit on when that insert should time out, so
asterisk blocks on this cdr write attempt, which will now never
complete because of some problem outside the control of asterisk
* then there's a traffic jam of cdrs that never even attempt because
the earlier cdr did not complete, and it seems like there's a design
decision that cdrs should write serially, one after the other

What would I like to see as the solution?

That's an excellent question. The obvious fixes are:
* don't have a flaky i/o subsystem on your db box
* don't have a network hiccup

Barring that, it would be nice if we could set an abort timer on the
cdr_mysql insert attempts. This should be user tunable. For all of my
systems, much less than one second should be the return time, and I'd
want an abort after, say, 10 seconds.

I'd want some kind of logging trap thrown, and the error logged
somewhere in /var/log/asterisk or syslog, etc.

What do people think?

I've known about this issue for years, but I've never seen a
discussion on this. Perhaps this has come up before in Mantis.