search

NicolasConstant / BirdsiteLive

An ethical bridge from Twitter
GNU Affero General Public License v3.0
457 stars 36 forks source link

DB error "duplicate key value violates unique constraint" #191

Open peanutlasko opened 1 year ago

peanutlasko commented 1 year ago

I keep seeing these errors pop up in my Docker container logs. Is this something to be worried about?

23-05-20 10:06:21 fail: BirdsiteLive.Pipeline.Processors.Federation.SendTweetsToFollowersProcessor[0] 2023-05-20 10:06:21 Posting to mas.to/inbox failed 2023-05-20 10:06:21 Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "sync_tweets_acct_tweetid_inbox_key" 2023-05-20 10:06:21
2023-05-20 10:06:21 DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. 2023-05-20 10:06:21 at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|225_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) 2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.g__ReadMessageSequential|440(NpgsqlConnector connector, Boolean async) 2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) 2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) 2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) 2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) 2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) 2023-05-20 10:06:21 at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in //Dapper/SqlMapper.Async.cs:line 418 2023-05-20 10:06:21 at BirdsiteLive.DAL.Postgres.DataAccessLayers.SyncTweetsPostgresDal.SaveTweetAsync(SyncTweet tweet) in /src/DataAccessLayers/BirdsiteLive.DAL.Postgres/DataAccessLayers/SyncTweetsPostgresDal.cs:line 48 2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsTaskBase.SaveSyncTweetAsync(String acct, Int64 tweetId, String host, String inbox) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsTaskBase.cs:line 30 2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsToSharedInboxTask.ExecuteAsync(ExtractedTweet[] tweets, SyncTwitterUser user, String host, Follower[] followersPerInstance) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsToSharedInboxTask.cs:line 65 2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsToSharedInboxTask.ExecuteAsync(ExtractedTweet[] tweets, SyncTwitterUser user, String host, Follower[] followersPerInstance) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsToSharedInboxTask.cs:line 87 2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.Federation.SendTweetsToFollowersProcessor.ProcessFollowersWithSharedInboxAsync(ExtractedTweet[] tweets, List`1 followers, SyncTwitterUser user) in /src/BirdsiteLive.Pipeline/Processors/Federation/SendTweetsToFollowersProcessor.cs:line 71 2023-05-20 10:06:21 Exception data: 2023-05-20 10:06:21 Severity: ERROR 2023-05-20 10:06:21 SqlState: 23505 2023-05-20 10:06:21 MessageText: duplicate key value violates unique constraint "sync_tweets_acct_tweetid_inbox_key" 2023-05-20 10:06:21 Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. 2023-05-20 10:06:21 SchemaName: public 2023-05-20 10:06:21 TableName: sync_tweets 2023-05-20 10:06:21 ConstraintName: sync_tweets_acct_tweetid_inbox_key 2023-05-20 10:06:21 File: nbtinsert.c 2023-05-20 10:06:21 Line: 433 2023-05-20 10:06:21 Routine: _bt_check_unique

Sverik commented 1 year ago

Ditto. Some Tweets appear with a huge delay, more than a month later. Not sure if this is related.

Sverik commented 1 year ago

Could it be that the unique index for sync_tweets table is missing host?

birdsitelive=# \d+ sync_tweets_acct_tweetid_inbox_key 
     Index "public.sync_tweets_acct_tweetid_inbox_key"
 Column  |          Type           | Definition | Storage  
---------+-------------------------+------------+----------
 acct    | character varying(50)   | acct       | extended
 tweetid | bigint                  | tweetid    | plain
 inbox   | character varying(2048) | inbox      | extended
unique, btree, for table "public.sync_tweets"

Whereas the index for followers includes host

birdsitelive=# \d+ followers_acct_host_key 
         Index "public.followers_acct_host_key"
 Column |          Type          | Definition | Storage  
--------+------------------------+------------+----------
 acct   | character varying(50)  | acct       | extended
 host   | character varying(253) | host       | extended
unique, btree, for table "public.followers"
reykjalin commented 1 year ago

I've started seeing the same issue and my instance has become unresponsive 🤔