Summary:ASTERISK-07086: [patch] record spooling support in cdr_addon_mysql.c
Reporter:Juan Pablo Abuyeres (jpabuyer)Labels:
Date Opened:2006-06-02 17:39:29Date Closed:2008-03-20 13:00:30
Versions:Frequency of
Environment:Attachments:( 0) cdr_addon_mysql.patch-3
( 1) cdr_mysql.conf.patch
( 2) cdr_mysql.conf.sample
Description:I am losing CDR records on my MySQL backend. I just compared CSV records
with MySQL's and I was surprised with the difference. Searching a bit, I
hit this bug: http://bugs.digium.com/view.php?id=4953


1.- kpfleming says "The spooling issue will continue to be a problem for
v1-0 users, but HEAD (and v1-2) users can use the built-in spooling in
the CDR engine.". That "built-in spooling" has to be enabled somewhere
or is it automatic?

2.- If there is a built-in spooling into CDR itself and there is no
spooling in cdr_addon_mysql, when the mysql server is down, crashed,
full, unwired, firewalled, or whatever.. is built-in spooling in CDR
itself supposed to handle that??

I'm using asterisk- and asterisk-addons-1.2.2

Joseph Benden's patch makes a great job spooling records, but you guys made him take out spooling support from cdr_addon_mysql because in asterisk 1.2.x CDR's built-in spooling support was going to take care of not losing records. But that's not happening right now.
Comments:By: Serge Vecher (serge-v) 2006-06-08 16:12:21

Please note that the bug tracker is not a right place to ask questions, but a place to file bug reports with proper debugging information.

JBenden: since you are the one who has made the patch in 4953, would you like to add a comment here? Thanks.

By: Joseph Benden (jbenden) 2006-06-08 17:21:03

Sure, I'll comment! :)

He is correct.  Due to time constraints, I decided on my past bug entry to let the subject be, since it was slowly moving along.  However, my previous spooling code should really be included.

In looking at cdr.c, I see no way for a CDR module to report back that a CDR couldn't be successfully logged and thus the CDRs can just fall off into the ether.

Ideally, the cdr.c and module definitions should be changed such that CDR modules must report success or failure and the cdr.c should spool them for future retries.  However, the simplistic approach is for the MySQL CDR module to spool them as my original patch did.

I am willing to help with whichever direction we agree to.

By: Serge Vecher (serge-v) 2006-06-09 09:57:24

well, with the impending release of 1.4 beta, I do not know if the best direction is to pursue code addition for 1.2, so I'll mark this bug as trunk. If you guys are interested in getting this into 1.4 beta, I would suggest the following:

1. Email the asterisk-dev list outlining the lack of spooling support with reference for the bug and 4953.
2. Post a patch for latest trunk.
3. Thoroughly test and get other members of the community to test and report positive results.

By: Joseph Benden (jbenden) 2006-06-11 10:15:52

I have attached a patch that applies to current SVN.  It contains the spooling support.

By: Serge Vecher (serge-v) 2006-06-11 12:31:38

we need thorough and prompt testing of this patch from jpabuyer, rpingar(?) and anybody else who is interested. Thanks.

By: wILMAR cAMPOS (willcampos) 2006-06-11 14:27:10

I will test and let you guys know...

By: wILMAR cAMPOS (willcampos) 2006-06-11 14:39:20

patching file cdr_addon_mysql.c
patch: **** malformed patch at line 14: @@ -47,8 +51,8 @@

It seems patch is not well formed....

By: Joseph Benden (jbenden) 2006-06-11 14:59:08

The patch applies cleanly for myself. Not sure why it doesn't for yourself.

[root@beavis asterisk-addons]# curl -opatch "http://bugs.digium.com/file_download.php?file_id=10545&type=bug"
 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                Dload  Upload   Total   Spent    Left  Speed
100  5739  100  5739    0     0  17469      0 --:--:-- --:--:-- --:--:--  103k
[root@beavis asterisk-addons]# patch <patch
patching file cdr_addon_mysql.c

By: wILMAR cAMPOS (willcampos) 2006-06-11 15:30:55

I execute the way you did and it works now...

I Will let you know how it goes....


By: Joseph Benden (jbenden) 2006-06-11 15:34:04

I should note that this patch requires a change to cdr_mysql.conf.  A new directive called "spool" is required to be set to a file that the user Asterisk runs under can write to. Additional information about the configuration of this is available here:


