Re: [asterisk-users] Asterisk Database Handling
Tilghman and Jay, Thanks for the licensing advice. If anyone is interested in replicate, I'm now ready to distribute it under the GPL. Regards, Matthew Roth InterMedia Marketing Solutions Software Engineer and Systems Developer ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
using odbc+( postgres or mysql) is more stable, but at all odbc + postgres is recommended --- Sherwood McGowan [EMAIL PROTECTED] wrote: Steve Prior wrote: Tilghman Lesher wrote: Correct; it's actually a workaround for a bug in the MySQL drivers. It was discovered long after 1.2 was end-of-lifed. I got bit by MySQL reconnects on some other software I wrote I think when I jumped from MySQL 4.* to 5.*. If memory serves, here is the relevant info from the official MySQL documentation: From: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection). This may explain why this is happening in Asterisk. In the case of my other code the answer was not to keep a long term connection through idle periods. Steve ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users That's probably why I've never had timeout issues, I use asterisk-addons-1.4 when I do use cdr_mysql and I think at one point there WAS a problem, but there was a published fix from the mysql_connect vs mysql_real_connect issue ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Pezhman Lali wrote: using odbc+( postgres or mysql) is more stable, but at all odbc + postgres is recommended Really? -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Unfortunately, not really feesible. I didn't design the system but we are using CDR's not only for billing purposes, but also to keep state on users. The state info (in a call, not in a call etc) needs to be updated in as close to real time as possible. Files won't do it. Douglas, I've written a program named replicate that may be of interest to you. It replicates all writes to a file over a socket. The core of the program is GNU tail, so it operates nearly in real time (it has inherited the --sleep-interval argument for fine-tuning). I've been using it in production for over a year to push all writes to the queue_log to a remote reporting server and it has been rock solid. It operates almost identically to tail -F except every new write to the file is written over a socket connection. The remote end of the connection is responsible for sending back an acknowledgment. If it doesn't, replicate will spool the write to a temporary file on the local machine. Once the remote end is responding again, the spool file is written to it. This allows us to have downtime (expected or unexpected) on the reporting server without losing any queue_log records. I've never released replicate publicly, because I'm not sure how taking a program like tail and adapting it to a new purpose is handled by the GPL. However, I'm sure someone on this list more knowledgeable than myself can help me understand that issue. Regards, Matthew Roth InterMedia Marketing Solutions Software Engineer and Systems Developer ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Thursday 22 May 2008 09:29:24 Matthew J. Roth wrote: It operates almost identically to tail -F except every new write to the file is written over a socket connection. The remote end of the connection is responsible for sending back an acknowledgment. If it doesn't, replicate will spool the write to a temporary file on the local machine. Once the remote end is responding again, the spool file is written to it. This allows us to have downtime (expected or unexpected) on the reporting server without losing any queue_log records. I've never released replicate publicly, because I'm not sure how taking a program like tail and adapting it to a new purpose is handled by the GPL. However, I'm sure someone on this list more knowledgeable than myself can help me understand that issue. There's no problem, as long as you distribute your program under the GPL, which means providing the source on demand to anybody to whom you distribute a binary. If you want to distribute under another license, then it comes down to how you developed the code. If you developed the code to work similarly to tail, but you did not use any tail code (or any other GPL code) directly, then you're pretty much free to use any license you like. However, if you started off with the tail codebase and altered it, it is a derivative work, and GPL is the only way to go. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
A program like netcat? Matthew J. Roth wrote: Douglas Garstang wrote: Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Unfortunately, not really feesible. I didn't design the system but we are using CDR's not only for billing purposes, but also to keep state on users. The state info (in a call, not in a call etc) needs to be updated in as close to real time as possible. Files won't do it. Douglas, I've written a program named replicate that may be of interest to you. It replicates all writes to a file over a socket. The core of the program is GNU tail, so it operates nearly in real time (it has inherited the --sleep-interval argument for fine-tuning). I've been using it in production for over a year to push all writes to the queue_log to a remote reporting server and it has been rock solid. It operates almost identically to tail -F except every new write to the file is written over a socket connection. The remote end of the connection is responsible for sending back an acknowledgment. If it doesn't, replicate will spool the write to a temporary file on the local machine. Once the remote end is responding again, the spool file is written to it. This allows us to have downtime (expected or unexpected) on the reporting server without losing any queue_log records. I've never released replicate publicly, because I'm not sure how taking a program like tail and adapting it to a new purpose is handled by the GPL. However, I'm sure someone on this list more knowledgeable than myself can help me understand that issue. Regards, Matthew Roth InterMedia Marketing Solutions Software Engineer and Systems Developer ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Alex Balashov wrote: A program like netcat? Alex, You're not the first person to suggest nc for this purpose. As I understand it, it's a TCP/UDP swiss army knife so I'm sure it's up to the task. However, in reading the man page, I don't see any trivial way to buffer failed writes and retry them when the connection is reestablished. I'd be glad to hear it if you know of a way. As it is, I'm assuming that I'd have to write a script around nc to handle such situations. If that's the case, I'll stick with my program because the development time is a sunk cost and it just works for my particular application. Regards, Matthew Roth InterMedia Marketing Solutions Software Engineer and Systems Developer ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Matthew J. Roth wrote: Alex Balashov wrote: A program like netcat? Alex, You're not the first person to suggest nc for this purpose. As I understand it, it's a TCP/UDP swiss army knife so I'm sure it's up to the task. However, in reading the man page, I don't see any trivial way to buffer failed writes and retry them when the connection is reestablished. I'd be glad to hear it if you know of a way. As it is, I'm assuming that I'd have to write a script around nc to handle such situations. If that's the case, I'll stick with my program because the development time is a sunk cost and it just works for my particular application. True, true. nc does not have that capability. -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wed, May 21, 2008 at 06:02:07PM -0400, Alex Balashov wrote: Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? In some recent mysql update, the behaviour with which it times out connections changed, and it bit some other project I follow as well; MythTV, I think. You might check their mailing list, Doug, for details. Cheers, -- jra -- Jay R. Ashworth Baylink [EMAIL PROTECTED] Designer The Things I Think RFC 2100 Ashworth Associates http://baylink.pitas.com '87 e24 St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274 Those who cast the vote decide nothing. Those who count the vote decide everything. -- (Joseph Stalin) ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Thu, May 22, 2008 at 10:05:13AM -0500, Tilghman Lesher wrote: I've never released replicate publicly, because I'm not sure how taking a program like tail and adapting it to a new purpose is handled by the GPL. However, I'm sure someone on this list more knowledgeable than myself can help me understand that issue. There's no problem, as long as you distribute your program under the GPL, which means providing the source on demand to anybody to whom you distribute a binary. If you want to distribute under another license, then it comes down to how you developed the code. If you developed the code to work similarly to tail, but you did not use any tail code (or any other GPL code) directly, then you're pretty much free to use any license you like. However, if you started off with the tail codebase and altered it, it is a derivative work, and GPL is the only way to go. And of course, as long as you don't distribute it[1], then you can do anything you like with it, GPL or not. Cheers, -- jra [1]for suitable values of 'distribute', which for GPLv2 means ship, and for GPLv3/AGPL means make available through a web service. -- Jay R. Ashworth Baylink [EMAIL PROTECTED] Designer The Things I Think RFC 2100 Ashworth Associates http://baylink.pitas.com '87 e24 St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274 Those who cast the vote decide nothing. Those who count the vote decide everything. -- (Joseph Stalin) ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
[asterisk-users] Asterisk Database Handling
General Asterisk question. We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. This was a documented problem with voicemail users in the db in some previous version of Asterisk 1.2, and was patched and fixed in a later version of Asterisk 1.2 We are using Asterisk 1.2.19. So... surely this must be a general problem with ANY Asterisk module that uses the database. Do all modules use the same common database code or do they all use their own? If they all use their own, I guess idle database connection issues may be fixed in some modules and not others. If it's common Asterisk database code, is it all fixed in a newer version? Is it fixed in Asterisk 1.4? Thanks, Doug. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
I had a similar problem, but in my case we had a custom application that was throwing an segmentation exception which was causing Asterisk to Restart. And in that case It use to miss the log in database. You can determine the same by looking at the UNIQUEID being logged for the call. The UNIQUEID actually comprises of 2 things DateTimeString.CallNumber , everytime Asterisk restarts CallNumber will start from 1. So you can check the 2-3 UNIQUEID before you missed entry in CDR table and 2-3 after the missed entry to determine if Asterisk Restarted. Other way is you can see the kernel logs to see if Asterisk has thrown any exception. they can be found on Linux at /var/log/messages Regards, Sanjay Rajdev - Original Message - From: Alex Balashov [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Thursday, May 22, 2008 3:32:07 AM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi Subject: Re: [asterisk-users] Asterisk Database Handling Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? -- Alex Balashov Evariste Systems Web : http://www.evaristesys.com/ Tel : (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Sanjay Rajdev wrote: I had a similar problem, but in my case we had a custom application that was throwing an segmentation exception which was causing Asterisk to Restart. And in that case It use to miss the log in database. You can determine the same by looking at the UNIQUEID being logged for the call. The UNIQUEID actually comprises of 2 things DateTimeString.CallNumber, everytime Asterisk restarts CallNumber will start from 1. So you can check the 2-3 UNIQUEID before you missed entry in CDR table and 2-3 after the missed entry to determine if Asterisk Restarted. Other way is you can see the kernel logs to see if Asterisk has thrown any exception. they can be found on Linux at /var/log/messages There is no such thing as a segmentation exception. C does not have exception handling, and Asterisk does not throw any exceptions. There is, however, a segmentation fault. That is a memory condition that causes the operating system to terminate the process, in absence of a signal handler for it. None of these can or should be characterised as exceptions, and have absolutely nothing to do with them. -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
I couldn't find one for cdr_mysql.conf. We're using odbc anyway. MySQL directly might be an option if it works. Don't think we want to modify the server. - Original Message From: Alex Balashov [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Wednesday, May 21, 2008 3:02:07 PM Subject: Re: [asterisk-users] Asterisk Database Handling Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: I couldn't find one for cdr_mysql.conf. We're using odbc anyway. MySQL directly might be an option if it works. Don't think we want to modify the server. Perhaps there's a keepalive option to be set on the UnixODBC DSN, then? -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wed, May 21, 2008 at 5:37 PM, Douglas Garstang [EMAIL PROTECTED] wrote: General Asterisk question. We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. This was a documented problem with voicemail users in the db in some previous version of Asterisk 1.2, and was patched and fixed in a later version of Asterisk 1.2 We are using Asterisk 1.2.19. So... surely this must be a general problem with ANY Asterisk module that uses the database. Do all modules use the same common database code or do they all use their own? If they all use their own, I guess idle database connection issues may be fixed in some modules and not others. If it's common Asterisk database code, is it all fixed in a newer version? Is it fixed in Asterisk 1.4? Thanks, Doug. I have no answer, just a potential work around if you get no good answers. I guess you could just pipe the master.csv into your database (similar to how queuemetrics does queue_log) Thanks, Steve Totaro ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
- Original Message From: Alex Balashov [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Wednesday, May 21, 2008 3:30:06 PM Subject: Re: [asterisk-users] Asterisk Database Handling Douglas Garstang wrote: I couldn't find one for cdr_mysql.conf. We're using odbc anyway. MySQL directly might be an option if it works. Don't think we want to modify the server. Perhaps there's a keepalive option to be set on the UnixODBC DSN, then? I already searched. Could not find one. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
So... surely this must be a general problem with ANY Asterisk module that uses the database. Do all modules use the same common database code or do they all use their own? If they all use their own, I guess idle database connection issues may be fixed in some modules and not others. If it's common Asterisk database code, is it all fixed in a newer version? Is it fixed in Asterisk 1.4? Thanks, Doug. I have no answer, just a potential work around if you get no good answers. I guess you could just pipe the master.csv into your database (similar to how queuemetrics does queue_log) Are you saying this is a known problem? ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: I couldn't find one for cdr_mysql.conf. We're using odbc anyway. MySQL directly might be an option if it works. Don't think we want to modify the server. - Original Message From: Alex Balashov [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Wednesday, May 21, 2008 3:02:07 PM Subject: Re: [asterisk-users] Asterisk Database Handling Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. Sherwood McGowan ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wed, May 21, 2008 at 6:42 PM, Douglas Garstang [EMAIL PROTECTED] wrote: So... surely this must be a general problem with ANY Asterisk module that uses the database. Do all modules use the same common database code or do they all use their own? If they all use their own, I guess idle database connection issues may be fixed in some modules and not others. If it's common Asterisk database code, is it all fixed in a newer version? Is it fixed in Asterisk 1.4? Thanks, Doug. I have no answer, just a potential work around if you get no good answers. I guess you could just pipe the master.csv into your database (similar to how queuemetrics does queue_log) Are you saying this is a known problem? Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Thanks, Steve Totaro ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Sherwood McGowan wrote: I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. I would tend to strongly concur here. ODBC is nice, but it's not nearly as well-supported or mature as the native database interfaces. Doesn't mean it works badly, just that I have lower expectations for it than I do for cdr_{nativeDB}. -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. It's interesting you say that Sherwood. Does your MySQL server have some sort of keep alive setting? I suspect this is a general problem that would affect any and all Asterisk database connectivity. Doug. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wed, May 21, 2008 at 7:00 PM, Douglas Garstang [EMAIL PROTECTED] wrote: I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. It's interesting you say that Sherwood. Does your MySQL server have some sort of keep alive setting? I suspect this is a general problem that would affect any and all Asterisk database connectivity. Doug. A quote from Tilghman Lesher from a previous post. That's fine, but I have had the most horrid results using any distribution- supplied ODBC drivers. The best results are obtained by source-compiling the latest ODBC drivers, whether they be the MySQL ODBC Connector 3.51 or PsqlODBC. UnixODBC is fairly safe to use from distribution channels, however. Thanks, Steve Totaro ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: It's interesting you say that Sherwood. Does your MySQL server have some sort of keep alive setting? I suspect this is a general problem that would affect any and all Asterisk database connectivity. I would actually expect it to affect only specific types of database connections. While there may exist in the source code a general database abstraction layer, it would only be a wrapper for interfaces to particular databases implemented using native APIs elsewhere in the code (i.e. the MySQL protocol, the Postgres protocol, the ODBC interchange protocol, etc.) -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. Oh... Also... does your system have idle periods? You said your servers where under high load. The issue seems to be that Asterisk does not re-establish the database connection after it is disconnected do to being idle. If you don't have idle periods, you'd never see this problem anyway. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Unfortunately, not really feesible. I didn't design the system but we are using CDR's not only for billing purposes, but also to keep state on users. The state info (in a call, not in a call etc) needs to be updated in as close to real time as possible. Files won't do it. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wed, May 21, 2008 at 7:11 PM, Douglas Garstang [EMAIL PROTECTED] wrote: Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Unfortunately, not really feesible. I didn't design the system but we are using CDR's not only for billing purposes, but also to keep state on users. The state info (in a call, not in a call etc) needs to be updated in as close to real time as possible. Files won't do it. A named pipe would write in realtime but if broken would cause big problems. http://www.mail-archive.com/asterisk-users@lists.digium.com/msg203863.html This thread may be of help. Thanks, Steve Totaro ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: Not at all, just offering a workaround. If your master.csv is complete and correct then it makes sense to use that data unless someone can identify your problem and offer a fix. Unfortunately, not really feesible. I didn't design the system but we are using CDR's not only for billing purposes, but also to keep state on users. The state info (in a call, not in a call etc) needs to be updated in as close to real time as possible. Files won't do it. The Manager API can get that information out for you in even more real-time, although I've had some problems with its accuracy (vide disconnect events, etc). But, I agree, it's easier to just get the CDRs working. :-) -- Alex Balashov Evariste Systems Web: http://www.evaristesys.com/ Tel: (+1) (678) 954-0670 Direct : (+1) (678) 954-0671 Mobile : (+1) (706) 338-8599 ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. Oh... Also... does your system have idle periods? oh and yes, overnight there is NO load or next to no load on our server at my current project, but in the morning nothing needs to be done to have the pgsql cdrs work just fine. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Douglas Garstang wrote: I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. Oh... Also... does your system have idle periods? You said your servers where under high load. The issue seems to be that Asterisk does not re-establish the database connection after it is disconnected do to being idle. If you don't have idle periods, you'd never see this problem anyway. ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users True, I should have also mentioned that I have used it on low load testing servers and it did not have a problem with reconnecting. Like I said, I haven't used the ODBC method, but the cdr_mysql addon reconnected for me. Currently, however, I'm operating a high load (1K calls per hour or more) server with cdr_pgsql with no keep alive, but as far as I remember I never had to institute a keep_alive of any sort on my other servers. However, don't take this as gold, it's been more than 6 months since I worked with a low load server. Sherwood McGowan ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wednesday 21 May 2008 17:02:07 Alex Balashov wrote: Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? Please check the 'idlecheck' option in res_odbc.conf. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wednesday 21 May 2008 17:52:45 Alex Balashov wrote: Sherwood McGowan wrote: I personally can tell you I've never had a problem with either the PostgreSQL or MySQL cdr apps themselves losing records. However, I can't say personally how well the ODBC method works. I'll just stick to saying that if you're considering using the cdr_mysql addon, I would highly suggest it as I've used it with MUCH success on high load servers. I would tend to strongly concur here. ODBC is nice, but it's not nearly as well-supported or mature as the native database interfaces. Doesn't mean it works badly, just that I have lower expectations for it than I do for cdr_{nativeDB}. Most of the CDR backends suck. The ODBC backend has gotten a complete workover in 1.6, though. The adaptive ODBC CDR backend should make it easier to log anything that people might want in their CDRs. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Looks like an Asterisk 1.4 option? - Original Message From: Tilghman Lesher [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Wednesday, May 21, 2008 4:39:24 PM Subject: Re: [asterisk-users] Asterisk Database Handling On Wednesday 21 May 2008 17:02:07 Alex Balashov wrote: Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? Please check the 'idlecheck' option in res_odbc.conf. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wednesday 21 May 2008 19:03:25 Douglas Garstang wrote: On Wednesday, May 21, 2008 4:39:24 PM, Tilghman Lesher wrote: On Wednesday 21 May 2008 17:02:07 Alex Balashov wrote: Douglas Garstang wrote: We are sending CDR's to MySQL via odbc. It seems that Asterisk is sometimes dropping CDR's, and they aren't being sent to the database (they ARE in the Master.csv file though). We suspect that when the MySQL socket is idle, it gets disconnected, either by the MySQL server or by our firewall, and when Asterisk goes to send the next CDR over the socket, does not re-open the database, and drops that CDR. Possibly on the next call, it connects ok and sends the next CDR. Isn't there a keepalive option somewhere for cdr_mysql.conf, or failing that, a keepalive mechanism that can be enabled for TCP connections on the server side? Please check the 'idlecheck' option in res_odbc.conf. Looks like an Asterisk 1.4 option? Correct; it's actually a workaround for a bug in the MySQL drivers. It was discovered long after 1.2 was end-of-lifed. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Tilghman Lesher wrote: Correct; it's actually a workaround for a bug in the MySQL drivers. It was discovered long after 1.2 was end-of-lifed. I got bit by MySQL reconnects on some other software I wrote I think when I jumped from MySQL 4.* to 5.*. If memory serves, here is the relevant info from the official MySQL documentation: From: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection). This may explain why this is happening in Asterisk. In the case of my other code the answer was not to keep a long term connection through idle periods. Steve ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
On Wednesday 21 May 2008 19:36:11 Steve Prior wrote: Tilghman Lesher wrote: Correct; it's actually a workaround for a bug in the MySQL drivers. It was discovered long after 1.2 was end-of-lifed. I got bit by MySQL reconnects on some other software I wrote I think when I jumped from MySQL 4.* to 5.*. If memory serves, here is the relevant info from the official MySQL documentation: From: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection). This may explain why this is happening in Asterisk. In the case of my other code the answer was not to keep a long term connection through idle periods. No, actually, we're using that flag to try to get automatic reconnection, and we're getting a segmentation fault from the MySQL drivers. It would be one thing if queries simply failed, but that's not the case. Note also that we're not only not using any of those items which could be lost after a reconnection, we're actually re-preparing the statement handle after each reconnect. However, once I added the idlecheck timeout, I have yet to have gotten the segmentation fault related to this MySQL driver bug. Interestingly, the segfault only happens on the second statement run after a reconnect, which leads me to believe that there is some shared structure which is getting properly reallocated in one place, leaving a stale handle to another, which when dereferenced causes the segmentation fault. However, not being familiar with the driver code, I haven't gone any further in trying to track this bug down. -- Tilghman ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Asterisk Database Handling
Steve Prior wrote: Tilghman Lesher wrote: Correct; it's actually a workaround for a bug in the MySQL drivers. It was discovered long after 1.2 was end-of-lifed. I got bit by MySQL reconnects on some other software I wrote I think when I jumped from MySQL 4.* to 5.*. If memory serves, here is the relevant info from the official MySQL documentation: From: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection). This may explain why this is happening in Asterisk. In the case of my other code the answer was not to keep a long term connection through idle periods. Steve ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users That's probably why I've never had timeout issues, I use asterisk-addons-1.4 when I do use cdr_mysql and I think at one point there WAS a problem, but there was a published fix from the mysql_connect vs mysql_real_connect issue ___ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users