[Home]

Summary:ASTERISK-03538: CDRs not being written when DB is restarted.
Reporter:eko1 (eko1)Labels:
Date Opened:2005-02-18 09:45:32.000-0600Date Closed:2005-05-09 21:58:39
Priority:MajorRegression?No
Status:Closed/CompleteComponents:CDR/cdr_odbc
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:I have Asterisk writting CDRs through ODBC to a PostgreSQL DB. Whenever I restart PostgreSQL, Asterisk does not write CDRs to the DB only until I restart or reload Asterisk (since this reloads the cdr_odbc.so module but it doesn't always work).

I was able to reproduce this on both my production Asterisk server and my personal Asterisk server. Asterisk doesn't show you any info. but I checked my cdr table and looked at the data.


****** ADDITIONAL INFORMATION ******

I was wondering if someone can add sanity checks in the cdr_odbc.c code like in:
http://bugs.digium.com/bug_view_page.php?bug_id=0003529
so that it checks the DB connection before attempting to write the CDR. It would be nice to have Asterisk report the error if it fails to write a CDR.
Comments:By: raiden (raiden) 2005-02-18 09:58:42.000-0600

I have the *same* problem with ODBC and a PostGres database but with realtime sip authentication. If I restart my database, asterisk fails to reconnect and authenticate users. Only when I restart asterisk it will connect. I originally thought it was a unixODBC setting problems but people using mysql dont have that issue.
By: Mark Spencer (markster) 2005-02-18 10:41:31.000-0600

I believe the realtime via ODBC has been fixed as of yesterday thanks to anthm.
By: Clod Patry (junky) 2005-02-18 11:37:52.000-0600

with cdr_pgsql.c i know it's handle.
By: raiden (raiden) 2005-02-18 12:08:54.000-0600

using cdr_pgsql.c (for cdr's), it does reconnect which is no problem (for me). I also recompiled * from cvs a few minutes ago (which should have anthm's fix) and the realtime sip authentication does not reconnect with unixODBC.

Feb 18 12:15:26 NOTICE[9264]: chan_sip.c:8393 handle_request: Registration from '<sip:1104@192.168.0.5>' failed for 'xxx.xxx.xxx.xxx'
Feb 18 12:15:26 WARNING[9264]: res_config_odbc.c:66 realtime_odbc: SQL Alloc Handle failed!
Feb 18 12:15:26 NOTICE[9264]: chan_sip.c:8393 handle_request: Registration from '<sip:1108@192.168.0.7>' failed for 'xxx.xxx.xxx.xxx'
Feb 18 12:15:26 WARNING[9264]: res_config_odbc.c:66 realtime_odbc: SQL Alloc Handle failed!

If I restart asterisk, everything works fine!
By: Brian West (bkw918) 2005-02-18 14:09:50.000-0600

cdr_odbc will always retry the connection if it fails like twice if I recall...  After that it will give up and say to hell with it.   I did write it with that in mind :P


bkw
By: Brian West (bkw918) 2005-02-18 14:10:25.000-0600

maybe the Postgres ODBC driver is not right.. their are two of them out there.

bkw
By: eko1 (eko1) 2005-02-18 16:00:27.000-0600

Just for the record, I'm using psqlodbc-07.03.0200, i.e. not the one bundle with unixODBC.
By: eko1 (eko1) 2005-02-18 16:53:47.000-0600

junky: You said it works with cdr_pgsql but I switched over to it and I am having the same problem. I'd be interested to know what you tried to reproduce the problem.

At least with cdr_pgsql, you get an error message:

Feb 18 16:47:48 ERROR[19588]: cdr_pgsql.c:128 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
Feb 18 16:47:48 ERROR[19588]: cdr_pgsql.c:129 pgsql_log: cdr_pgsql: Reason: FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
       This probably means the server terminated abnormally
       before or while processing the request.
By: Brian West (bkw918) 2005-02-18 18:12:39.000-0600

if you notice

if (!connected) {                                                                                                                    
       res = odbc_init();

you'll notice it that on every and all failure i set connected = 0;

thus when it fails it will call odbc_ini() to bring the connection back up.

Now logic tells me that if you're having this problem with cdr_pgsql this appears to be a Layer 8 issue.

bkw
By: Anthony Minessale (anthm) 2005-02-19 09:41:46.000-0600

How bout we convert it to use res_odbc then you can use the new

res = odbc_smart_execute(obj, stmt);
Which will reconnect and try again on all failures.

It would break back compat though, cos fetching the res_odbc obj differs from accessing a dsn directly so the config would have to change but at least the db handle can stay connected.
By: Mark Spencer (markster) 2005-02-19 14:38:04.000-0600

Can you confirm if CVS head works as you expect?
By: Clod Patry (junky) 2005-02-20 19:13:49.000-0600

take a look on ASTERISK-354828
By: Mark Spencer (markster) 2005-02-26 01:58:33.000-0600

Last call to confirm or deny that this is a problem in CVS head or this bug will be closed.
By: Anthony Minessale (anthm) 2005-02-26 09:42:58.000-0600

well,

hold on... let me get my flame resistant force field up, ok. Can't we take our efforts and try and make 1 thing work?  I think EVERY cdr_SOMESPECIFICDB.c should go into addons along with most other overly specific modules.  Undoubtedly native db drivers have less code to execute than a connection manager but if the scope of asterisk is limited to supporting ODBC which is not my favorite thing in the world but the most logical thing for asterisk because it has enough troubles on it's mind as it is to be worrying about res_overly_specific_redundant_module.so popping up and complicating things.  The average new user is not going to care about nanoseconds of performance boost.  Each specific module has to reimplement connection caching, sanity checks etc all over again (and possibly wrong). As we see from the mysqlectimy performed on chan_iax/sip the birds are out and chirping in the valley again.   If asterisk's goal is truly simplicity it should drop as much of these specific modules as it can and just move it to addons you can still use it just dont force it on the masses they don't really even need it.  You could buy the average user a PC from dell or somewhere for like $400 and they are happy where most of us would toss on 2 more gigs of ram and a RAID array later if we weren't happy with the basic product.

Anyway in conclusion I think we should redo cdr_odbc to use res_odbc and the new templatable CDR system (yeah res_odbc and the new templatable thing is all my code but duhh that's why I made it) once it's done you have 1 solid way to build your own sql st and use your own SQL engine and it will try its best to keep your connection up and you move the development burden of sql connection drivers to the guy who wrote the db server who is the best guy for the job wtf else could you want for now.
By: Mark Spencer (markster) 2005-02-26 10:24:53.000-0600

The res_odbc connection stuff still isn't working right as evidenced by the smart_sqlexecute or whatever is not working without the explicit select(1) being called each time, which is an unreasonable performance burden.  When that's fixed, then it may be worthwhile to point people to res_odbc in the future.

The point right now is that the code in cdr_odbc (in CVS head) does appear to have code setup to automatically reconnect, and I need to know if that is working or not.  

Also note that just because you see the error does NOT mean it is not working, it just means it failed to write, but according to the code, both modules will attempt to write it again.
By: eko1 (eko1) 2005-02-26 10:48:47.000-0600

OK. I download CVS HEAD this moring, Asterisk CVS-HEAD-02/26/05-10:24:43. I stopped PostgreSQL and restarted it, made a call, checked the cdr table with psql and the call was there! It worked!
By: Anthony Minessale (anthm) 2005-02-26 11:14:37.000-0600

Then you're gonna need smart_everything a replacement for EVERY odbc call similar to smart_execute.  In my tests when the db was dead only execute failed with the db 100% down and it recovered instantly that is why i put the smart thing there so some ppl may have a SQL server that is unreliable they may want the repeated "select 1" rather than missing a call i bet you could do 500+ per second and not bother the system taht is why i made that burden controable.  Can we please hear from people (with details) who have permenant failure with the sanitycheck set to 1.
By: Mark Spencer (markster) 2005-02-26 11:24:35.000-0600

Okay great, then this is already fixed in CVS head.  If you really need it in stable, you'll have to talk to russell :)
By: Russell Bryant (russell) 2005-05-09 21:58:38

hm ... I'm not sure what the original problem was, but cdr_odbc in 1.0 does not appear to have differences from the one in cvs head that would make a difference in this case.  I'm going to go ahead and close this out, assuming there isn't anything that I need to do.  Someone feel free to indicate otherwise if I am wrong.