If you're using HTTP API call, I guess you're also taking advantage of
dlr_url & dlr_mask options to get DLRs, right?
Do you store anywhere message statuses and actual dlr_url ?
The problem is you need to delete only old entries, but dlr table itself
doesn't contain a time field there.
There are only:
| smsc | varchar(40) | YES | MUL | NULL | |
| ts | varchar(255) | YES | | NULL | |
| destination | varchar(40) | YES | | NULL | |
| source | varchar(40) | YES | | NULL | |
| service | varchar(40) | YES | | NULL | |
| url | varchar(255) | YES | UNI | NULL | |
| mask | int(10) | YES | | NULL | |
| status | int(10) | YES | | NULL | |
| boxc | varchar(40) | YES | | NULL | |
| parts_num | bigint(20) | YES | | NULL | |
So you can delete appropriate entries based on either Source, Destination,
SMSC, BOXC or anything else via:
For example for specific smsc:
DELETE FROM dlr WHERE smsc='smsc1';
2012/4/3 Tapan Thapa <tapan.thapa2...@gmail.com>
> 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 <spame...@gmail.com> 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 <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
>>>>>
>>>>
>>>>
>>>
>>
>
