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@erinaco.ru' 'sip:1111@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@192.168.10.100 received: NULL path: NULL expires: 1970-01-01 03:00:00 q: 1.00 callid: dfjrewr12386fd6-343@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@erinaco.ru' 'sip:1111@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@192.168.10.100 received: NULL path: NULL expires: 1970-01-01 03:00:00 q: 1.00 callid: dfjrewr12386fd6-343@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@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.