Is Uniqueid/sequence A Safe CDR Table Primary Key ?

Home » Asterisk Users » Is Uniqueid/sequence A Safe CDR Table Primary Key ?
Asterisk Users 5 Comments

Hi,

When dealing with CDR SQL tables, I always added an auto-incremented cdr_id key as a primary key, just in case provided uniqueid key went wrong.

Now I’m facing a situation where I need to insert into a database’s table and from the dialplan, a reference to the CDR record which is currently processed.

So my questions are:

1. Can uniqueid/sequence (or uniqueid/sequence/calldate) bundle be safely used as CDR’s table primary key (ie I cannot have any uniqueid/sequence combination from one CDR record to match a past uniqueid/sequence combination) ?

2. Suggestions ?

Regards

5 thoughts on - Is Uniqueid/sequence A Safe CDR Table Primary Key ?

  • Possibly. Things to keep in mind:

    * You can run into uniqueid collisions across multiple systems if you do not specify a system name in asterisk.conf or do not specify a unique system name in asterisk.conf.
    * You can run into uniqueid collisions if your system clock goes backwards for any reason (the uniqueid for a channel happens to use a timestamp for its uniqueness)

    Whether or not this is unique enough will be completely dependent on your overall system configuration.

    In general, the recommended combination that *should* uniquely specify a CDR (when configured correctly) is linkedid (which should be enabled and added to your schema), uniqueid, and sequence number, with the asterisk system name specified.

  • OK, then I’ll go with linkedid, uniqueid and sequence number.

    Thanks for sharing this on this list

    2013/6/3 Matthew Jordan

  • Hello,

    Still about CDR and MySQL table, should the calldate field be inserted by Asterisk?

    This is the table structure we are using, based on Asterisk wiki:

    mysql> describe cdr;
    +————-+———————–+——+—–+———————+—————-+
    | Field | Type | Null | Key | Default |
    Extra |
    +————-+———————–+——+—–+———————+—————-+
    | id | mediumint(8) unsigned | NO | PRI | NULL |
    auto_increment |
    | calldate | datetime | NO | | 0000-00-00 00:00:00
    | |
    | clid | varchar(80) | NO | |
    | |
    | src | varchar(80) | NO | |
    | |
    | dst | varchar(80) | NO | |
    | |
    | dcontext | varchar(80) | NO | |
    | |
    | channel | varchar(80) | NO | |
    | |
    | dstchannel | varchar(80) | NO | |
    | |
    | lastapp | varchar(80) | NO | |
    | |
    | lastdata | varchar(80) | NO | |
    | |
    | duration | int(11) | NO | | 0
    | |
    | billsec | int(11) | NO | | 0
    | |
    | disposition | varchar(45) | NO | |
    | |
    | amaflags | int(11) | NO | | 0
    | |
    | accountcode | varchar(20) | NO | |
    | |
    | uniqueid | varchar(32) | NO | |
    | |
    | userfield | varchar(255) | NO | |
    | |
    | peeraccount | varchar(20) | NO | |
    | |
    | linkedid | varchar(32) | NO | |
    | |
    | sequence | int(11) | NO | | 0
    | |
    +————-+———————–+——+—–+———————+—————-+
    20 rows in set (0.01 sec)

    Thank you!

    2013/6/4 Olivier

  • Are you using cdr_adaptive_odbc.conf to populate it? If so, there is no Asterisk analog to calldate. You would need an alias set up. Mine looks like:

    alias start => calldate

    so that the start of my call is what gets logged to the database as the calldate.

    Kevin Larsen

    From: Jairo
    To: Asterisk Users Mailing List – Non-Commercial Discussion
    , Date: 06/11/2013 08:28 AM
    Subject: Re: [asterisk-users] Is uniqueid/sequence a safe CDR table primary key ?
    Sent by: asterisk-users-bounces@lists.digium.com

    Hello,

    Still about CDR and MySQL table, should the calldate field be inserted by Asterisk?

    This is the table structure we are using, based on Asterisk wiki:

    mysql> describe cdr;
    +————-+———————–+——+—–+———————+—————-+
    | Field | Type | Null | Key | Default |
    Extra |
    +————-+———————–+——+—–+———————+—————-+
    | id | mediumint(8) unsigned | NO | PRI | NULL |
    auto_increment |
    | calldate | datetime | NO | | 0000-00-00 00:00:00
    | |
    | clid | varchar(80) | NO | |
    | |
    | src | varchar(80) | NO | |
    | |
    | dst | varchar(80) | NO | |
    | |
    | dcontext | varchar(80) | NO | |
    | |
    | channel | varchar(80) | NO | |
    | |
    | dstchannel | varchar(80) | NO | |
    | |
    | lastapp | varchar(80) | NO | |
    | |
    | lastdata | varchar(80) | NO | |
    | |
    | duration | int(11) | NO | | 0
    | |
    | billsec | int(11) | NO | | 0
    | |
    | disposition | varchar(45) | NO | |
    | |
    | amaflags | int(11) | NO | | 0
    | |
    | accountcode | varchar(20) | NO | |
    | |
    | uniqueid | varchar(32) | NO | |
    | |
    | userfield | varchar(255) | NO | |
    | |
    | peeraccount | varchar(20) | NO | |
    | |
    | linkedid | varchar(32) | NO | |
    | |
    | sequence | int(11) | NO | | 0
    | |
    +————-+———————–+——+—–+———————+—————-+
    20 rows in set (0.01 sec)

    Thank you!

    2013/6/4 Olivier
    OK, then I’ll go with linkedid, uniqueid and sequence number.

    Thanks for sharing this on this list

    2013/6/3 Matthew Jordan
    wrong.

    Possibly. Things to keep in mind:

    * You can run into uniqueid collisions across multiple systems if you do not specify a system name in asterisk.conf or do not specify a unique system name in asterisk.conf.
    * You can run into uniqueid collisions if your system clock goes backwards for any reason (the uniqueid for a channel happens to use a timestamp for its uniqueness)

    Whether or not this is unique enough will be completely dependent on your overall system configuration.

    In general, the recommended combination that *should* uniquely specify a CDR (when configured correctly) is linkedid (which should be enabled and added to your schema), uniqueid, and sequence number, with the asterisk system name specified.

  • Yes, using cdr_adaptive_odbc.conf.

    As it is a new table, just changed the name from calldate to start and now it is inserting the field ok.

    Thank you very much for your help.

    Best.

    2013/6/11 Kevin Larsen