Notification from remote users fails because profile_id is null

[hyde-stevenson]

Hello,

It looks like when I try to follow from mastodon or maybe not directly linked, I get this message in my postgresql logs :

2019-11-04 12:53:56.486 CET [8126] user@pixelfed DETAIL: Failing row contains (24, null, null, null, null, null, null, 2019-11-04 11:53:56, 2019-11-04 11:53:56, null, null, null).
2019-11-04 12:53:56.486 CET [8126] user@pixelfed STATEMENT: insert into "notifications" ("updated_at", "created_at") values ($1, $2) returning "id"

I found in laravel.log also this :

2019-10-29 16:27:53] production.ERROR: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "profile_id" violates not-null constraint
DETAIL: Failing row contains (13, null, null, null, null, null, null, 2019-10-29 16:27:53, 2019-10-29 16:27:53, null, null, null). (SQL: insert into "notifications" ("updated_at", "created_at") values (2019-10-29 16:27:53, 2019-10-29 16:27:53) returning "id") {"exception":"[object] (Illuminate\Database\QueryException(code: 23502): SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "profile_id" violates not-null constraint
DETAIL: Failing row contains (13, null, null, null, null, null, null, 2019-10-29 16:27:53, 2019-10-29 16:27:53, null, null, null). (SQL: insert into "notifications" ("updated_at", "created_at") values (2019-10-29 16:27:53, 2019-10-29 16:27:53) returning "id") at /html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 23502): SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "profile_id" violates not-null constraint
DETAIL: Failing row contains (13, null, null, null, null, null, null, 2019-10-29 16:27:53, 2019-10-29 16:27:53, null, null, null).

[hyde-stevenson]

I dropped the not null constraint in the notifications table and I dont get any more the error when I favourite a pic from mastodon.

[trwnh]

I think the real issue is that profile_id is null for remote users?

[hyde-stevenson]

Actually you're right but wasn't sure what it was supposed to get and if the profile_id could be null.

[trwnh]

Just to be clear, you were following a Pixelfed user from within Mastodon, right?

[hyde-stevenson]

Yeah my Pixelfed account from my Mastodon account.

[dansup]

I think the real issue is that profile_id is null for remote users?

Nope, remote and local users have profiles record, only local users have users record. This is a bug in the inbox handler.

[trwnh]

Somewhere around this line?

pixelfed/app/Util/ActivityPub/Inbox.php

Line 183 in 26c47a0

'profile_id' => $target->id,

pixelfed/app/Util/ActivityPub/Inbox.php

Lines 159 to 208 in 26c47a0

	public function handleFollowActivity()
	{
	$actor = $this->actorFirstOrCreate($this->payload['actor']);
	if(!$actor || $actor->domain == null) {
	return;
	}
	$target = $this->profile;
	if($target->is_private == true) {
	// make follow request
	FollowRequest::firstOrCreate([
	'follower_id' => $actor->id,
	'following_id' => $target->id
	]);
	// todo: send notification
	} else {
	// store new follower
	$follower = Follower::firstOrCreate([
	'profile_id' => $actor->id,
	'following_id' => $target->id,
	'local_profile' => empty($actor->domain)
	]);
	if($follower->wasRecentlyCreated == true && $target->domain == null) {
	// send notification
	Notification::firstOrCreate([
	'profile_id' => $target->id,
	'actor_id' => $actor->id,
	'action' => 'follow',
	'message' => $follower->toText(),
	'rendered' => $follower->toHtml(),
	'item_id' => $target->id,
	'item_type' => 'App\Profile'
	]);
	}
	// send Accept to remote profile
	$accept = [
	'@context' => 'https://www.w3.org/ns/activitystreams',
	'id' => $target->permalink().'#accepts/follows/' . $follower->id,
	'type' => 'Accept',
	'actor' => $target->permalink(),
	'object' => [
	'id' => $this->payload['id'],
	'actor' => $actor->permalink(),
	'type' => 'Follow',
	'object' => $target->permalink()
	]
	];
	Helpers::sendSignedObject($target, $actor->inbox_url, $accept);
	}
	}

Tracing the actor fetch leads to this:

pixelfed/app/Util/ActivityPub/Helpers.php

Lines 381 to 432 in f7aeb8f

	public static function profileFirstOrNew($url, $runJobs = false)
	{
	$url = self::validateUrl($url);
	if($url == false) {
	abort(400, 'Invalid url');
	}
	$host = parse_url($url, PHP_URL_HOST);
	$local = config('pixelfed.domain.app') == $host ? true : false;
	if($local == true) {
	$id = last(explode('/', $url));
	return Profile::whereNull('status')
	->whereNull('domain')
	->whereUsername($id)
	->firstOrFail();
	}
	$res = self::fetchProfileFromUrl($url);
	if(isset($res['id']) == false) {
	return;
	}
	$domain = parse_url($res['id'], PHP_URL_HOST);
	$username = (string) Purify::clean($res['preferredUsername']);
	if(empty($username)) {
	return;
	}
	$remoteUsername = "@{$username}@{$domain}";
	abort_if(!self::validateUrl($res['inbox']), 400);
	abort_if(!self::validateUrl($res['outbox']), 400);
	abort_if(!self::validateUrl($res['id']), 400);
	$profile = Profile::whereRemoteUrl($res['id'])->first();
	if(!$profile) {
	$profile = new Profile();
	$profile->domain = $domain;
	$profile->username = (string) Purify::clean($remoteUsername);
	$profile->name = isset($res['name']) ? Purify::clean($res['name']) : 'user';
	$profile->bio = isset($res['summary']) ? Purify::clean($res['summary']) : null;
	$profile->sharedInbox = isset($res['endpoints']) && isset($res['endpoints']['sharedInbox']) ? $res['endpoints']['sharedInbox'] : null;
	$profile->inbox_url = $res['inbox'];
	$profile->outbox_url = $res['outbox'];
	$profile->remote_url = $res['id'];
	$profile->public_key = $res['publicKey']['publicKeyPem'];
	$profile->key_id = $res['publicKey']['id'];
	$profile->save();
	if($runJobs == true) {
	// RemoteFollowImportRecent::dispatch($res, $profile);
	CreateAvatar::dispatch($profile);
	}
	}
	return $profile;
	}

[BenLubar]

pixelfed/app/Util/ActivityPub/Inbox.php

Lines 182 to 190 in 26c47a0

	Notification::firstOrCreate([
	'profile_id' => $target->id,
	'actor_id' => $actor->id,
	'action' => 'follow',
	'message' => $follower->toText(),
	'rendered' => $follower->toHtml(),
	'item_id' => $target->id,
	'item_type' => 'App\Profile'
	]);

Here's the PostgreSQL error log created by this function call:

ERROR:  null value in column "profile_id" violates not-null constraint
DETAIL:  Failing row contains (18, null, null, null, null, null, null, null, null, 2019-11-28 22:13:07, 2019-11-28 22:13:07, null).
STATEMENT:  insert into "notifications" ("updated_at", "created_at") values ($1, $2) returning "id"

[myrrhashad]

Hello.
With a fresh install on Yunohost, I have the same issue (follows from Mastodon fail because of "null value in column "profile_id""). Is there a way to fix this ?