By: Juan Pablo Abuyeres (jpabuyer) 2006-06-11 21:56:35

Thank you. I will test this patch and report results on Tuesday.

By: Juan Pablo Abuyeres (jpabuyer) 2006-06-13 12:15:31

The patch applies clean to asterisk-addons-trunk rev. 220
Compiles clean too.

I tested the functionality, and works very well. If MySQL is not there, a file is created spooling non-comitted records. Once mysql is back to life, the next time cdr logs something, it flushes the spooled sqls into mysql and the file is deleted. Very good.

Liking this patch as much as I do, I must notice something. I'm not a C programmer, but from examining the code, it seems to me that there's no locking for the spooling file. So I am guessing there might be a case where 2 concurrent cdrs might write crap into the spooling file, or if mysql is down and up and down fast enough, maybe an instance of cdr would be spooling a record just before another instance is deleting the file, thus losing the spooled record (linux doesn't really delete a file while it's open, so, if the spool file was deleted while it was opened by another process writing a cdr record, it would be  really deleted as soon as cdr closes it after writing the doomed record). I really don't know, but I wanted to notice. I hope JBenden can clarify this for me.

Anyway.. As far as I can tell from my testing, the patch makes a good job and is _very_ welcome.

By: Joseph Benden (jbenden) 2006-06-13 12:20:58

Yes, you are correct about the lack of file locking; however, the entire function mysql_log() is wrapped in a mutex which serves to allow a single thread to log a CDR record at a time and effectively manage and use the spool a single thread at a time.

Therefore, while no file locking is used, a mutex is.

By: Juan Pablo Abuyeres (jpabuyer) 2006-06-13 13:34:44

Very well then.

Your patch should include changes for configs/cdr_mysql.conf.sample to make use of spooling, I'd say by default. If the sample configuration file doesn't include it, users will only find the feature in this bug report, when they already lost their CDRs, like me :)

By: Serge Vecher (serge-v) 2006-06-15 14:12:10

Joseph: got the sample config file here?

By: Joseph Benden (jbenden) 2006-06-15 14:36:45

Sure. I posted a patch and the whole thing. Not sure which you'd rather have.  I also added the timeout option.

By: Joseph Benden (jbenden) 2006-06-15 14:37:09

I should also mention that I do have a disclaimer on file...


By: wILMAR cAMPOS (willcampos) 2006-06-18 10:08:09

The patch works really well... not a single issue since applied.

By: Serge Vecher (serge-v) 2006-06-19 09:00:56

ok, well since this works for many people, please reply to the original mailing on the asterisk-dev list by Joseph detailing the successful experience you have with this patch. Only if many people vouch for it, will it make it. Thanks.

By: wILMAR cAMPOS (willcampos) 2006-06-19 09:06:37

Can someone give me the original mailing?

By: Matt Riddell (zx81) 2006-06-19 10:24:29

Subject of original mail was:

Re: [asterisk-dev] losing CDRs on mysql backend

I've had no problems since installing this patch too.

By: Joseph Benden (jbenden) 2006-07-12 09:30:33

I have uploaded an additional patch (to be applied after the original) to enable a few additional CLI commands:

cdr mysql connect
cdr mysql disconnect
cdr mysql unspool

The commands force a connect, a disconnect, and the unspooling of the stored CDRs.

Additionally, when a connection is established to the MySQL server and a network outage occurs, the MySQL client library may hang.  This causes problems with Asterisk and shows a bunch of deadlock detected messages spewing from cdr.c (not cdr_addon_mysql.c)  This is a known problem effecting certain versions of MySQL.  Here's a link for more information:


By: Serge Vecher (serge-v) 2006-07-12 09:34:22

Joseph: can you please roll all changes into one patch file, please? This will make it easier for committer to review.

ZX81: don't know if you already did, but please also reply to JBenden's email to asterisk-dev with information on successful testing, so the parties that need to see it, will.


By: Joseph Benden (jbenden) 2006-07-12 09:42:40

Ok, patch-3 contains both parts, plus a mutex around connect and disconnect.

By: Serge Vecher (serge-v) 2006-07-12 09:53:03

thanks, I'm removing the old patches...

By: Juan Pablo Abuyeres (jpabuyer) 2006-08-08 13:41:46

is this commited already?

By: Serge Vecher (serge-v) 2006-08-08 13:57:02

jpabuyer: no.
JBenden: are you visiting the #asterisk-dev channel on IRC? If not, could you please join the channel and try to get some feedback there?

