Res_odbc.c: SQL Execute returned an error: 23000: Duplicate entry for key 'PRIMARY'

Distro Discussion & Help
1

I’m getting lots of those
res_odbc.c: SQL Execute returned an error: 23000: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.56-MariaDB]Duplicate entry ‘1537285440.4841’ for key ‘PRIMARY’

and
res_odbc.c: SQL Execute error -1!
followed by
cdr_adaptive_odbc.c: cdr_adaptive_odbc: Insert failed on ‘asteriskcdrdb:cdr’. CDR failed: INSERT INTO cdr (calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, uniqueid, recordingfile, cnum, cnam, linkedid, sequence) VALUES ({ ts ‘2018-09-18 18:44:07’ }, ‘“M0:Sales:” <3410721395>’, ‘3410721395’, ‘414’, ‘ext-local’, ‘Local/414@from-queue-00000797;2’, ‘SIP/413-000003b6’, ‘Dial’, ‘SIP/414,HhtrM(auto-blkvm)Ib(func-apply-sipheaders^s^1)’, 350, 350, ‘ANSWERED’, 3, ‘1537285440.4841’, ‘in-3106981831-3410721395-20180918-184336-1537285416.4838.gsm’, ‘3410721395’, ‘Sales:’, ‘1537285416.4838’, 7040)

I have also tried mysqlcheck with no issues.
Searching via cdr reports for the unique id I do get entries, so SOME are accepted and some are not.
Any ideas on what to look for appreciated

This is a default sng7 install, with nothing special on cdr configuration
OS: sangoma Kernel: x86_64 Linux 3.10.0-862.9.1.el7.x86_64
FreePBX 14.0.3.13
odbc show

ODBC DSN Settings

Name: asteriskcdrdb
DSN: MySQL-asteriskcdrdb
Number of active connections: 1 (out of 5)

cdr show status

Call Detail Record (CDR) settings

Logging: Enabled
Mode: Simple
Log unanswered calls: No
Log congestion: No

  • Registered Backends

    Adaptive ODBC

contents of /etc/asterisk/cdr_adaptive_odbc.conf
[asteriskcdrdb]
connection=asteriskcdrdb
loguniqueid=yes
table=cdr
alias start => calldate

cel | CREATE TABLE cel (
id int(11) NOT NULL AUTO_INCREMENT,
eventtype varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
eventtime datetime NOT NULL,
cid_name varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_num varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_ani varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_rdnis varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
cid_dnid varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
exten varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
context varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
channame varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
appname varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
appdata varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
amaflags int(11) NOT NULL,
accountcode varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
uniqueid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
linkedid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
peer varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
userdeftype varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
extra varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
KEY uniqueid_index (uniqueid),
KEY linkedid_index (linkedid),
KEY context_index (context)
) ENGINE=InnoDB AUTO_INCREMENT=2202933 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Regards

2

I’ve seen these errors before, and they’ve always been caused by some oddball Asternic Reports MySQL TRIGGER.

3

Spot on!
There is a trigger on table cdr
from change log it says

  • Added MySQL trigger for non session channel insertion acdrunichan
    whatever that might mean.
    I have asked asternic for input on the situation.

Thanks for the heads up.
I’ll report on the outcome.

DROP TRIGGER IF EXISTSsimp_channel;CREATE DEFINER=root@localhostTRIGGERsimp_channelAFTER INSERT ONcdr` FOR EACH ROW BEGIN

SET @orichn = ‘’; SET @dstchn = ‘’;

SELECT SUBSTRING(NEW.channel,1,LENGTH(NEW.channel)-LOCATE("-",REVERSE(NEW.channel))) INTO @orichn;
SELECT SUBSTRING(NEW.dstchannel,1,LENGTH(NEW.dstchannel)-LOCATE("-",REVERSE(NEW.dstchannel))) INTO @dstchn;

IF @dstchan LIKE ‘Local/FM%’ THEN

SELECT CONCAT(‘SIP/’,SPLIT_STR(REPLACE(REPLACE(dstchannel,’#’,’-’),’@’,’-’),’-’,2)) INTO @dstchn;
INSERT INTO acdrunichan VALUES (NEW.uniqueid,@orichn,@dstchn);

ELSE

INSERT INTO acdrunichan VALUES (NEW.uniqueid,@orichn,@dstchn);

END IF;
END`

4

Hello, stom! Any feedback from Asternic on this issue? Have you managed to resolve the error?

5

Asternic seems to be unresponsive in general.
Have changed the trigger action from insert to update, effectively disabling the trigger, and the error stopped.
I have no idea why this trigger is needed, and what is missed if it is disabled.

6

This is potentially going to cause a different error. Instead of “update”, use “replace”. This way, if the record doesn’t exist, it will be created instead of throwing a different error (e.g., “Record does not exist”).

7

Can you tell me where (what file) I change this?

8

Well, trigger event on update would fire if the cdr is updated.
However cdr’s dont seem to to be ever updated.
And btw availabe events are limited to update, insert, delete. There is no trigger replace event.

I have used phpmyadmin
database is asteriskcdrdb
table cdr
triiger name is simp_channel
changed event to update

9

FYI asternic runs the issabel project. You might find him over there these days

2 Likes
10

Thanks for the tip, but my spanish needs polishing.

And there is no point chasing anyone
Its a good pointer on where (not) to spend money too.

11

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.