[SR-Users] Re: usrloc database duplicate keys issue
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_insert_update 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 PrattelnFax +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-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
[SR-Users] Re: usrloc database duplicate keys issue
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_insert_update Cheers, Henning > -Original Message- > From: Benoît Panizzon > Sent: Freitag, 26. Januar 2024 14:07 > To: Henning Westerholt > Cc: Kamailio (SER) - Users Mailing List > 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: > [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 PrattelnFax +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-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
[SR-Users] Re: usrloc database duplicate keys issue
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 PrattelnFax +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-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
[SR-Users] Re: usrloc database duplicate keys issue
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 > 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: > [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 PrattelnFax +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-le...@lists.kamailio.org > Important: keep the mailing list in the recipients, do not reply only to the > sender! > Edit mailing list options or unsubscribe: __ Kamailio - Users Mailing List - Non Commercial Discussions To unsubscribe send an email to sr-users-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
[SR-Users] Re: 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: [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 PrattelnFax +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-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
[SR-Users] Re: usrloc database duplicate keys issue
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 > Sent: Freitag, 26. Januar 2024 08:27 > To: sr-users@lists.kamailio.org > Cc: Benoît Panizzon > 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: > [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:p...@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 PrattelnFax +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-le...@lists.kamailio.org > Important: keep the mailing list in the recipients, do not reply only to the > sender! > Edit mailing list options or unsubscribe: __ Kamailio - Users Mailing List - Non Commercial Discussions To unsubscribe send an email to sr-users-le...@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: