Hello. We use 2 kamailio servers cluster and we have porblems with db. Database failed pecause of error:
Could not execute Write_rows_v1 event on table production.location; Duplicate entry 'uloc-54aae947-86d-a67' for key 'ruid_idx', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 380, Internal MariaDB error code: 1062
But a location table no row 'ruid_idx' and no entry uloc-54aae947-86d-a67.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30
i think, that you use modparam("usrloc", "db_check_update", 1) I have already reported on this issue and it was resolved for permanent registration, but dynamic is still problem. Set this parameter to "0" For more details you can read http://sip-router.org/tracker/index.php?do=details&task_id=448#comment15...
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-69738864
The problems in the issue you refer in the above comment to were related to static values for some fields (callid, cseq) when adding contacts via rpc/mi commands and that item was closed.
Can you provide more details here about what do you think is still an issue there?
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-69748458
from my post http://sip-router.org/tracker/index.php?do=details&task_id=448 <q> "Comment by Savolainen Dmitri (snen) - Friday, 04 July 2014, 12:55 GMT Yes, it is about adding the same record twice, but it doesn't matter via kamctl or via sip-endpoint. Just kamctl with permanent registration is more clearly, because there is no expire. Same update with expire will try to INSERT until expired (or adding not same record)" </q> You've fixed only MI part, but dynamic registration from endpoints in the usrloc record may be marked as CS_DIRTY with modparam("usrloc", "db_check_update", 1) and will be never CS_SYNC up to kamailio reboot or registration changed. So we can see multiple "Duplicate entry" message every <a href="http://www.kamailio.org/docs/modules/4.2.x/modules/usrloc.html#usrloc.p.timer_interval">timer_interval</a> period. You can see my fixing suggestions in the initial post of http://sip-router.org/tracker/index.php?do=details&task_id=448
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-69755233
I took the time to revise the old reported issue and I am having difficulty to understand what is left unfixed. There were some issue in matching the old records in database for db_update_ucontact_addr() in various mach modes, which should be fixed with the commit e8a795a6a413ae453f619e3deaf36c26b85b0077.
For db_mode_ruid=1, the matching is done on ruid column, thus db_update_ucontact_ruid() should result in one affected row if the same ruid value is found. I expect that at least expires and last_modifield fields will have different values.
Devel version has the option to set CLIENT_FOUND_ROWS to mysql connection to return matched rows instead of changed rows when doing UPDATE: * http://kamailio.org/docs/modules/devel/modules/db_mysql.html#db_mysql.p.upda...
Maybe that can be made default.
Otherwise, I am not sure what else needs to be fixed. If you still think there is an issue, add new details here, explaining the case again. From the old issue on sip-router.org tracker is nothing I found to be fixed anymore.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-88455530
It is match more better after fixing static registration. I have analyzed some remaining "Duplicate entry" errors. It happens after SQL reconnect at least.
1.Try to update via *db_update_ucontact_ruid* 2. Missed SQL connection and getting *usrloc [ucontact.c:1055]: db_update_ucontact_ruid(): updating database failed*. But **record was realy updated**. 3. Reconnect 4. Try to update record again and getting *ul_dbf.affected_rows(ul_dbh)==0* 5. Try to insert and getting "Duplicate entry" every *usrloc:timer_interval* period up to exipre or re-register
Realy, maybe it is not an error
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-90963367
I also saw today related error messages in syslog (below) running quite recent kamailio master. I have these related params set:
modparam("usrloc", "db_mode", 3) modparam("usrloc", "db_ops_ruid", 1) modparam("usrloc", "db_check_update", 1)
Based on the above discussion, it was not clear to me if this issue has been resolved or if I have some module parameters missing in config.
-- Juha
May 3 11:04:55 lohi /usr/bin/sip-proxy[2366]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-5545c4ea-94a-1' for key 'ruid_idx' May 3 11:04:55 lohi /usr/bin/sip-proxy[2366]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 3 11:04:55 lohi /usr/bin/sip-proxy[2366]: ERROR: usrloc [ucontact.c:668]: db_insert_ucontact(): inserting contact in db failed May 3 11:04:55 lohi /usr/bin/sip-proxy[2366]: ERROR: usrloc [ucontact.c:1665]: update_ucontact(): failed to update database May 3 11:04:55 lohi /usr/bin/sip-proxy[2366]: ERROR: registrar [save.c:761]: update_contacts(): failed to update contact
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-98454109
There is one more scenario for this error. Config with ``` modparam("usrloc", "db_ops_ruid", 1) modparam("usrloc", "db_mode", 2) ``` 1. location table hasn't some user "sipuser" 2. "sipuser" try to register 3. kamailio try to insert record 4. insertion failed due to timeout problem, but record was inserted 5. Try to insert and getting "Duplicate entry" every usrloc:timer_interval period up to * kamailio reboot * register expires * "sipuser" revoke registration (Expires: 0)
So "Duplicate entry" error can last a very long time for one record
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-157379444
Can you detail more about your next statement?
``` insertion failed due to timeout problem, but record was inserted ```
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-157711714
I use mysql as DB. In my mind sometimes ( in case of big query flow for example ), db_mysql:timeout_interval is to small for insertion
defalut mysql param ``` modparam("db_mysql", "timeout_interval", 2) ```
i have modparam("usrloc", "timer_interval", 120)
let's look at log: insertion failed
``` Nov 18 04:02:06 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Lost connection to MySQL server during query Nov 18 04:02:06 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query Nov 18 04:02:06 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [ucontact.c:629]: db_insert_ucontact(): inserting contact in db failed Nov 18 04:02:06 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [urecord.c:386]: wb_timer(): inserting contact into database failed (aor: 00041557) ````
but in mysql last_modified: **2015-11-18 03:59:59** ``` mysql> select username, last_modified from location where ruid='uloc-5624b05d-2b7c-fde6d7' \G *************************** 1. row *************************** username: 00041557 last_modified: 2015-11-18 03:59:59 1 row in set (0.00 sec) ```
first "Duplicate entry" after usrloc:timer_interval period ``` Nov 18 04:04:08 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-5624b05d-2b7c-fde6d7' for key 'ruid_idx' Nov 18 04:04:08 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query Nov 18 04:04:08 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [ucontact.c:629]: db_insert_ucontact(): inserting contact in db failed Nov 18 04:04:08 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [urecord.c:386]: wb_timer(): inserting contact into database failed (aor: 00041557) ```
repeat "Duplicate entry" every usrloc:timer_interval period for long time
``` Nov 18 19:19:07 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Duplicate entry 'uloc-5624b05d-2b7c-fde6d7' for key 'ruid_idx' Nov 18 19:19:07 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: <core> [db_query.c:235]: db_do_insert_cmd(): error while submitting query Nov 18 19:19:07 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [ucontact.c:629]: db_insert_ucontact(): inserting contact in db failed Nov 18 19:19:07 sip140 /usr/local/kamailio/sbin/kamailio[11136]: ERROR: usrloc [urecord.c:386]: wb_timer(): inserting contact into database failed (aor: 00041557) ```
i delete such records periadicaly from DB
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-157779206
I think this is not easy to track as long as the MySQL made the insert but the client reports an error for doing the insert. So mysql client says the insert didn't succeed, but the mysql server actually did it.
There can be added some self healing approaches:
* 1)add a timer function to delete expired records from database, so this situation will stay there only for the duration of one registration interval * 2) if insert fails, try to figure out if the driver reports a duplicate key, if yes, then do a query and load the record from db with same ruid and if other attributes matches the structure in memory, then either remove it from db (maybe it has some too old details, then next insert should succeed) or set the structure in memory to do update next time * 3) if insert in db fails, try to do a remove from db at that time
What I wanted to figure out is whether the unique id generation used for ruid can result in duplication, which should not happen by its algorithm and it is not the reason for the reported issue here. In other words, you don't report here that Kamailio generates same ruid value for different location records.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-160119895
I have never seen this
whether the unique id generation used for ruid can result in duplication
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-160136007
Closed #30.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#event-524277515
I pushed a commit to master branch implementing the option 1) -- see db_timer_clean parameter. Not much testing for it, if any issue, open a new bug.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/30#issuecomment-174314816
-
Daniel-Constantin Mierla -
Dmitri Savolainen
-
juha-h
-
ovoshlook
-
Savolainen Dmitri