Hello, I am sorry but your provided query will not work for me as i am not using sqlbox to send messages.
I am using smsbox to send sms via HTTP API call and at bearerbox's end i am storing dlr under mysql so that in case bearerbox crashes due to any reason, i have dlr to restore from MySQL. Please suggest any query to delete dlr from dlr tables as there is no field through which i can delete rows in it. Regards Tapan Thapa On Tue, Apr 3, 2012 at 10:32 AM, spameden <[email protected]> wrote: > you can use this query: > > delete d from dlr d inner join sent_sms s ON s.dlr_url=d.url where > s.momt='MT' and s.time <= UNIX_TIMESTAMP('2012-04-02 08:00'); > > to delete all dlr's from dlr table before 2012-04-02 08:00 > > the only thing is you need to double check if sent_sms has dlr_url field > the same value as url field in dlr table. > > > > 2012/4/3 Tapan Thapa <[email protected]> > >> Hello, >> >> Let me confirm that we are getting proper delivery report from SMSC and >> the same is getting deleted automatically from dlr table however as we all >> know that we generally don't receive 100% delivery report. >> >> So we use to get 80-90% of delivery report and rest 10-20% delivery >> report is still available in pending state in dlr table. >> >> So my query here is, is there any way (like sql query) through which i >> can delete old dlr from dlr table. >> >> Thanks in advance. >> >> Regards >> Tapan Thapa >> >> On Tue, Apr 3, 2012 at 9:46 AM, spameden <[email protected]> wrote: >> >>> Might be something with your setup. Are you sure DLRs are receiving? >>> >>> Can you check sent_sms table, is there any DLR entries? >>> >>> About dlr table: I strongly suggest adding indexes there for speeding >>> things up. >>> >>> If you're inserting message into send_sms with dlr_mask!=NULL kannel >>> automatically adds relevant entry into dlr table to keep track of the DLR, >>> after DLR is received kannel deletes entry from dlr table and moves DLR >>> entry into sent_sms table. >>> >>> It's bit weird that your dlr table has over 8mln of entries, something >>> is wrong with your setup. >>> >>> Regards >>> >>> >>> 2012/4/3 Tapan Thapa <[email protected]> >>> >>>> Hello Community, >>>> >>>> I had enabled dlr-storage under kannel to store delivery report in >>>> mysql database and now dlr table is having more then 8 (.8 million) lacs >>>> records. >>>> >>>> Can any one let me know how should i delete old dlr from database for >>>> which i have not received the delivery report since last 10 days? >>>> >>>> Thanks in advance. >>>> >>>> Regards >>>> Tapan Thapa >>>> >>> >>> >> >
