26 Jan
2024
26 Jan
'24
10:26 a.m.
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.
--
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
______________________________________________________
26 Jan
26 Jan
2:42 p.m.
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
--
Henning Westerholt – https://skalatan.de/blog/
Kamailio services – https://gilawa.com
-----Original Message-----
From: Benoît Panizzon via sr-users <sr-users(a)lists.kamailio.org>
Sent: Freitag, 26. Januar 2024 08:27
To: sr-users(a)lists.kamailio.org
Cc: Benoît Panizzon <benoit.panizzon(a)imp.ch>
Subject: [SR-Users] usrloc database duplicate keys issue
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.
--
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(a)lists.kamailio.org
Important: keep the mailing list in the recipients, do not reply only to the sender!
Edit mailing list options or unsubscribe:
4:06 p.m.
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
______________________________________________________
4:52 p.m.
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(a)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(a)lists.kamailio.org
Important: keep the mailing list in the recipients, do not reply only to the sender!
Edit mailing list options or unsubscribe:
5:50 p.m.
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!
--
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
______________________________________________________
27 Jan
27 Jan
2:46 p.m.
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_ins…
Cheers,
Henning
-----Original Message-----
From: Benoît Panizzon <benoit.panizzon(a)imp.ch>
Sent: Freitag, 26. Januar 2024 14:07
To: Henning Westerholt <hw(a)gilawa.com>
Cc: Kamailio (SER) - Users Mailing List <sr-users(a)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
______________________________________________________
29 Jan
29 Jan
11:35 a.m.
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_ins…
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-
--
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
______________________________________________________
298
days inactive
301
days old
6 comments
3 participants
participants (3)
-
Benoît Panizzon -
Fred Posner -
Henning Westerholt