#58 Duplicate entries in the 'calls' table

[Patrick L. Lange]

The calls table contains duplicate entries. I found out about it when I looked at the
completeCalls table. At least one duplicate is:

SELECT * from completeCalls WHERE callId='d91d94896edb32250af8f7090c825b4a@141.31.8.62';

[David Suendermann-Oeft]

Of presently 341 calls, in the calls table, these are the duplicates:

| c8f88d5373b5deab3c2e0e3c8e10fcc2@141.31.8.62 | 3 |
| e9fe69f0461216715767be8cabbbbff7@141.31.8.62 | 3 |
| ebd7a26632d423e2d4e6b2ce78e64cd2@141.31.8.62 | 2 |
| d5fbf6aeefe97e030f5e2c0226360f0f@141.31.8.62 | 2 |
| d91d94896edb32250af8f7090c825b4a@141.31.8.62 | 2 |

select callId, count(1) from calls group by callId order by count(1) desc;

[David Suendermann-Oeft]

The call with the cairoCallId d91d94896edb32250af8f7090c825b4a@141.31.8.62 was duplicated due to a glitch in the view jvxmlCairoCallId which was not prepared for the (strange!) situation that a single jvxmlCallId (jvxmlCairoCallId) wrapped two cairoCallIds (e99b9ef45b0a707b666c31fded58aa7d@141.31.8.62 and d91d94896edb32250af8f7090c825b4a@141.31.8.62). However, after fixing this, there are still some duplicates:

| c8f88d5373b5deab3c2e0e3c8e10fcc2@141.31.8.62 | 2 |
| ebd7a26632d423e2d4e6b2ce78e64cd2@141.31.8.62 | 2 |
| e9fe69f0461216715767be8cabbbbff7@141.31.8.62 | 2 |
| d5fbf6aeefe97e030f5e2c0226360f0f@141.31.8.62 | 2 |

[David Suendermann-Oeft]

Another cause of the duplication seem to be calls whose JVXML SIP call embraces another JVXML SIP call such as in the following example:

select id from haleflogsmin where message like '%2ae97aba68cd31300bf809107aebba69@141.31.8.161:5060%';
| 2394741 |
| 2394971 |
| 2398696 |
select id from haleflogsmin where message like '%0cbc06545a686b7c233e68600fb60208@141.31.8.161:5060%';
| 2396846 |
| 2397077 |
| 2397609 |

Here, 2ae97aba68cd31300bf809107aebba69's Sending a BYE SIP Request was for some reason much delayed (2015-06-29 22:51:25), after Call 0cbc06545a686b7c233e68600fb60208 had already started in 2015-06-29 22:50:54.

[David Suendermann-Oeft]

Some (heuristic) thresholds were implemented in the views jvxmlCairoCallId and jvxmlCairoAsteriskCallId making the duplication go away. The root cause is strange SIP message behavior making the association between JVXML and Cairo sessions ambiguous at times. Right now, there are no duplicates. It is however possible that some calls do not make it into the calls table at all in case they do not fulfill the above thresholds. There are 333 calls in the calls table right now.

[Vikram Ramanarayanan]

The following calls have a different ID but are in some sense duplicates:

| 498ff94cf56abdc69d45c10a58d4034c@141.31.8.62 | 141.31.8.62 | HALEF_MixMonitor_audio_ext_2222_07082015_233947.wav | 2015-08-07 17:41:51 | 134 | 2222 | 5372 |
| beddc2886ed92658dc2b349952e35675@141.31.8.61 | 141.31.8.61 | HALEF_MixMonitor_audio_ext_2222_07082015_233947.wav | 2015-08-07 17:41:51 | 134 | 2222 | 5372 |

[David Suendermann-Oeft]

Here is a DB excerpt of these two calls:

| 4379898 | 4391774 | 10f33b1e6d89e141015d6c9b48787dd7@64.40.49.128 | 021565832baacc7b2574c39c02a5bfac@141.31.8.161:5060 | 498ff94cf56abdc69d45c10a58d4034c@141.31.8.62 | 141.31.8.62 | 141.31.8.60 | f2116082-06fb-401f-8465-6a6ce455107b | HALEF_MixMonitor_audio_ext_2222_07082015_233947.wav | 2015-08-07 17:41:51 | 134 | 2222 |

A | 4379887 |
J | 4379887 |
J | 4380130 |
J | 4391747 |
J | 4391778 |
C | 4379897 |
C | 4379898 |
C | 4379928 |
C | 4391770 |
C | 4391774 |

| 4379899 | 4394719 | 10f33b1e6d89e141015d6c9b48787dd7@64.40.49.128 | 021565832baacc7b2574c39c02a5bfac@141.31.8.161:5060 | beddc2886ed92658dc2b349952e35675@141.31.8.61 | 141.31.8.62 | 141.31.8.99 | f2116082-06fb-401f-8465-6a6ce455107b | HALEF_MixMonitor_audio_ext_2222_07082015_233947.wav | 2015-08-07 17:41:51 | 134 | 2222 |

C | 4379899 |
C | 4380579 |
C | 4394719 |

[David Suendermann-Oeft]

