Is the unqueid on vicidial_closer_log a primary key

[paolo]

Hi Everyone

We discovered that some uniqueids on the vicidail_closr_log table has several Lead_ids linked to it. My question is, is the uniqueid in the vicidial_closer_log table a primary key and should only be assigned to a single lead_id?

Code: Select all

select max(lead_id) as lead_id, uniqueid, count(*) from vicidial_closer_log where campaign_id='MEDIBCOPT' group by uniqueid having count(*)>1;

1278007   2455092   2008   MEDIBCOPT   2023-10-15 07:55:13   1697381713         QUEUE   1   9377178784   VDCL      N   0.00      0   NONE   1697381712.150      45   1
1278008   2455093   2008   MEDIBCOPT   2023-10-15 07:55:13   1697381713   1697385314   3600   TIMEOT   1   6065944353   VDCL      N   3600.00      0   QUEUETIMEOUT   1697381712.150      46   1
1278009   2455094   2008   MEDIBCOPT   2023-10-15 07:55:13   1697381713   1697384875   3162   DROP   1   5743202174   VDCL      N   3162.00      0   ABANDON   1697381712.150      47   1
                                                               


[williamconley]

NO.

UniqueID is merely what asterisk calls this ID. That description, however, is not literal. Multiple calls may have the same UniqueID over time.

[basha04]

By running

Code: Select all

show create table vicidial_closer_log;

the following result would be displayed

Code: Select all

CREATE TABLE `vicidial_closer_log` (
  `closecallid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `lead_id` bigint(20) unsigned NOT NULL,
  `list_id` bigint(14) unsigned DEFAULT NULL,
  `campaign_id` varchar(20) DEFAULT NULL,
  `call_date` datetime DEFAULT NULL,
  `start_epoch` int(10) unsigned DEFAULT NULL,
  `end_epoch` int(10) unsigned DEFAULT NULL,
  `length_in_sec` int(10) DEFAULT NULL,
  `status` varchar(6) DEFAULT NULL,
  `phone_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(18) DEFAULT NULL,
  `user` varchar(20) DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  `processed` enum('Y','N') DEFAULT NULL,
  `queue_seconds` decimal(7,2) DEFAULT 0.00,
  `user_group` varchar(20) DEFAULT NULL,
  `xfercallid` int(9) unsigned DEFAULT NULL,
  `term_reason` enum('CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','HOLDRECALLXFER','HOLDTIME','NOAGENT','NONE','MAXCALLS','ACFILTER','CLOSETIME') DEFAULT 'NONE',
  `uniqueid` varchar(20) NOT NULL DEFAULT '',
  `agent_only` varchar(20) DEFAULT '',
  `queue_position` smallint(4) unsigned DEFAULT 1,
  `called_count` smallint(5) unsigned DEFAULT 0,
  `insert_record_dt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`closecallid`),
  KEY `lead_id` (`lead_id`),
  KEY `call_date` (`call_date`),
  KEY `campaign_id` (`campaign_id`),
  KEY `uniqueid` (`uniqueid`),
  KEY `phone_number` (`phone_number`),
  KEY `date_user` (`call_date`,`user`),
  KEY `usr_dt_st` (`user`,`call_date`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=1033139 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

The primary key on vicidial_closer_log is the closecallid column. The lead_id is a primary key on vicidial_list table.

uniqueid it is supposed to be unique for each call, it's like a vin number on the cars. From my experience I have never seen two different calls have the same uniqueid, I guess it might be a problem if two different calls have the same uniqueid.
In simple words different uniqueids might have the same lead_id , but not the opposite. One phone_number which has a lead_id might have been called more than once , this means that the same lead_id have different uniqueid.


The scripts which inserts into vicidial_closer_log are:

agi-VDAD_ALL_inbound.agi --- > uniqueid is an asterisk channel parameter which is unique . See https://docs.asterisk.org/Configuration ... Variables/

vdc_db_query.php --- > uniqueid is $uniqueid=date("U").".".rand(1, 9999);
closer_dispo.php --- > does not insert an uniqueid value
admin_modify_lead.php --- > does not insert an uniqueid value
qc_modify_lead.php --- > does not insert an uniqueid value

[williamconley]

uniqueid it is supposed to be unique for each call, it's like a vin number on the cars.

Not true. It's meant to be unique "at that moment on that dialer" not like a vin number, which is unique among all vehicles in the country from the start of registering vehicles until now.

From my experience I have never seen two different calls have the same uniqueid, I guess it might be a problem if two different calls have the same uniqueid.

Two calls can have the same uniqueid, but not on the same server at the same time. Doesn't happen often, but does happen. Especially on larger clusters.

In simple words different uniqueids might have the same lead_id , but not the opposite.

I'm not sure where you were going with this, but uniqueid is time-based. Two calls with the same uniqueid can't happen at the same time on the same server, but can happen on different servers at the same exact time. Very Unlikely to be the same lead. Two calls to the same lead at the same time on two different servers would be counterproductive. LOL

So if two calls have the same uniqueid, they'll very likely be simultaneously generated on two servers and be for different leads.

But this does mean that attempting to use uniqueID as a primary key may never cause a problem ... or data may be lost and you may never know because the system will simply refuse to make the second record.

[basha04]

@williamconley thank you for correcting me.
I thought that uniqueid was indeed unique. This might be a problem if we combine two systems into one. By system I mean up and running vicidial cluster with more than 2-3 telephony servers.

[williamconley]

Which is precisely how I found out long ago. LOL.