Tracking error with duplicate attachment id

[Chupalika]

I've been getting the "Error" message from the tracker console. It's a bit annoying since I'd have to skip like 3 pages before it starts tracking again. There isn't any other message other than "Error", so I had to go into Debug mode to check it out. Here is the error:

[ERROR] [BaseEndpoint] Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: attachments.attachment_id'.
[ERROR] [BaseEndpoint]    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
[ERROR] [BaseEndpoint]    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
[ERROR] [BaseEndpoint]    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
[ERROR] [BaseEndpoint]    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
[ERROR] [BaseEndpoint]    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
[ERROR] [BaseEndpoint]    at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
[ERROR] [BaseEndpoint] --- End of stack trace from previous location ---
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 137
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 179
[ERROR] [BaseEndpoint]    at DHT.Server.Database.Sqlite.Repositories.SqliteMessageRepository.Add(IReadOnlyList`1 messages) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Database\Sqlite\Repositories\SqliteMessageRepository.cs:line 180
[ERROR] [BaseEndpoint]    at DHT.Server.Endpoints.TrackMessagesEndpoint.Respond(HttpRequest request, HttpResponse response, CancellationToken cancellationToken) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Endpoints\TrackMessagesEndpoint.cs:line 43
[ERROR] [BaseEndpoint]    at DHT.Server.Endpoints.BaseEndpoint.Handle(HttpContext ctx) in C:\Users\Ryko\Documents\GitHub\Discord-History-Tracker\app\Server\Endpoints\BaseEndpoint.cs:line 23

It seems that there is a duplicate attachment ID somehow. Since attachment URLs use channel ID and attachment ID, maybe it's the combination that's unique instead of just attachment ID? I'm not sure. Anyway, I'm not experienced in this area, and I'm not sure how you'd like to handle it, so I'll leave it to you 😀

Let me know if you need anything else.

(By the way, I don't want this to sound like a complaint, so I'll end by saying that I really appreciate your app. It's awesome!)

[chylex]

Interesting, could you please go to the Network tab in browser dev tools, find the request to "track-messages" that is giving a 500 error, and copy the raw Request payload?

The Request will contain message data, including senders, message contents, and links to attachments, so I would recommend sending it privately. You can redact message contents and usernames if you wish, but I need to see the attachment links to check for the duplicates.

You can use https://privatebin.arch-linux.cz/ - set a password, expiration, burn the contents after reading, and email the link to contact@chylex.com

[Chupalika]

Heya, I was just in the process of collecting the data to send to you, but I ended up doing a little more digging and I think I figured out the cause actually. Do you know about the announcement/publish feature? In case not, basically a channel can be toggled into an "announcement channel", and messages sent can be "published" to channels in other servers that are following this channel. If a DHT user happens to be in multiple servers that follow the same announcement channel, well, it turns out the message will have the same message ID and attachment ID. 😵‍ So, that would be the cause for duplicate IDs if the user is tracking both channels.

[chylex]

Interesting, I will need to figure out a test case. Thanks!

[chylex]

On my end the published messages have different IDs, and they are flagged as replies to the original message in the announcement channel. Even when one message was published to multiple channels, the message ids differed by 1.

I don't have a test case with attachments, though. If multiple messages have the same attachment IDs, then that should cause the error I think.

[Chupalika]

oops, sorry, yeah, it seems my message IDs are also different in my test case. The attachment had the same link and ID though.

Not sure if this would be helpful, but here is my side:

This message ID is 1247996104411316275, and the attachment link is https://cdn.discordapp.com/attachments/581520871601209374/1247996103631310868/high_res_eh.png?ex=666aa031&is=66694eb1&hm=5bd93e68b1e9ebcb6c051070d7479c3a9aa2d6863478103610285fed46624bfc&

This message ID is 1247996154411483320, and the attachment link is https://cdn.discordapp.com/attachments/581520871601209374/1247996103631310868/high_res_eh.png?ex=666b48f1&is=6669f771&hm=47d7157d3bbb57935242b419b1400f05f9bfed47c045c232c8116d79b39a272e&

[chylex]

Reproduced the issue, I will have a fix soon.