So, it looks as if the problem could have been avoided by enforcing the one-to-one relationship between JVXML and Cairo. To do this, I created the view jvxmlCairoLink which will need to be build into jvxmlCairoCallIdExt and jvxmlCairoCallId later.

[David Suendermann-Oeft]

Here is another one: 0458c17696a3ab872159138f3b2787fd@141.31.8.62

[David Suendermann-Oeft]

These are the calls in jvxmlCairoCallId with currently more than one entry per cairoCallId

+----------------------------------------------+-----+
| cairoCallId | cnt |
+----------------------------------------------+-----+
| 0458c17696a3ab872159138f3b2787fd@141.31.8.62 | 2 |
| 04fb5a2476f84f52a95e26349bfe1694@141.31.8.62 | 2 |
| 07f706626c5599beea25c5382827c435@141.31.8.62 | 3 |
| 13bc99452ea10aaf739511dd855f0fec@141.31.8.74 | 2 |
| 175bab77f62ce3981d95163e6eaf7637@141.31.8.62 | 3 |
| 27713c9f3015bf14a2bfb9d20f7ecf72@141.31.8.62 | 3 |
| 2f99c29a627464a4b13ecb70f60aa5d7@141.31.8.62 | 3 |
| 4b488299afbd30deca2267a55b4c8308@141.31.8.62 | 3 |
| 7319c6921bd4f21140a4d39803ddda3c@141.31.8.62 | 3 |
| 8d4ad3035a5b7d0fea11215191d35505@141.31.8.74 | 3 |
| afff360f3728b6d96590d917fa4a122b@141.31.8.74 | 3 |
| cf10a4a014a1e53a9096baf0f0191daf@141.31.8.62 | 3 |
| cf485766173165147bc424bbc6e3bf2f@141.31.8.62 | 3 |
| dda0814103c555a8bc18e76922d1aa40@141.31.8.62 | 2 |
| ea6ef545546376ed2ae7e99e72f6bfd3@141.31.8.74 | 3 |
+----------------------------------------------+-----+

select * from (select cairoCallId, count(1) cnt from jvxmlCairoCallId group by cairoCallId) a where cnt > 1;

[David Suendermann-Oeft]

Turns out that for callID 07f706626c5599beea25c5382827c435@141.31.8.62 there are already two entries in cairoCallId, but associated with two machineIPs (one if which is [62] is actually a JVXML server)!

+---------+---------+----------------------------------------------+---------------+-----+-------------+
| minId | maxId | callId | isCairoCallId | cnt | machineIp |
+---------+---------+----------------------------------------------+---------------+-----+-------------+
| 3766744 | 3771986 | 07f706626c5599beea25c5382827c435@141.31.8.62 | 1 | 5 | 141.31.8.62 |
| 3766745 | 3771959 | 07f706626c5599beea25c5382827c435@141.31.8.62 | 1 | 6 | 141.31.8.60 |
+---------+---------+----------------------------------------------+---------------+-----+-------------+

select * from cairoCallId where callId = '07f706626c5599beea25c5382827c435@141.31.8.62';

[David Suendermann-Oeft]

After fixing something, there are still two JVXML call IDs asoociated with this call:

+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+
| minId | maxId | asteriskCallId | jvxmlCallId | cairoCallId | jvxmlMachineIp | cairoMachineIp |
+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+
| 3766745 | 3776753 | 466a621a09e55c7c0a31d0bf62b7443f@64.40.49.128 | 5f71eb492d917c9f7edb5d7734411cec@141.31.8.161:5060 | 07f706626c5599beea25c5382827c435@141.31.8.62 | 141.31.8.74 | 141.31.8.60 |
| 3766745 | 3771984 | 7a1180502f07f5c42617587f0ad1b3d5@64.40.49.128 | 1e334bc4742c45b723845d8366f069b6@141.31.8.161:5060 | 07f706626c5599beea25c5382827c435@141.31.8.62 | 141.31.8.62 | 141.31.8.60 |
+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+

select * from jvxmlCairoCallId where cairoCallId = '07f706626c5599beea25c5382827c435@141.31.8.62';

[David Suendermann-Oeft]

This is way shorter now:

+----------------------------------------------+-----+
| cairoCallId | cnt |
+----------------------------------------------+-----+
| 0458c17696a3ab872159138f3b2787fd@141.31.8.62 | 2 |
| 04fb5a2476f84f52a95e26349bfe1694@141.31.8.62 | 2 |
| 13bc99452ea10aaf739511dd855f0fec@141.31.8.74 | 2 |
| dda0814103c555a8bc18e76922d1aa40@141.31.8.62 | 2 |
+----------------------------------------------+-----+

select * from (select cairoCallId, count(1) cnt from jvxmlCairoCallId group by cairoCallId) a where cnt > 1;

[David Suendermann-Oeft]

Still multiple JVXML call IDs for one cairo call ID:

