account_warnings.account_id is nulled when the reporter account is deleted

[xrobau]

Steps to reproduce the problem

When trying to view the admin page of the account below, an error is generated:

[ffc136ca-78d5-49f8-895d-19238e1b94bd]
[ffc136ca-78d5-49f8-895d-19238e1b94bd] ActionView::Template::Error (undefined method `username' for nil:NilClass):
[ffc136ca-78d5-49f8-895d-19238e1b94bd]      5:         = fa_icon 'warning'
[ffc136ca-78d5-49f8-895d-19238e1b94bd]      6:     .log-entry__content
[ffc136ca-78d5-49f8-895d-19238e1b94bd]      7:       .log-entry__title
[ffc136ca-78d5-49f8-895d-19238e1b94bd]      8:         = t(account_warning.action, scope: 'admin.strikes.actions', name: content_tag(:span, account_warning.account.username, class: 'username'), target: content_tag(:span, account_warning.target_account.pretty_acct, class: 'target')).html_safe
[ffc136ca-78d5-49f8-895d-19238e1b94bd]      9:       .log-entry__timestamp
[ffc136ca-78d5-49f8-895d-19238e1b94bd]     10:         %time.formatted{ datetime: account_warning.created_at.iso8601 }
[ffc136ca-78d5-49f8-895d-19238e1b94bd]     11:           = l(account_warning.created_at)
[ffc136ca-78d5-49f8-895d-19238e1b94bd]
[ffc136ca-78d5-49f8-895d-19238e1b94bd] app/views/admin/account_warnings/_account_warning.html.haml:8
[ffc136ca-78d5-49f8-895d-19238e1b94bd] app/views/admin/account_warnings/_account_warning.html.haml:1
[ffc136ca-78d5-49f8-895d-19238e1b94bd] app/views/admin/accounts/show.html.haml:256
[ffc136ca-78d5-49f8-895d-19238e1b94bd] app/controllers/concerns/localized.rb:11:in `set_locale'
[ffc136ca-78d5-49f8-895d-19238e1b94bd] lib/mastodon/rack_middleware.rb:9:in `call'
[ffc136ca-78d5-49f8-895d-19238e1b94bd] lib/public_file_server_middleware.rb:18:in `call'

https://github.com/mastodon/mastodon/blob/main/app/views/admin/account_warnings/_account_warning.html.haml#L8

Querying the account_warnings table, the reporter ID has been nulled:

mastodon=> select id,account_id,target_account_id,created_at from account_warnings where target_account_id=109367766060320746;
 id  | account_id | target_account_id  |         created_at
-----+------------+--------------------+----------------------------
 113 |            | 109367766060320746 | 2022-11-19 03:02:06.634367
 114 |            | 109367766060320746 | 2022-11-19 03:31:17.633006
 110 |            | 109367766060320746 | 2022-11-19 02:41:19.739124
(3 rows)

mastodon=>

It appears that the foreign key here nullifies the account_id when the reporting account is deleted

https://github.com/mastodon/mastodon/blob/main/db/schema.rb#L1163

There are currently 315 warnings WITH a null account_id, and 322 without, so it appears to have been happening for a while, but stopped in feb.

mastodon=> select max(created_at), min(created_at) from account_warnings where account_id is null;
            max             |            min
----------------------------+----------------------------
 2023-02-16 06:12:50.031092 | 2022-11-16 09:56:26.437818
(1 row)

mastodon=>

Expected behaviour

Not have crashed

Actual behaviour

Crashed

Detailed description

I suspect this was brought upon by running an expire in (I assume) Feburary.

As the constraint explicitly says to null the account_id col, the view should be able to handle account_warning.account being null without crashing.

My workaround for the moment will just be to set the account_id to be target_account_id, but that won't fix it for someone else who runs an expire and then has no idea why they can't see the admin page of random accounts.

Specifications

v4.1.4

[xrobau]

The (hacky, awful) fix, for anyone who encounters this:

mastodon=> begin;
BEGIN
mastodon=*> update account_warnings set account_id=target_account_id where account_id is null;
UPDATE 315
mastodon=*> select id,account_id,target_account_id,created_at from account_warnings where target_account_id=109367766060320746;
 id  |     account_id     | target_account_id  |         created_at
-----+--------------------+--------------------+----------------------------
 113 | 109367766060320746 | 109367766060320746 | 2022-11-19 03:02:06.634367
 114 | 109367766060320746 | 109367766060320746 | 2022-11-19 03:31:17.633006
 110 | 109367766060320746 | 109367766060320746 | 2022-11-19 02:41:19.739124
(3 rows)

mastodon=*> commit;
COMMIT
mastodon=>