Closed aaronskiba closed 1 week ago
aaronskiba
commented
3 weeks ago This appears to be an occurrence of the following issue: https://github.com/portagenetwork/roadmap/issues/593
SELECT question_id, COUNT(question_id)
FROM answers
WHERE plan_id = 13558
GROUP BY question_id
HAVING COUNT(question_id) > 1| question_id | count |
|---|---|
| 72638 | 2 |
aaronskiba
commented
3 weeks ago The corresponding Plan.section shows that (5/4) questions have been answered.
aaronskiba
commented
3 weeks ago In addition to the "copy" functionality not working, the duplicate answer cannot be updated either:
[51, 60] in /home/aaron/Documents/GitHub/roadmap/app/controllers/answers_controller.rb
51: question_id: args[:question_id]
52: )
53: authorize @answer
54:
55: byebug
=> 56: @answer.update(args.merge(user_id: current_user.id))
57: if args[:question_option_ids].present?
58: # Saves the record with the updated_at set to the current time.
59: # Needed if only answer.question_options is updated
60: @answer.touch
(byebug) @answer.update!(args.merge(user_id: current_user.id))
QuestionOption Load (1.4ms) SELECT "question_options".* FROM "question_options" INNER JOIN "answers_question_options" ON "question_options"."id" = "answers_question_options"."question_option_id" WHERE "answers_question_options"."answer_id" = $1 [["answer_id", 67389]]
↳ (byebug):1:in `block in create_or_update'
Question Load (1.0ms) SELECT "questions".* FROM "questions" WHERE "questions"."id" = $1 ORDER BY "questions"."number" ASC LIMIT $2 [["id", 72638], ["LIMIT", 1]]
↳ (byebug):1:in `block in create_or_update'
User Load (1.5ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 25743], ["LIMIT", 1]]
↳ (byebug):1:in `block in create_or_update'
Answer Exists? (1.5ms) SELECT 1 AS one FROM "answers" WHERE "answers"."question_id" = $1 AND "answers"."id" != $2 AND "answers"."plan_id" = $3 LIMIT $4 [["question_id", 72638], ["id", 67389], ["plan_id", 13558], ["LIMIT", 1]]
↳ (byebug):1:in `block in create_or_update'
*** ActiveRecord::RecordInvalid Exception: Validation failed: Question must be uniqueSELECT COUNT(id)
FROM answers;
---------------------------
67327
*************************************************************
SELECT COUNT(*)
FROM answers
JOIN (
SELECT plan_id, question_id
FROM answers
GROUP BY plan_id, question_id
HAVING COUNT(*) > 1
) AS duplicates
ON answers.plan_id = duplicates.plan_id AND answers.question_id = duplicates.question_id;
----------------------------
22According to these queries, there are 11 (22/2) answers with "duplicates" in the answers table. So only 0.00016% (11/67327) of the answers entries are affected.
aaronskiba
commented
3 weeks ago This table illustrates how close the created_at values are between the answers and their duplicates (max = 0.014105 seconds).
| id | duplicate_id | plan_id | question_id | created_at | duplicate_created_at | time_difference_seconds |
|---|---|---|---|---|---|---|
| 26035 | 26036 | 6914 | 67418 | 2021-04-02 19:54:36 | 2021-04-02 19:54:36 | 0 |
| 34772 | 34773 | 8424 | 68834 | 2021-12-17 23:01:46 | 2021-12-17 23:01:46 | 0 |
| 41114 | 41115 | 9347 | 69714 | 2022-06-04 02:52:42 | 2022-06-04 02:52:42 | 0 |
| 41124 | 41125 | 9347 | 69723 | 2022-06-04 04:46:48 | 2022-06-04 04:46:48 | 0 |
| 49172 | 49173 | 10912 | 68316 | 2023-03-22 15:26:56.835211 | 2023-03-22 15:26:56.842863 | 0.007652 |
| 49711 | 49712 | 11005 | 74820 | 2023-04-03 07:10:32.197305 | 2023-04-03 07:10:32.205013 | 0.007708 |
| 52035 | 52036 | 11366 | 68127 | 2023-05-23 19:21:09.952598 | 2023-05-23 19:21:09.964106 | 0.011508 |
| 54484 | 54485 | 11785 | 72244 | 2023-08-21 14:01:49.753142 | 2023-08-21 14:01:49.767247 | 0.014105 |
| 55605 | 55606 | 12007 | 69720 | 2023-09-20 00:20:59.242693 | 2023-09-20 00:20:59.24843 | 0.005737 |
| 60793 | 60794 | 12632 | 75680 | 2023-11-27 03:02:59.964372 | 2023-11-27 03:02:59.970709 | 0.006337 |
| 67389 | 67390 | 13558 | 72638 | 2024-05-08 23:18:26.696402 | 2024-05-08 23:18:26.700717 | 0.004315 |
aaronskiba
commented
3 weeks ago We can see there is a very small percentage of duplicates, and the created_at difference is very small between the answers and their corresponding duplicates. Given all of this, it seems quite possible that we're sometimes encountering a race condition which causes the question uniqueness validation check to sometimes fail. When two records are being saved nearly simultaneously, the validation check might pass for both before either is committed to the database, leading to the insertion of duplicate entries.
This seems even more likely when we factor in the 'auto-save' feature being used for answers. If a manually save was executed and an auto-save was triggered at nearly the same moment, then the aforementioned scenario could occur.
Perhaps a migration should be created to enforce the uniqueness constraint at the db level?
aaronskiba
commented
1 week ago Closing this issue as it is a symptom that will be remedied after addressing issue https://github.com/portagenetwork/roadmap/issues/800.
View details in Rollbar: https://app.rollbar.com/a/ualbertalib/fix/item/dmp_assistant/467