Deleting & recreating user with open tickets removes ability to delete ALL users and tickets

[seamustuohy]

Related to issue 19 in civicdr tickets cannot currently be deleted in the platform. It seems that this is related to a hanging reference to two tickets that were created for a previous version of the user [1217bd8f-f807-4bac-862b-e93b4740f18a & 6d760025-cd19-450e-8aca-9397fb2f5a8b] in the "reads" table.

This began occuring after running the 'create ticket' selenium test. The test created a user, and then created a ticket. Like all tests in the test-suite all users are deleted after finishing the test. The test ran twice before I realized it was not deleting the tickets. I added this function and the next test found that it could not delete the user, or the ticket afterwards. After that, no users or tickets could be deleted from the platform.

The log messages showing an attempt to delete the user can be seen below. The ID's have been sanitized. But, I can provide the raw logs via e-mail.

Attempt to delete profile.

2017-06-23T19:48:34.948510+00:00 heroku[router]: at=info method=DELETE path="/ip_profiles/XXX-XXX-XXX-XXX-XXX" host=asl19-civicdr-backend.herokuapp.com request_id=XXX-XXX-XXX-XXX-XXX fwd="XXX.XXX.XXX.XXX" dyno=web.1 connect=1ms service=67ms status=500 bytes=306 protocol=https

The errors that followed this.

2017-06-23T19:48:34+00:00 app[postgres.30826]: [DATABASE] [6-1]  sql_error_code = 23503 ERROR:  update or delete on table "tickets" violates foreign key constraint "reads_ticket_id_foreign" on table "reads"
2017-06-23T19:48:34+00:00 app[postgres.30826]: [DATABASE] [6-2]  sql_error_code = 23503 DETAIL:  Key (id)=(6d760025-cd19-450e-8aca-9397fb2f5a8b) is still referenced from table "reads".
2017-06-23T19:48:34+00:00 app[postgres.30826]: [DATABASE] [6-3]  sql_error_code = 23503 STATEMENT:  delete from "tickets" where "id" = $1
2017-06-23T19:48:34+00:00 app[postgres.30825]: [DATABASE] [6-1]  sql_error_code = 23503 ERROR:  update or delete on table "tickets" violates foreign key constraint "reads_ticket_id_foreign" on table "reads"
2017-06-23T19:48:34+00:00 app[postgres.30825]: [DATABASE] [6-2]  sql_error_code = 23503 DETAIL:  Key (id)=(1217bd8f-f807-4bac-862b-e93b4740f18a) is still referenced from table "reads".
2017-06-23T19:48:34+00:00 app[postgres.30825]: [DATABASE] [6-3]  sql_error_code = 23503 STATEMENT:  delete from "tickets" where "id" = $1

[majorzazz]

I'm trying to untangle these 500s as well. First off is this PR to delete reads when the associated ticket is removed: https://github.com/ASL-19/civicdr-backend/pull/7

[seamustuohy]

Looks like the last commit didn't address the issue fully. Attempted to delete a ticket and received the same error.

2017-06-27T14:29:43+00:00 app[postgres.18715]: [DATABASE] [6-1]  sql_error_code = 23503 ERROR:  update or delete on table "tickets" violates foreign key constraint "reads_ticket_id_foreign" on table "reads"
2017-06-27T14:29:43+00:00 app[postgres.18715]: [DATABASE] [6-2]  sql_error_code = 23503 DETAIL:  Key (id)=(6d760025-cd19-450e-8aca-9397fb2f5a8b) is still referenced from table "reads".
2017-06-27T14:29:43+00:00 app[postgres.18715]: [DATABASE] [6-3]  sql_error_code = 23503 STATEMENT:  delete from "tickets" where "id" = $1
2017-06-27T14:29:43.076527+00:00 heroku[router]: at=info method=DELETE path="/tickets/6d760025-cd19-450e-8aca-9397fb2f5a8b" host=asl19-civicdr-backend.herokuapp.com request_id=XXXXXXXXXXXX fwd="XXXXXXXXX" dyno=web.1 connect=1ms service=52ms status=500 bytes=306 protocol=https
2017-06-27T14:29:53.837490+00:00 heroku[router]: at=info method=OPTIONS path="/tickets/1217bd8f-f807-4bac-862b-e93b4740f18a" host=asl19-civicdr-backend.herokuapp.com request_id=XXXXXXXXXXXX fwd="XXXXXXXXX" dyno=web.1 connect=1ms service=2ms status=204 bytes=283 protocol=https
2017-06-27T14:29:53.905901+00:00 heroku[router]: at=info method=DELETE path="/tickets/1217bd8f-f807-4bac-862b-e93b4740f18a" host=asl19-civicdr-backend.herokuapp.com request_id=XXXXXXXXXXXX fwd="XXXXXXXXX" dyno=web.1 connect=1ms service=41ms status=500 bytes=306 protocol=https
2017-06-27T14:29:53+00:00 app[postgres.18715]: [DATABASE] [7-1]  sql_error_code = 23503 ERROR:  update or delete on table "tickets" violates foreign key constraint "reads_ticket_id_foreign" on table "reads"
2017-06-27T14:29:53+00:00 app[postgres.18715]: [DATABASE] [7-2]  sql_error_code = 23503 DETAIL:  Key (id)=(1217bd8f-f807-4bac-862b-e93b4740f18a) is still referenced from table "reads".
2017-06-27T14:29:53+00:00 app[postgres.18715]: [DATABASE] [7-3]  sql_error_code = 23503 STATEMENT:  delete from "tickets" where "id" = $1

[majorzazz]

Thanks for the fix on the order of deletion, @seamustuohy. Is this ticket now fixed, alongside https://github.com/ASL-19/civicdr/issues/19?

[seamustuohy]

I have not tested this specific workflow yet. I should have it implemented in the functionality testing scripts and tested this afternoon.

[seamustuohy]

Tested and confirmed that it works appropriately