You can add a timestamp field to your table with default ON_INSERT
CURRENT_TIME_STAMP.
As this is only dlr table, if you delete all, there won't be any
problem other than an error line in your logs for a dlr coming in and
not having a match.
Hope helps
Alvaro
On 4/3/12, spameden <spame...@gmail.com> wrote:
> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
--
|-----------------------------------------------------------------------------------------------------------------|
Envíe y Reciba Datos y mensajes de Texto (SMS) hacia y desde cualquier
celular y Nextel
en el Perú, México y en mas de 180 paises. Use aplicaciones 2 vias via
SMS y GPRS online
Visitenos en www.perusms.NET www.smsglobal.com.mx y
www.pravcom.com
