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 '[email protected]' 'sip:[email protected]'
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:[email protected]
received: NULL
path: NULL
expires: 1970-01-01 03:00:00
q: 1.00
callid: [email protected]
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 '[email protected]' 'sip:[email protected]'
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:[email protected]
received: NULL
path: NULL
expires: 1970-01-01 03:00:00
q: 1.00
callid: [email protected]
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@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.
_______________________________________________
sr-dev mailing list
[email protected]
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
