[kamailio/kamailio] presence: preserve etag uniqueness when marking presentity for removal (PR #4275)
#### Pre-Submission Checklist - [x] Commit message has the format required by CONTRIBUTING guide - [x] Commits are split per component (core, individual modules, libs, utils, ...) - [x] Each component has a single commit (if not, squash them into one commit) - [x] No commits to README files for modules (changes must be done to docbook files in `doc/` subfolder, the README file is autogenerated) #### Type Of Change - [x] Small bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds new functionality) - [ ] Breaking change (fix or feature that would change existing functionality) #### Checklist: - [x] PR should be backported to stable branches - [x] Tested changes locally - [ ] Related to issue #### Description When the `presence` module attempts to clean up records in the `presentity` table, it replaces the `etag` with a static, hardcoded string: ``` "*#-OFFLINE-#*" ``` However, the `presentity` table enforces a uniqueness constraint on the following combination of fields: ``` MariaDB [kamailio]> show create table presentity; | presentity | CREATE TABLE `presentity` ( ... UNIQUE KEY `presentity_idx` (`username`,`domain`,`event`,`etag`), ``` This approach breaks the uniqueness of etag, which is intended to differentiate records , especially in cases such as parallel calls, or when using kamailio as a forking proxy. Instead of overwriting the etag with the static offline marker, this patch appends the marker to the existing etag value. This ensures the offline flag is retained while preserving the uniqueness of the etag. The deletion query was also updated accordingly: it now identifies offline records using a LIKE '%*#-OFFLINE-#*' clause. Based on testing, the duplicate key errors no longer occur, and the cleanup logic now behaves as expected. This patch improves the reliability of presentity cleanup without requiring schema or index changes. Please consider merging into master. Thanks! You can view, comment on, or merge this pull request online at: https://github.com/kamailio/kamailio/pull/4275 -- Commit Summary -- * presence: preserve etag uniqueness when marking presentity for removal -- File Changes -- M src/modules/presence/presentity.c (90) -- Patch Links -- https://github.com/kamailio/kamailio/pull/4275.patch https://github.com/kamailio/kamailio/pull/4275.diff -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275@github.com>
miconda left a comment (kamailio/kamailio#4275) I expect that introducing the use of raw_query breaks the usage of the module with non-sql backends (e.g., db_text), so another solution has to be found. Maybe leveraging another column (e.g., priority set to -1 for cleaning up records) or adding a new column. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2949380759 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2949380759@github.com>
linuxmaniac left a comment (kamailio/kamailio#4275) but this problem is about unique keys. So it's a relational database problem no? -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2949457920 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2949457920@github.com>
miconda left a comment (kamailio/kamailio#4275) Well, if I understood correctly, the code replaces the unique ETags of the presentity records with a predefined value (ie., `#-OFFLINE-#'`) in order to delete the records later, but that can lead to infringement of unique constraints. So the current behaviour seems to be an uninspired `solution` based on this report, so we have to look for alternatives that are compatible with the internal DB API, without relying on raw queries. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2950574075 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2950574075@github.com>
Ozzyboshi left a comment (kamailio/kamailio#4275) I’m proposing an alternative solution, which I had previously developed and appears to work well. This approach uses the "event" field to mark records with #-OFFLINE-#, allowing us to retain the etag, which is the key element that differentiates records. One major advantage of this solution is that it eliminates the need for raw SQL queries. In the current implementation, I had to rely on raw SQL because I needed to retrieve all records ending with #-OFFLINE-#, and I couldn’t find a way to achieve that without raw queries. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2954752825 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2954752825@github.com>
linuxmaniac left a comment (kamailio/kamailio#4275) Maybe it's time to introduce a new field into the schema. This approach doesn't feels right. If we don't want to relay on raw queries. I would introduce a ``offline`` (boolean) field to mark the rows so it can be filtered with a simple query -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2954796386 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2954796386@github.com>
miconda left a comment (kamailio/kamailio#4275) If a new column is introduced, I would name it a bit more generic (e.g., `status`), so it can be eventually reused if new processing modes are needed in the future, for now it can be 0 for active, 1 for delete. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2954833486 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2954833486@github.com>
Ozzyboshi left a comment (kamailio/kamailio#4275) ok but... someone is willing to create the PR? Should I do it? Changing db schema is required I need to go over the procedure in this case because I have no clue how to do it. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2955732772 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2955732772@github.com>
linuxmaniac left a comment (kamailio/kamailio#4275) @Ozzyboshi I will create a PR with that approach -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2956061757 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2956061757@github.com>
Closed #4275. -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#event-18068124753 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/issue_event/18068124753@github.com>
Ozzyboshi left a comment (kamailio/kamailio#4275) ok in this case i will just close this PR -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-2957872302 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c2957872302@github.com>
linuxmaniac left a comment (kamailio/kamailio#4275) @Ozzyboshi I forgot this branch. Can you please test the solution from your side? -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-4489074644 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c4489074644@github.com>
Ozzyboshi left a comment (kamailio/kamailio#4275) sorry victor just saw your comment now, i think this branch is part of our solution, i can check it, why are you interested? do you have the same problem? -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-5059807829 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c5059807829@github.com>
linuxmaniac left a comment (kamailio/kamailio#4275)
@Ozzyboshi I will create a PR with that approach
Well I said I was going to do it
Maybe it's time to introduce a new field into the schema. This approach doesn't feels right. If we don't want to relay on raw queries. I would introduce a ``offline`` (boolean) field to mark the rows so it can be filtered with a simple query
-- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-5060941778 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c5060941778@github.com>
Ozzyboshi left a comment (kamailio/kamailio#4275) ok perfect if upstream is able to provide a solution i can remove mine from my codebase -- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/4275#issuecomment-5080397209 You are receiving this because you are subscribed to this thread. Message ID: <kamailio/kamailio/pull/4275/c5080397209@github.com>
participants (3)
-
Alessio Garzi -
Daniel-Constantin Mierla
-
Victor Seva