[sr-dev] [tracker] Task opened: usrloc driver error on query: Duplicate entry 'XXXXXXXXXXXX' for key 'ruid_idx'
sip-router
bugtracker at sip-router.org
Fri Jul 4 11:26:34 CEST 2014
THIS IS AN AUTOMATED MESSAGE, DO NOT REPLY.
A new Flyspray task has been opened. Details are below.
User who did this - Savolainen Dmitri (snen)
Attached to Project - sip-router
Summary - usrloc driver error on query: Duplicate entry 'XXXXXXXXXXXX' for key 'ruid_idx'
Task Type - Bug Report
Category - usrloc (k)
Status - Unconfirmed
Assigned To -
Operating System - Linux
Severity - Medium
Priority - Normal
Reported Version - 4.1
Due in Version - Undecided
Due Date - Undecided
Details - getting error ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'XXXXXXXXXXXX' for key 'ruid_idx'
Getting error expample
usloc params:
<code>
modparam('usrloc', 'db_mode', 2)
modparam("usrloc", "db_check_update", 1)
modparam("usrloc", "db_ops_ruid", 1)
modparam("usrloc", "timer_interval", 120) #default 60
modparam('usrloc', 'db_url', 'mysql:XXXXXXXXXXXXXXXXX')
</code>
now let's manipulate with user 1111
1. 1111 doesn't exist
<code>
mysql>select * from location where username='1111' \G
Empty set (0.00 sec)
</code>
2. add permanent location for 1111
./kamctl ul add '1111 at erinaco.ru' 'sip:1111 at 192.168.10.100'
3. 1111 is in DB after some seconds (up to "timer_interval")
<code>
mysql>select * from location where username='1111' \G
*************************** 1. row ***************************
id: 111625
ruid: ulcx-53b55b8f-46d1-1
username: 1111
domain: erinaco.ru
contact: sip:1111 at 192.168.10.100
received: NULL
path: NULL
expires: 1970-01-01 03:00:00
q: 1.00
callid: dfjrewr12386fd6-343 at kamailio.mi
cseq: 1
last_modified: 1970-01-01 03:00:00
flags: 0
cflags: 0
user_agent: SIP Router MI Server
socket: NULL
methods: NULL
instance: NULL
reg_id: 0
</code>
4. let's execute this again
./kamctl ul add '1111 at erinaco.ru' 'sip:1111 at 192.168.10.100'
There is no any changes in db after "timer_interval"
<code>
mysql>select * from location where username='1111' \G
*************************** 1. row ***************************
id: 111626
ruid: ulcx-53b55f26-4895-1
username: 1111
domain: erinaco.ru
contact: sip:1111 at 192.168.10.100
received: NULL
path: NULL
expires: 1970-01-01 03:00:00
q: 1.00
callid: dfjrewr12386fd6-343 at kamailio.mi
cseq: 1
last_modified: 1970-01-01 03:00:00
flags: 0
cflags: 0
user_agent: SIP Router MI Server
socket: NULL
methods: NULL
instance: NULL
reg_id: 0
1 row in set (0.00 sec)
</code>
Now, every "timer_interval" seconds in kamailio.log
<code>
[snen at sw4 sbin]# grep 'ulcx-53b55f26-4895-1' /var/log/kamailio.log
Jul 3 17:52:22 sw4 ./kamailio[18576]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'ulcx-53b55f26-4895-1' for key 'ruid_idx'
Jul 3 17:54:22 sw4 ./kamailio[18576]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'ulcx-53b55f26-4895-1' for key 'ruid_idx'
Jul 3 17:56:22 sw4 ./kamailio[18576]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'ulcx-53b55f26-4895-1' for key 'ruid_idx'
Jul 3 17:58:22 sw4 ./kamailio[18576]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'ulcx-53b55f26-4895-1' for key 'ruid_idx'
</code>
The reason is "modparam("usrloc", "db_check_update", 1)": affected_rows after UPDATE returns 0, and INSERT returns permanent error.
Record was marked as CS_DIRTY and will be never CS_SYNC up to kamailio reboot or registration change(that may be never for permanent registrations)
so we can see increasing the number of such records in the work process for some usage configuration.
some fixes:
* set last_modified to NOW() while updating (bad for simultaneous updates)
* "INSERT IGNORE" as usrloc module param
* do not perform DB changes, if there is no "in memory" changes (may be bad in external DB update case)
* mark record as CS_SYNC after affected_rows 0 and INSERT error, so error will be only once
in this patch INSERT error ignored after UPDATE and affected_rows 0
<code>
diff --git a/modules/usrloc/ucontact.c b/modules/usrloc/ucontact.c
index b18ce98..ad71b34 100644
--- a/modules/usrloc/ucontact.c
+++ b/modules/usrloc/ucontact.c
@@ -820,9 +820,8 @@ int db_update_ucontact_addr(ucontact_t* _c)
/* supposed to be an UPDATE, but if affected rows is 0, then try
* to do an INSERT */
if(ul_dbf.affected_rows(ul_dbh)==0) {
- LM_DBG("affected rows by UPDATE was 0, doing an INSERT\n");
- if(db_insert_ucontact(_c)<0)
- return -1;
+ LM_DBG("affected rows by UPDATE was 0, doing an INSERT without error check\n");
+ db_insert_ucontact(_c);
}
}
/* delete old db attrs and add the current list */
@@ -1006,9 +1005,8 @@ int db_update_ucontact_ruid(ucontact_t* _c)
/* supposed to be an UPDATE, but if affected rows is 0, then try
* to do an INSERT */
if(ul_dbf.affected_rows(ul_dbh)==0) {
- LM_DBG("affected rows by UPDATE was 0, doing an INSERT\n");
- if(db_insert_ucontact(_c)<0)
- return -1;
+ LM_DBG("affected rows by UPDATE was 0, doing an INSERT without error check\n");
+ db_insert_ucontact(_c);
}
}
@@ -1226,9 +1224,8 @@ int db_update_ucontact_instance(ucontact_t* _c)
/* supposed to be an UPDATE, but if affected rows is 0, then try
* to do an INSERT */
if(ul_dbf.affected_rows(ul_dbh)==0) {
- LM_DBG("affected rows by UPDATE was 0, doing an INSERT\n");
- if(db_insert_ucontact(_c)<0)
- return -1;
+ LM_DBG("affected rows by UPDATE was 0, doing an INSERT without error check\n");
+ db_insert_ucontact(_c);
}
}
</code>
More information can be found at the following URL:
http://sip-router.org/tracker/index.php?do=details&task_id=448
You are receiving this message because you have requested it from the Flyspray bugtracking system. If you did not expect this message or don't want to receive mails in future, you can change your notification settings at the URL shown above.
More information about the sr-dev
mailing list