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 <tapan.thapa2...@gmail.com>
> 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 <spame...@gmail.com> 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 <tapan.thapa2...@gmail.com>
>>
>>> 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
>>>
>>
>>
>
