Hi
I have come across an issue with the usrloc database on kamailio 5.6 and 5.7
Jan 26 07:19:45 dev-cpereg01 kamailio[2708456]: ERROR: <core> [db_query.c:244]: db_do_insert_cmd(): error while submitting query Jan 26 07:19:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [ucontact.c:686]: db_insert_ucontact(): inserting contact in db failed somedudetp0216 (uloc-65b237f8-28983d-1) Jan 26 07:19:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [urecord.c:419]: wb_timer(): inserting contact into database failed (aor: somedudetp0216)
settings:
# ----- usrloc params ----- modparam("usrloc", "db_url", DBLOCAL) modparam("usrloc", "db_mode", 2) modparam("usrloc", "use_domain", 0)
modparam("usrloc", "ka_mode", 0) modparam("usrloc", "ka_filter", 1) modparam("usrloc", "timer_procs", 1) modparam("usrloc", "ka_from", "sip:ping@sip.imp.ch") modparam("usrloc", "server_id_filter", 1)
# Use DMQ to sync userloc loadmodule "dmq_usrloc.so" # Replicate usrloc via dmq
# Enable userloc sync modparam("dmq_usrloc", "enable", 1)
# Do a full sync on start modparam("dmq_usrloc", "sync", 1)
I assume, when kamailio is being restartet there are some expired entries still present in the database which kamailio tries to insert on a new registration. This leads to this duplicate key error.
Is there a way to tell kamailio to use 'replace into' instread of 'insert into'?
Solution for now, is to manually delete expired entries from the database.
Hello,
are there more specific errors e.g. from the db_mysql module? Otherwise enable query logging or checkout the DB error log might help you here.
If you run Kamailio in debug mode you will also see all the queries on that side.
Cheers,
Henning
Hi Henning
Missed the first line with the specific issue:
Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-65b237f8-28983d-1' for key 'location.ruid_idx' (1062) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: <core> [db_query.c:244]: db_do_insert_cmd(): error while submitting query Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [ucontact.c:686]: db_insert_ucontact(): inserting contact in db failed somedudetp0216 (uloc-65b237f8-28983d-1) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [urecord.c:419]: wb_timer(): inserting contact into database failed (aor: somedudetp0216)
Did you upgrade the database (assuming an upgrade from pre-5.6):
https://www.kamailio.org/wikidocs/install/upgrade/5.5.x-to-5.6.0/
Regards,
Fred Posner p: +1 (352) 664-3733
On Jan 26, 2024, at 8:06 AM, Benoît Panizzon via sr-users sr-users@lists.kamailio.org wrote:
Hi Henning
Missed the first line with the specific issue:
Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-65b237f8-28983d-1' for key 'location.ruid_idx' (1062) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: <core> [db_query.c:244]: db_do_insert_cmd(): error while submitting query Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [ucontact.c:686]: db_insert_ucontact(): inserting contact in db failed somedudetp0216 (uloc-65b237f8-28983d-1) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [urecord.c:419]: wb_timer(): inserting contact into database failed (aor: somedudetp0216)
-- Mit freundlichen Grüssen
-Benoît Panizzon- @ HomeOffice und normal erreichbar
I m p r o W a r e A G - Leiter Commerce Kunden ______________________________________________________
Zurlindenstrasse 29 Tel +41 61 826 93 00 CH-4133 Pratteln Fax +41 61 826 93 01 Schweiz Web http://www.imp.ch ______________________________________________________ __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions To unsubscribe send an email to sr-users-leave@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
Hi Fred
Did you upgrade the database (assuming an upgrade from pre-5.6):
https://www.kamailio.org/wikidocs/install/upgrade/5.5.x-to-5.6.0/
Yes I did. I even remember reading this article.
That is the only change, right?
-- add index on connection_id for usrloc module location table CREATE INDEX tcpcon_idx ON location (`connection_id`);
show create table location:
CREATE TABLE `location` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ruid` varchar(64) NOT NULL DEFAULT '', `username` varchar(64) NOT NULL DEFAULT '', `domain` varchar(64) DEFAULT NULL, `contact` varchar(512) NOT NULL DEFAULT '', `received` varchar(128) DEFAULT NULL, `path` varchar(512) DEFAULT NULL, `expires` datetime NOT NULL DEFAULT '2030-05-28 21:32:15', `q` float(10,2) NOT NULL DEFAULT '1.00', `callid` varchar(255) NOT NULL DEFAULT 'Default-Call-ID', `cseq` int NOT NULL DEFAULT '1', `last_modified` datetime NOT NULL DEFAULT '2000-01-01 00:00:01', `flags` int NOT NULL DEFAULT '0', `cflags` int NOT NULL DEFAULT '0', `user_agent` varchar(255) NOT NULL DEFAULT '', `socket` varchar(64) DEFAULT NULL, `methods` int DEFAULT NULL, `instance` varchar(255) DEFAULT NULL, `reg_id` int NOT NULL DEFAULT '0', `server_id` int NOT NULL DEFAULT '0', `connection_id` int NOT NULL DEFAULT '0', `keepalive` int NOT NULL DEFAULT '0', `partition` int NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `ruid_idx` (`ruid`), KEY `account_contact_idx` (`username`,`domain`,`contact`), KEY `expires_idx` (`expires`), KEY `connection_idx` (`server_id`,`connection_id`), KEY `tcpcon_idx` (`connection_id`) ) ENGINE=InnoDB
Yes, that key is present!
Hello Benoit,
somehow the usrloc internal record id got duplicated. Are you seeing this error frequently or just on some occasions, like the restart you mentioned? You can try to activate this parameter: https://kamailio.org/docs/modules/5.7.x/modules/usrloc.html#usrloc.p.db_inse...
Cheers,
Henning
-----Original Message----- From: Benoît Panizzon benoit.panizzon@imp.ch Sent: Freitag, 26. Januar 2024 14:07 To: Henning Westerholt hw@gilawa.com Cc: Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org Subject: Re: [SR-Users] usrloc database duplicate keys issue
Hi Henning
Missed the first line with the specific issue:
Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-65b237f8-28983d-1' for key 'location.ruid_idx' (1062) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: <core> [db_query.c:244]: db_do_insert_cmd(): error while submitting query Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [ucontact.c:686]: db_insert_ucontact(): inserting contact in db failed somedudetp0216 (uloc- 65b237f8-28983d-1) Jan 26 07:27:45 dev-cpereg01 kamailio[2708456]: ERROR: usrloc [urecord.c:419]: wb_timer(): inserting contact into database failed (aor: somedudetp0216)
-- Mit freundlichen Grüssen
-Benoît Panizzon- @ HomeOffice und normal erreichbar
I m p r o W a r e A G - Leiter Commerce Kunden ______________________________________________________
Zurlindenstrasse 29 Tel +41 61 826 93 00 CH-4133 Pratteln Fax +41 61 826 93 01 Schweiz Web http://www.imp.ch ______________________________________________________
Hi Henning
somehow the usrloc internal record id got duplicated. Are you seeing this error frequently or just on some occasions, like the restart you mentioned? You can try to activate this parameter: https://kamailio.org/docs/modules/5.7.x/modules/usrloc.html#usrloc.p.db_inse...
It only happens when restarting. I fear, there is some racing condition when kamailio initially reads in the database and clients re-register at the same time. Then the problem persists, and constantly generates error entries in the log, until I delete the eventually 'expired' entries from the database manually.
Dank you for pointing out: db_insert_update, I missed that option, that sounds like the solution I was looking for.
Mit freundlichen Grüssen
-Benoît Panizzon-
-
Benoît Panizzon -
Fred Posner -
Henning Westerholt