[SR-Users] Changing acc_cdrs time and duration columns from varchar to int/float

[mayamatakeshi]

Hello Daniel,
I am load testing it and after tens of millions of records, there were no
issues.
So I will push this to production.
Thank you,

Regards,
Takeshi

On Mon, Jul 27, 2015 at 4:41 PM, Daniel-Constantin Mierla <miconda at gmail.com
> wrote:

>  Hello,
>
> iirc, Kamailio does only insert operations, therefore if you tested and
> works, then should be no problem, because there is no select to retrieve
> the values back in kamailio.
>
> Cheers,
> Daniel
>
>
> On 21/07/15 00:07, mayamatakeshi wrote:
>
>
>
> On Tue, Jul 21, 2015 at 6:35 AM, mayamatakeshi <mayamatakeshi at gmail.com>
> wrote:
>
>>  Hello,
>> I am planning to enable acc cdr in my kamailio 4.1 server.
>> I am testing in my lab and it is working fine.
>> However, when I check the table definition I see this:
>>
>>  mysql> describe acc_cdrs;
>> +------------+------------------+------+-----+---------+----------------+
>> | Field      | Type             | Null | Key | Default | Extra          |
>> +------------+------------------+------+-----+---------+----------------+
>> | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
>> | start_time | varchar(32)      | NO   | MUL |         |                |
>> | end_time   | varchar(32)      | NO   |     |         |                |
>> | duration   | varchar(32)      | NO   |     |         |                |
>> +------------+------------------+------+-----+---------+----------------+
>> 4 rows in set (0.00 sec)
>>
>>
>>  The *_time and duration columns are defined as varchar.
>>  Because of this, i need to perform a cast when doing queries:
>>   select * from acc_cdrs where cast(start_time as unsigned) >= 1437120622;
>> which would cause extra processing in the db server.
>>
>>  So I experimented changing these columns to "int unsigned" and to
>> "float unsigned" and it worked for both without problems (records properly
>> added to acc_cdrs and no errors in kamailio.log):
>>
>>  alter table acc_cdrs modify start_time int unsigned;
>> alter table acc_cdrs modify end_time int unsigned;
>> alter table acc_cdrs modify duration int unsigned;
>>
>>  alter table acc_cdrs modify start_time float unsigned;
>> alter table acc_cdrs modify end_time float unsigned;
>> alter table acc_cdrs modify duration float unsigned;
>>
>
>  Actually, checking again, they should not be a "float unsigned" but
> "double unsigned":
>
>  alter table acc_cdrs modify start_time double unsigned;
> alter table acc_cdrs modify end_time double unsigned;
> alter table acc_cdrs modify duration double unsigned;
>
>
>
>>
>>  So I am thinking in doing this in my production server but I would like
>> to know if there is any potential risk of doing this (as I am not sure why
>> it was decided for these fields to be varchar instead of a numeric type).
>>
>>  Regards,
>> Takeshi
>>
>
>
>
> _______________________________________________
> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing listsr-users at lists.sip-router.orghttp://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>
>
> --
> Daniel-Constantin Mierlahttp://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
> Book: SIP Routing With Kamailio - http://www.asipto.com
>
>
> _______________________________________________
> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
> sr-users at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20150727/288e2c52/attachment-0001.html>