By: Matt Riddell (zx81) 2006-09-06 07:01:33

What's the status on this?

We've been running it in production for months without problems.

All of the requirements were met.

I think it would be a real shame to release another version of Asterisk that has the potential to lose CDR items as it pretty much rules out Asterisk for use in production systems.

By: Juan Pablo Abuyeres (jpabuyer) 2006-09-13 10:27:02


By: Matt Riddell (zx81) 2006-09-13 10:32:18

I agree.  We're going to have to continue running custom patches on our Asterisk systems till this is accepted.

By: Juan Pablo Abuyeres (jpabuyer) 2006-09-28 17:57:34


Would you please update your patch for Addons Version 1.4.0-beta1 ?


By: Juan Pablo Abuyeres (jpabuyer) 2006-10-19 10:44:27

I mean... beta-2

By: Joshua C. Colp (jcolp) 2006-11-16 13:24:40.000-0600

I actually have a branch open that I'm hoping to merge that will basically extend the spooling support we have now so records will be kept around if a cdr driver is unavailable/under maintenance. I would like to see this being used instead of a method specific to the MySQL CDR driver. Any thoughts?

By: Matt Riddell (zx81) 2006-11-16 14:42:33.000-0600

Problem is, this has been in line for 1.2 then 1.4, and it's quite necessary.

If we brought in the other code, wouldn't it have to wait until 1.6?

If this is something that would apply to 1.4 then obviously, a generic mechanism would be much better.

We just don't want to lose cdrs when using mysql.

By: Joshua C. Colp (jcolp) 2006-11-20 21:04:28.000-0600

We can't put this in 1.4 though, it's feature frozen. It would have to go into trunk and be released in 1.6 or someone would have to overrule and put it in.

By: Juan Pablo Abuyeres (jpabuyer) 2006-11-21 06:14:00.000-0600

Meanwhile, could you give us the svn url of your branch so we can test?

By: Anthony LaMantia (alamantia) 2007-01-05 13:13:12.000-0600

ping: file, do you have a branch with this patch integrated into it for testing?

By: Serge Vecher (serge-v) 2007-03-14 08:47:38

ping file :)

By: Juan Pablo Abuyeres (jpabuyer) 2007-03-14 21:04:12

file not found :-P

By: Matt Riddell (zx81) 2007-03-19 23:52:38


By: Donny Kavanagh (donnyk) 2007-09-25 11:56:00

i just noticed this assigned to me.

I'm working on some changes to the cdr core which will allow this.

I will need to create a branch of asterisk-addons to modify cdr_addon_mysql.

I'll look into it and let you know when i have something ready for testing, atm i'm about 75% done.

This would only be a solution by 1.6+.

By: Donny Kavanagh (donnyk) 2007-09-29 00:17:39

if anyone is still interested in this, i created a branch of asterisk-addons to enable spooling support, it is however spooling in memory, i haven't given much though to backing the records up to a file system incase the system should be down.  I would like to hear peoples thoughts on this.

The addons branch would obviously depend on my asterisk-NoLossCDR branch.

By: Juan Pablo Abuyeres (jpabuyer) 2007-11-21 07:02:41.000-0600


Where can I download it from??

By: Tilghman Lesher (tilghman) 2008-01-18 15:05:05.000-0600

juggie:  any updates here?  I see that NoLossCDR branch is out of date by about 2 months.

By: jmls (jmls) 2008-02-17 12:50:51.000-0600

juggie, should we close this, or are you able to help out ?

By: Donny Kavanagh (donnyk) 2008-03-11 14:21:31

Sorry all, personal life stepped in and NoLossCDR went on life support, however me and seanbright have restarted the branch and are actively working on it again as of about two-three weeks ago.

As of right now you will find our asterisk branch on the svn in /asterisk/team/group/NoLossCDR-Redux2/

Asterisk addons can be found @ /asterisk-addons/team/group/asterisk-addons-NoLossCDR-Redux2/

I do apologize, but as of right now the asterisk-addons branch is simply a copy of trunk but has not progressed past that, as i have not had time to complete the changes required, test and commit.  I will try to do that asap.  If you would like to volonteer to test, please contact myself or seanbright on irc.

By: Jason Parker (jparker) 2008-03-20 13:00:26

With the branch that Juggie and seanbright are working on, this report doesn't really need to be open any longer, as we'll be going with that method instead.