+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+
| minId | maxId | asteriskCallId | jvxmlCallId | cairoCallId | jvxmlMachineIp | cairoMachineIp |
+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+
| 4550912 | 4551000 | 090fc97d2d4ca09535a3bee21e0d61a3@64.40.49.128 | 1297b98a4b0e7a71119a0b15680c5257@141.31.8.161:5060 | 0458c17696a3ab872159138f3b2787fd@141.31.8.62 | 141.31.8.62 | 141.31.8.60 |
| 4550912 | 4561543 | 6a794d0400422a8e05a39ed715349a8f@64.40.49.128 | 711ec0b70e2594bd604dfb3e39dec9b0@141.31.8.161:5060 | 0458c17696a3ab872159138f3b2787fd@141.31.8.62 | 141.31.8.62 | 141.31.8.60 |
+---------+---------+-----------------------------------------------+----------------------------------------------------+----------------------------------------------+----------------+----------------+

select * from jvxmlCairoCallId where cairoCallId = '0458c17696a3ab872159138f3b2787fd@141.31.8.62';

[David Suendermann-Oeft]

Overlap of JVXML logs:

+---------+---------+----------------------------------------------------+---------------+-----+-------------+
| minId | maxId | callId | isCairoCallId | cnt | machineIp |
+---------+---------+----------------------------------------------------+---------------+-----+-------------+
| 4550066 | 4551000 | 1297b98a4b0e7a71119a0b15680c5257@141.31.8.161:5060 | 0 | 6 | 141.31.8.62 |
| 4550901 | 4561543 | 711ec0b70e2594bd604dfb3e39dec9b0@141.31.8.161:5060 | 0 | 8 | 141.31.8.62 |
+---------+---------+----------------------------------------------------+---------------+-----+-------------+

select * from cairoCallId where callId in ('1297b98a4b0e7a71119a0b15680c5257@141.31.8.161:5060', '711ec0b70e2594bd604dfb3e39dec9b0@141.31.8.161:5060');

[David Suendermann-Oeft]

This is due to overlapping SIP messages from multiple calls. on the same server.

[David Suendermann-Oeft]

+---------+---------+----------------------------------------------+
| minId | maxId | cairoCallId |
+---------+---------+----------------------------------------------+
| 3149380 | 3151126 | dda0814103c555a8bc18e76922d1aa40@141.31.8.62 |
| 3149380 | 3152188 | dda0814103c555a8bc18e76922d1aa40@141.31.8.62 |
| 3463193 | 3473648 | 13bc99452ea10aaf739511dd855f0fec@141.31.8.74 |
| 3463193 | 3464537 | 13bc99452ea10aaf739511dd855f0fec@141.31.8.74 |
| 4550912 | 4551000 | 0458c17696a3ab872159138f3b2787fd@141.31.8.62 |
| 4550912 | 4561543 | 0458c17696a3ab872159138f3b2787fd@141.31.8.62 |
| 4579065 | 4582856 | 04fb5a2476f84f52a95e26349bfe1694@141.31.8.62 |
| 4579065 | 4580370 | 04fb5a2476f84f52a95e26349bfe1694@141.31.8.62 |
+---------+---------+----------------------------------------------+

create table tmp1 (select * from (select cairoCallId, count(1) cnt from jvxmlCairoCallId group by cairoCallId) a where cnt > 1);
select minId, maxId, cairoCallId from jvxmlCairoCallId where cairoCallId in (select cairoCallId from tmp1);

[David Suendermann-Oeft]

It really looks like we have to live with this artifact since there is at times substantial overlap:

+---------+---------+----------------------------------------------------+---------------+-----+-------------+
| minId | maxId | callId | isCairoCallId | cnt | machineIp |
+---------+---------+----------------------------------------------------+---------------+-----+-------------+
| 3148530 | 3151126 | 22572a731af0fa894c12b0972454ec14@141.31.8.161:5060 | 0 | 10 | 141.31.8.62 |
| 3149369 | 3152188 | 0843884638c1f1b042005a8963e64ad9@141.31.8.161:5060 | 0 | 8 | 141.31.8.62 |
| 3462241 | 3473648 | 4bc26f92766f7f2936b7e6ed55469f2e@141.31.8.161:5060 | 0 | 6 | 141.31.8.74 |
| 3463182 | 3464537 | 7af081ed75f88618405cf43d32b807a5@141.31.8.161:5060 | 0 | 6 | 141.31.8.74 |
| 4550066 | 4551000 | 1297b98a4b0e7a71119a0b15680c5257@141.31.8.161:5060 | 0 | 6 | 141.31.8.62 |
| 4550901 | 4561543 | 711ec0b70e2594bd604dfb3e39dec9b0@141.31.8.161:5060 | 0 | 8 | 141.31.8.62 |
| 4579026 | 4582856 | 6dfd230751712e1415e809c704af482f@141.31.8.161:5060 | 0 | 6 | 141.31.8.62 |
| 4579052 | 4580370 | 66195a7a724e57ae387a2efa73961d80@141.31.8.161:5060 | 0 | 6 | 141.31.8.62 |
+---------+---------+----------------------------------------------------+---------------+-----+-------------+

create table tmp2 (select jvxmlCallId from jvxmlCairoCallId where cairoCallId in (select cairoCallId from tmp1));
select * from cairoCallId where callId in (select * from tmp2);