mod_crd_sqlite entry limit and sqlite segfaults on triggers
Hi, By looking at the code, I believe there is no limit on the number of rows mod_cdr_sqlite will add to the cdr table. This could lead to (eventually) eating all RAM if you DB is in tmpfs or eating all disk space if it is stored on disk. Is this correct or am I missing something? To correct this I tried to add a trigger to the table along the lines of: CREATE TRIGGER IF NOT EXISTS CDR_LIMIT AFTER INSERT ON cdr BEGIN DELETE FROM crd WHERE rowid <= (SELECT MAX(rowid) from cdr) - 100000; END and no matter how I try to add the trigger, I get a segmentation fault when the code calls sqlite3_exec in switch_core_db_exec. Currently I'm blaiming SQLite, as there is a somewhat similar issue involving triggers reported for versions older than 3.5.4 (FS is using 3.3.13) http://sqlite.org/cvstrac/wiki?p=DatabaseCorruption Of course I could just execute the delete statement manually after every insert, but it is uglier (more costly, non atomic). Has anyone any experience using triggers with the sqlite version included in FS? Or has anyone tried other solution to limit the number on entries on the cdr table? Thanks in advance Neven Boric
It seems I have fixed the issue. I did a backtrace with gdb and saw that the crash was when sqlite3ExprListDup called strdup passing a NULL pointer. I asked on the SQLIte mailing list and they told me that SQLite doesn't use strdup. So, I downloaded the original 3.3.13 SQLite release and compared it to the Freeswitch code. I found out that calls to sqlite3StrDup had been replaced with calls to strdup (see sqliteInt.h). Problem is sqlite3StrDup checks for NULL input, whereas strdup does not. I put the following instead: #define sqliteStrDup(x) (x?strdup(x):NULL)//sqlite3StrDup(x) And now I can create triggers without crashes. Seems to me that as nobody uses triggers inside Freeswitch, this issue has gone unnoticed so far. I will continue testing to discard any side effects. Neven Boric escribió:
Hi,
By looking at the code, I believe there is no limit on the number of rows mod_cdr_sqlite will add to the cdr table. This could lead to (eventually) eating all RAM if you DB is in tmpfs or eating all disk space if it is stored on disk. Is this correct or am I missing something?
To correct this I tried to add a trigger to the table along the lines of:
CREATE TRIGGER IF NOT EXISTS CDR_LIMIT AFTER INSERT ON cdr BEGIN DELETE FROM crd WHERE rowid <= (SELECT MAX(rowid) from cdr) - 100000; END
and no matter how I try to add the trigger, I get a segmentation fault when the code calls sqlite3_exec in switch_core_db_exec.
Currently I'm blaiming SQLite, as there is a somewhat similar issue involving triggers reported for versions older than 3.5.4 (FS is using 3.3.13)
http://sqlite.org/cvstrac/wiki?p=DatabaseCorruption
Of course I could just execute the delete statement manually after every insert, but it is uglier (more costly, non atomic).
Has anyone any experience using triggers with the sqlite version included in FS? Or has anyone tried other solution to limit the number on entries on the cdr table?
Thanks in advance Neven Boric
Please post this type of thing to http://jira.freeswitch.org in the future. I have taken your change and committed it this time. It is very easy for these changes to get lost otherwise. -- View this message in context: http://freeswitch-users.2379917.n2.nabble.com/mod-crd-sqlite-entry-limit-and... Sent from the freeswitch-users mailing list archive at Nabble.com.
Jeff Lenk escribió:
Please post this type of thing to http://jira.freeswitch.org in the future.
Will do. What about the limit on the number of cdrs? Should I report that too?
I have taken your change and committed it this time. It is very easy for these changes to get lost otherwise.
-- View this message in context: http://freeswitch-users.2379917.n2.nabble.com/mod-crd-sqlite-entry-limit-and... Sent from the freeswitch-users mailing list archive at Nabble.com.
_______________________________________________ FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
Thats more of an implementation question for the author. Daniel Swarbrick. but you could open an issue on that too and assign it to him and he will check into it. -- View this message in context: http://freeswitch-users.2379917.n2.nabble.com/mod-crd-sqlite-entry-limit-and... Sent from the freeswitch-users mailing list archive at Nabble.com.
I would argue that you do not want to check the row count on every single insert. Have a cron job that runs periodically and does the deletes. Run that job once a day. Doing the maintenance on each insert is going to kill performance *and* concurrency. On Wed, Apr 27, 2011 at 2:56 PM, Neven Boric <nboric@yx.cl> wrote:
Will do. What about the limit on the number of cdrs? Should I report that too?
-- -Rupa
you're working in circles our version of sqliteStrDup does check for NULL the same way the other function does #define sqliteStrDup(x) (x?strdup(x):NULL) see......? x ? strdup(x) : NULL if x strdup it else return NULL sqlite3StrDup does the same thing if( z==0 ) return 0; The only difference is that sqlite devs use their own memory management system that does not work for us under heavy loads so we stick with good old malloc/free. besides, tolerating NULL is a fail safe. the real question is what is even trying to pass NULL to be duped, and, its actually doing exactly what it's asked it dups NULL to NULL. On Tue, May 10, 2011 at 8:24 AM, Rupa Schomaker <rupa@rupa.com> wrote:
I would argue that you do not want to check the row count on every single insert. Have a cron job that runs periodically and does the deletes. Run that job once a day. Doing the maintenance on each insert is going to kill performance *and* concurrency.
On Wed, Apr 27, 2011 at 2:56 PM, Neven Boric <nboric@yx.cl> wrote:
Will do. What about the limit on the number of cdrs? Should I report that too?
-- -Rupa
_______________________________________________ FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
-- Anthony Minessale II FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
participants (4)
-
Anthony Minessale
-
Jeff Lenk
-
Neven Boric
-
Rupa Schomaker