search

toddsundsted / ktistec

Single user ActivityPub (https://www.w3.org/TR/activitypub/) server.
GNU Affero General Public License v3.0
350 stars 21 forks source link

Error on startup #55

Closed vrthra closed 1 year ago

vrthra commented 1 year ago

I am now getting an error on startup from the docker image. I just upgraded to the latest version.

Unhandled exception: UNIQUE constraint failed: actors.iri (SQLite3::Exception)
  from /build/lib/sqlite3/src/sqlite3/statement.cr:27:5 in 'perform_exec'
  from /build/lib/db/src/db/statement.cr:85:9 in 'perform_exec_and_release'
  from /build/lib/db/src/db/statement.cr:70:7 in 'exec:args'
  from /build/lib/db/src/db/pool_statement.cr:19:30 in 'exec:args'
  from /build/lib/db/src/db/query_methods.cr:275:7 in 'exec'
  from /build/src/database/migrations/000021-add-unique-indexes-on-actors-and-objects.cr:9:3 in '->'
  from /build/src/framework/database.cr:60:41 in 'do_operation'
  from /build/src/framework/framework.cr:136:14 in '__crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:115:5 in 'main_user_code'
  from /usr/share/crystal/src/crystal/main.cr:101:7 in 'main'
  from /usr/share/crystal/src/crystal/main.cr:127:3 in 'main'
  from src/env/__libc_start_main.c:95:2 in 'libc_start_main_stage2'
toddsundsted commented 1 year ago

this is related to the thread here: https://github.com/toddsundsted/ktistec/issues/45#issuecomment-1326923189

i thought this was a problem with the objects table, but it looks like the problem might be a duplicate in the actors table.

you need to 1) find the duplicate(s), and then 2) and 3) delete all but one copy. the unique constraints introduced by this migration should have been in place when i created the tables—there's no good reason for actors (or objects) with duplicate iri columns. i'm happy to work with you on each of these steps if you're concerned are making the right changes.

step 1, run this: select iri from actors group by iri having count(id) > 1;

step 2, for each iri with duplicates, list all of the records that match that iri: select * from actors where iri = "https://foo.bar/baz";

step 3, delete all but the last (by created_at date) record for each set of duplicates. my assumption is that the oldest is correct, and the others are older copies. i recommend deleting the duplicates by id: delete from actors where id = 123456;

step 4, if you're comfortable with it, i'd like to know what iris with duplicates there were and when they were created. my theory is that you are maybe following someone on an activitypub instance that is sending something i just don't handle correctly. if i know the iri i can take a look at the messages they are sending.

thanks!

vrthra commented 1 year ago

Here are some of the dups;

rahul@instance-web:~/ktistec$ sqlite3 db/ktistec.db 'select iri from actors group by iri having count(id) > 1;' > dups.csv
rahul@instance-web:~/ktistec$ wc -l dups.csv 
255 dups.csv
rahul@instance-web:~/ktistec$ head dups.csv 
https://botsin.space/users/myexwife
https://c.im/users/Chris1
https://c.im/users/dumbgirl123
https://c.im/users/extraordinarycouture
https://c.im/users/koncikamining
https://c.im/users/mcalel
https://c.im/users/MeganFox
https://c.im/users/skullycumdump
https://c.im/users/stancuprint
https://cybre.space/users/Aesbeth
rahul@instance-web:~/ktistec$ sqlite3 db/ktistec.db '.dump actors' > actors.csv
rahul@instance-web:~/ktistec$ cat dups.csv | while read a; do echo "====$a===="; cat actors.csv | grep $a; done | head -20
====https://botsin.space/users/myexwife====
INSERT INTO actors VALUES(14308,'2022-12-04 12:26:26.368','2022-12-04 12:26:26.368','ActivityPub::Actor::Service','https://botsin.space/users/myexwife','myexwife',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAx7prZym83+N3ot0Nd2q/\ndMsO8ui7c2Yvq4h0gEuFHRk+i62wKUOsJHuDPXczw/OrCkcCVmpnEY27PhvIPf0U\nksrw00vbuD+S3eu2m8ivrbHBgsmuBmneNjp5aiVTgxE+1e0RJTeA9YLxz5XGJE64\n6MWtawNAZnZtd5PX+Md1BCQJJQ/iFsZJNCPECd3cEC1aj4JTsXqDh/lWIrpvuXzC\npgsQy0TvWGVgRzweGAM5ckMX8PkLaDP0OWVIXCCk74M0OfNxyEvyu+Cn6hh55nNg\neJdEpmK9VqahguwQwbUCPn8O0ptk0cZwt/a7ZIF2iIzA1/rfdB93jv4WGy14JAUD\neQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://botsin.space/users/myexwife/inbox','https://botsin.space/users/myexwife/outbox','https://botsin.space/users/myexwife/following','https://botsin.space/users/myexwife/followers','','',NULL,NULL,'["https://botsin.space/@myexwife"]','2022-12-04 12:26:26.384',NULL);
INSERT INTO actors VALUES(14318,'2022-12-04 12:33:23.115','2022-12-04 12:33:23.115','ActivityPub::Actor::Service','https://botsin.space/users/myexwife','myexwife',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAx7prZym83+N3ot0Nd2q/\ndMsO8ui7c2Yvq4h0gEuFHRk+i62wKUOsJHuDPXczw/OrCkcCVmpnEY27PhvIPf0U\nksrw00vbuD+S3eu2m8ivrbHBgsmuBmneNjp5aiVTgxE+1e0RJTeA9YLxz5XGJE64\n6MWtawNAZnZtd5PX+Md1BCQJJQ/iFsZJNCPECd3cEC1aj4JTsXqDh/lWIrpvuXzC\npgsQy0TvWGVgRzweGAM5ckMX8PkLaDP0OWVIXCCk74M0OfNxyEvyu+Cn6hh55nNg\neJdEpmK9VqahguwQwbUCPn8O0ptk0cZwt/a7ZIF2iIzA1/rfdB93jv4WGy14JAUD\neQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://botsin.space/users/myexwife/inbox','https://botsin.space/users/myexwife/outbox','https://botsin.space/users/myexwife/following','https://botsin.space/users/myexwife/followers','','',NULL,NULL,'["https://botsin.space/@myexwife"]',NULL,NULL);
====https://c.im/users/Chris1====
INSERT INTO actors VALUES(23172,'2022-12-08 08:19:39.738','2022-12-08 08:19:39.738','ActivityPub::Actor::Person','https://c.im/users/Chris1','Chris1',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvmNkJ4LuV/gnoYUhaD7I\nC8fOjwzsUbxYrOP6uqp/lOs+6Kk2Ni8WnDeQrQfl4qWfc9N8UQWltlxb/Ja72oR6\niEZPdUsv2tvfuoqK6IPlBsWXLB11UBS4G9f5TYWA0jtgDYSHQfUktk/3Qv+6+jVg\nC7CTsj49Mg706199B/pBxznIzGEThhx+hnzRIHaUn5FOXN5s7peEehx1DQqcSN1N\nDjCacnDHBXXBlIjzRH0K2uzkxZKXmzZMsUcb1/MoljoSg45UsKUHxZCqB+aO4Yed\nPgMypeUDJfGuw8UzEUX9xVsItEagAKYaICZCJHhgWc5fW8SUaNdwmgh5Bzm8H32D\njQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/Chris1/inbox','https://c.im/users/Chris1/outbox','https://c.im/users/Chris1/following','https://c.im/users/Chris1/followers','','',NULL,NULL,'["https://c.im/@Chris1"]','2022-12-08 08:19:39.759',NULL);
INSERT INTO actors VALUES(23174,'2022-12-08 08:20:30.334','2022-12-08 08:20:30.334','ActivityPub::Actor::Person','https://c.im/users/Chris1','Chris1',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvmNkJ4LuV/gnoYUhaD7I\nC8fOjwzsUbxYrOP6uqp/lOs+6Kk2Ni8WnDeQrQfl4qWfc9N8UQWltlxb/Ja72oR6\niEZPdUsv2tvfuoqK6IPlBsWXLB11UBS4G9f5TYWA0jtgDYSHQfUktk/3Qv+6+jVg\nC7CTsj49Mg706199B/pBxznIzGEThhx+hnzRIHaUn5FOXN5s7peEehx1DQqcSN1N\nDjCacnDHBXXBlIjzRH0K2uzkxZKXmzZMsUcb1/MoljoSg45UsKUHxZCqB+aO4Yed\nPgMypeUDJfGuw8UzEUX9xVsItEagAKYaICZCJHhgWc5fW8SUaNdwmgh5Bzm8H32D\njQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/Chris1/inbox','https://c.im/users/Chris1/outbox','https://c.im/users/Chris1/following','https://c.im/users/Chris1/followers','','',NULL,NULL,'["https://c.im/@Chris1"]',NULL,NULL);
====https://c.im/users/dumbgirl123====
INSERT INTO actors VALUES(23713,'2022-12-08 13:20:28.494','2022-12-08 13:20:28.494','ActivityPub::Actor::Person','https://c.im/users/dumbgirl123','dumbgirl123',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnnNoh54dXKkP1/OtWTuH\ntwk5AybexYZflkz3cw1FgyPQGsa/eKMwvdAK8RDpeBA3XBQPi4kLsD52YawApfBI\nGS5auCqYe1ftPECJfhBZbV3sN7msEr3WfPwK4AvCdsnKGw9saZIK6xWdrHAazmrV\npQc+w2L7BScbOblaMGeAZha58EwVP4TX7s7i6fPFhLGGyfu4lYVF4Rre7WWS+P+v\nuc0v+f4A+Dc9KkVwc0TD6o9j+4d5l8+jJQXqXFpZ3+2QyNuZh6viPAlis588jH3c\ny79E8lIwWQNVfRWaagTMvc5Uu/ziUfJqAtABRKQFoUDUfnckiFMFlW5Cm3RIvjwn\n+wIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/dumbgirl123/inbox','https://c.im/users/dumbgirl123/outbox','https://c.im/users/dumbgirl123/following','https://c.im/users/dumbgirl123/followers','','',NULL,NULL,'["https://c.im/@dumbgirl123"]','2022-12-08 13:20:28.517',NULL);
INSERT INTO actors VALUES(23725,'2022-12-08 13:25:11.262','2022-12-08 13:25:11.262','ActivityPub::Actor::Person','https://c.im/users/dumbgirl123','dumbgirl123',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnnNoh54dXKkP1/OtWTuH\ntwk5AybexYZflkz3cw1FgyPQGsa/eKMwvdAK8RDpeBA3XBQPi4kLsD52YawApfBI\nGS5auCqYe1ftPECJfhBZbV3sN7msEr3WfPwK4AvCdsnKGw9saZIK6xWdrHAazmrV\npQc+w2L7BScbOblaMGeAZha58EwVP4TX7s7i6fPFhLGGyfu4lYVF4Rre7WWS+P+v\nuc0v+f4A+Dc9KkVwc0TD6o9j+4d5l8+jJQXqXFpZ3+2QyNuZh6viPAlis588jH3c\ny79E8lIwWQNVfRWaagTMvc5Uu/ziUfJqAtABRKQFoUDUfnckiFMFlW5Cm3RIvjwn\n+wIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/dumbgirl123/inbox','https://c.im/users/dumbgirl123/outbox','https://c.im/users/dumbgirl123/following','https://c.im/users/dumbgirl123/followers','','',NULL,NULL,'["https://c.im/@dumbgirl123"]',NULL,NULL);
====https://c.im/users/extraordinarycouture====
INSERT INTO actors VALUES(23367,'2022-12-08 10:15:27.124','2022-12-08 10:15:27.124','ActivityPub::Actor::Person','https://c.im/users/extraordinarycouture','extraordinarycouture',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1vlWS7hM+W79GQ/iMaTL\nLB9gYUKOg9DYvwYGUYzP+/wNWwjBh6v8uqG9vZsXQy1CXxNsyrkxVak87uKu/sTC\nopEX9AP39qqCTHGa6JcDgFuJdZTczMG2gYdUtZGTY8EbV+ZM5rR59DY+GHAQk0AW\neoje444qaHEvYm05iecpT1MwRzQyRd4AvlQ1R2IBIeOGNDAmXdIqdPs/GjfxWL/g\n7uZr7uOkBL7I6Vw4zHj8BuAVH5zcbWmBKh0yy7WNbCDtFoOOT2Xwt9CE+C4FjBoG\nfqLS8KTix7yTJj7c4CQANrKGHa1fDgKVbGK/8AhRmUtwynLH9wEDR6FvR0c3OIKF\nnQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/extraordinarycouture/inbox','https://c.im/users/extraordinarycouture/outbox','https://c.im/users/extraordinarycouture/following','https://c.im/users/extraordinarycouture/followers','','',NULL,NULL,'["https://c.im/@extraordinarycouture"]','2022-12-08 10:15:27.143',NULL);
INSERT INTO actors VALUES(23372,'2022-12-08 10:17:50.548','2022-12-08 10:17:50.548','ActivityPub::Actor::Person','https://c.im/users/extraordinarycouture','extraordinarycouture',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1vlWS7hM+W79GQ/iMaTL\nLB9gYUKOg9DYvwYGUYzP+/wNWwjBh6v8uqG9vZsXQy1CXxNsyrkxVak87uKu/sTC\nopEX9AP39qqCTHGa6JcDgFuJdZTczMG2gYdUtZGTY8EbV+ZM5rR59DY+GHAQk0AW\neoje444qaHEvYm05iecpT1MwRzQyRd4AvlQ1R2IBIeOGNDAmXdIqdPs/GjfxWL/g\n7uZr7uOkBL7I6Vw4zHj8BuAVH5zcbWmBKh0yy7WNbCDtFoOOT2Xwt9CE+C4FjBoG\nfqLS8KTix7yTJj7c4CQANrKGHa1fDgKVbGK/8AhRmUtwynLH9wEDR6FvR0c3OIKF\nnQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/extraordinarycouture/inbox','https://c.im/users/extraordinarycouture/outbox','https://c.im/users/extraordinarycouture/following','https://c.im/users/extraordinarycouture/followers','','',NULL,NULL,'["https://c.im/@extraordinarycouture"]',NULL,NULL);
====https://c.im/users/koncikamining====
INSERT INTO actors VALUES(23456,'2022-12-08 11:08:08.497','2022-12-08 11:08:08.497','ActivityPub::Actor::Person','https://c.im/users/koncikamining','koncikamining',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnG8IYbgvTjlF0O9MFSM4\nJhrFM2ycn1k76RbStHs2iNqqHjTTJFqdqqDiYEshO3gVXfjSOyo5XAiwvEJjlct/\n7V96kaPFy5+u4Lp1kZlw0EOl1/HsCjZRd8Gm/nvyiLlkGZ8ogB4WbTLk/837z7QD\nehw2F0MSa32iABJHtSOsR23LZ8kounpy9PksMVCjFCMv43kYmC3KLTD0Iglw/MXF\naN76irZsXB/WVyNyYQayVAe6SuF/0B+MfsGklyYAdCO1jAzR2LiqVhK7Zg2YWIiK\nf3nF7yi3+W04wgiMWJqdUSxg0yP3gJvwWo4pzryLprx5alw9UqvWMIrja4FJ7DzW\nLQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/koncikamining/inbox','https://c.im/users/koncikamining/outbox','https://c.im/users/koncikamining/following','https://c.im/users/koncikamining/followers','','',NULL,NULL,'["https://c.im/@koncikamining"]','2022-12-08 11:08:08.517',NULL);
INSERT INTO actors VALUES(23462,'2022-12-08 11:11:31.956','2022-12-08 11:11:31.956','ActivityPub::Actor::Person','https://c.im/users/koncikamining','koncikamining',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnG8IYbgvTjlF0O9MFSM4\nJhrFM2ycn1k76RbStHs2iNqqHjTTJFqdqqDiYEshO3gVXfjSOyo5XAiwvEJjlct/\n7V96kaPFy5+u4Lp1kZlw0EOl1/HsCjZRd8Gm/nvyiLlkGZ8ogB4WbTLk/837z7QD\nehw2F0MSa32iABJHtSOsR23LZ8kounpy9PksMVCjFCMv43kYmC3KLTD0Iglw/MXF\naN76irZsXB/WVyNyYQayVAe6SuF/0B+MfsGklyYAdCO1jAzR2LiqVhK7Zg2YWIiK\nf3nF7yi3+W04wgiMWJqdUSxg0yP3gJvwWo4pzryLprx5alw9UqvWMIrja4FJ7DzW\nLQIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/koncikamining/inbox','https://c.im/users/koncikamining/outbox','https://c.im/users/koncikamining/following','https://c.im/users/koncikamining/followers','','',NULL,NULL,'["https://c.im/@koncikamining"]',NULL,NULL);
====https://c.im/users/mcalel====
INSERT INTO actors VALUES(23364,'2022-12-08 10:14:00.195','2022-12-08 10:14:00.195','ActivityPub::Actor::Person','https://c.im/users/mcalel','mcalel',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA47earsfGBqrUB7j4OgtU\nPCdb+zOqlbhzW1FVFZSD8Qz2c4Bux7xOs58Ba7roMce4uLdWdqJ51onFYHnTzNFk\n9K0G/uGi6wf1M/1E7BfXagmNAih+KDWRB78buDLaFhM8r9EwUQKnRyKdWuRgILqo\nc1Wf0IykiRvJaWz96mM+i6Jzks3ehq5xFIDz0oMYtd6c4iuH/9o6c3puEtqtxBRy\n+zSuoB5WRn1u2tHBAYQZp4MUn/GA2HCp7Jjabz9nZY2Qu9JrUo6uQbiTeUaFwwxs\n5h2LRb31H0c21B0ucZNyqtLu2ZTEJhzu4MRsjd6fq46DJyTSUsgAZ9tsdQDCpM99\ntwIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/mcalel/inbox','https://c.im/users/mcalel/outbox','https://c.im/users/mcalel/following','https://c.im/users/mcalel/followers','','',NULL,NULL,'["https://c.im/@mcalel"]','2022-12-08 10:14:00.224',NULL);
INSERT INTO actors VALUES(23374,'2022-12-08 10:18:36.484','2022-12-08 10:18:36.484','ActivityPub::Actor::Person','https://c.im/users/mcalel','mcalel',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA47earsfGBqrUB7j4OgtU\nPCdb+zOqlbhzW1FVFZSD8Qz2c4Bux7xOs58Ba7roMce4uLdWdqJ51onFYHnTzNFk\n9K0G/uGi6wf1M/1E7BfXagmNAih+KDWRB78buDLaFhM8r9EwUQKnRyKdWuRgILqo\nc1Wf0IykiRvJaWz96mM+i6Jzks3ehq5xFIDz0oMYtd6c4iuH/9o6c3puEtqtxBRy\n+zSuoB5WRn1u2tHBAYQZp4MUn/GA2HCp7Jjabz9nZY2Qu9JrUo6uQbiTeUaFwwxs\n5h2LRb31H0c21B0ucZNyqtLu2ZTEJhzu4MRsjd6fq46DJyTSUsgAZ9tsdQDCpM99\ntwIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/mcalel/inbox','https://c.im/users/mcalel/outbox','https://c.im/users/mcalel/following','https://c.im/users/mcalel/followers','','',NULL,NULL,'["https://c.im/@mcalel"]',NULL,NULL);
====https://c.im/users/MeganFox====
INSERT INTO actors VALUES(34712,'2022-12-13 15:05:38.759','2022-12-13 15:05:38.759','ActivityPub::Actor::Person','https://c.im/users/MeganFox','MeganFox',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1TtZR7x7/dDVNcskGIHV\n6ffSb8Fuq4OQ0+95UsSowGZEirHCNV+rBVduw+luEmSnS842MA1fX/hs4s24GU2c\neE6KuqhfirOfEftK5zMZC0EqnKkSK09RLwYihlsgXXy48p7X+XKA7vUKVEY4Wm4P\nsJVtf4EPQ5IuisDwGE6fcFwtdmXE+lDh/XGYBWoHHWJEL2+jQyFMlmi6dhgd0f8X\na/Xoe2B2t1gP/dw0Au02xNEExxvgtVaCIG543YNS0/uRsg0OJrNifk9pfUS1cd5f\nsZQxpN/VToNREN/6qnmE7YG3N28HrKkcEZSLQr+LmWz/3MpX72knIIXdIRgwl8af\n8QIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://c.im/users/MeganFox/inbox','https://c.im/users/MeganFox/outbox','https://c.im/users/MeganFox/following','https://c.im/users/MeganFox/followers','','',NULL,NULL,'["https://c.im/@MeganFox"]','2022-12-13 15:05:38.776',NULL);
....
====https://social.lot23.com/actor====
INSERT INTO actors VALUES(18688,'2022-12-06 12:30:34.361','2022-12-06 12:30:34.361','ActivityPub::Actor::Application','https://social.lot23.com/actor','social.lot23.com',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArlLWNVGKc3U5iJPo90+5\nHVYv1b9VI2+FJWQtHKRHZ/T+HukJ/Lyxhsxvn7pejKs6Zn5Z0HHW+qOcGp8v9xtQ\nrvGcTTDczywHU+NloflvsMz30261Y7H7A9FwoIRDT0F8GGYCG4ADqBILqpPSOujU\nK7HV2fqSzDOarF1Zuz3g5QBSg3Sfl2aziPvsHNtXW4UZenhLup7g66vVD093R6MC\n/uVGY9Gm1FW2RAh3IsB4Vr7NKnJUwUMwhjT5bYYeewBovThnFPWb8x+7VOlk5xvh\ncOrAXTscpqSNo6b3RdcihobDBtMv1Tf+Iwq5Wxrq7jPFojz/UeBYLt3gn8yAC/MU\n5wIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://social.lot23.com/actor/inbox','https://social.lot23.com/actor/outbox',NULL,NULL,NULL,NULL,NULL,NULL,'["https://social.lot23.com/about/more?instance_actor=true"]','2022-12-06 12:30:34.381',NULL);
INSERT INTO actors VALUES(18849,'2022-12-06 14:18:00.132','2022-12-06 14:18:00.132','ActivityPub::Actor::Application','https://social.lot23.com/actor','social.lot23.com',replace('-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArlLWNVGKc3U5iJPo90+5\nHVYv1b9VI2+FJWQtHKRHZ/T+HukJ/Lyxhsxvn7pejKs6Zn5Z0HHW+qOcGp8v9xtQ\nrvGcTTDczywHU+NloflvsMz30261Y7H7A9FwoIRDT0F8GGYCG4ADqBILqpPSOujU\nK7HV2fqSzDOarF1Zuz3g5QBSg3Sfl2aziPvsHNtXW4UZenhLup7g66vVD093R6MC\n/uVGY9Gm1FW2RAh3IsB4Vr7NKnJUwUMwhjT5bYYeewBovThnFPWb8x+7VOlk5xvh\ncOrAXTscpqSNo6b3RdcihobDBtMv1Tf+Iwq5Wxrq7jPFojz/UeBYLt3gn8yAC/MU\n5wIDAQAB\n-----END PUBLIC KEY-----\n','\n',char(10)),NULL,'https://social.lot23.com/actor/inbox','https://social.lot23.com/actor/outbox',NULL,NULL,NULL,NULL,NULL,NULL,'["https://social.lot23.com/about/more?instance_actor=true"]',NULL,NULL);
rahul@instance-web:~/ktistec$

Any advice on which duplicates to delete? And any info you want to get from these files?

toddsundsted commented 1 year ago

thanks! it looks like these are all actors that were deleted on their mastodon instance (the deleted_at field has a value) but were then subsequently recreated.

you have two options, depending on how careful you want to be.

  1. if it's all pairs like this, you could delete the member of the pair where deleted_at is null and see if that takes care of the duplicates. doing it this way will leave the deleted version of the actor record in the database. it's more work, but it's the state i would have expected to find in the database.

  2. there's a chance, based on the spot checking i've done, that these are all spam accounts that were identified and deleted. in that case, you can probably just delete them all since it's doubtful they shared anything meaningful in your timeline.

the first option is probably the safer of the two options but will take more effort.

toddsundsted commented 1 year ago

in the meantime, i'm going to look at that mastodon instance. i get hundreds, or maybe thousands, of deletes a day and i haven't seen this happen, so i'd like to see what this instance is doing—maybe it's running a weirdly old version or something...

vrthra commented 1 year ago

Thank you! Much appreciated. I will save the db just in case, and do the second option. If anything bad happens, I can always start from the saved db.

vrthra commented 1 year ago

After deletion;

sqlite3 db/ktistec.db "delete from actors where iri in (select iri from actors group by iri having count(id) > 1);"

I have another exception

Unhandled exception: UNIQUE constraint failed: objects.iri (SQLite3::Exception)
  from /build/lib/sqlite3/src/sqlite3/statement.cr:27:5 in 'perform_exec'
  from /build/lib/db/src/db/statement.cr:85:9 in 'perform_exec_and_release'
  from /build/lib/db/src/db/statement.cr:70:7 in 'exec:args'
  from /build/lib/db/src/db/pool_statement.cr:19:30 in 'exec:args'
  from /build/lib/db/src/db/query_methods.cr:275:7 in 'exec'
  from /build/src/database/migrations/000021-add-unique-indexes-on-actors-and-objects.cr:16:3 in '->'
  from /build/src/framework/database.cr:60:41 in 'do_operation'
  from /build/src/framework/framework.cr:136:14 in '__crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:115:5 in 'main_user_code'
  from /usr/share/crystal/src/crystal/main.cr:101:7 in 'main'
  from /usr/share/crystal/src/crystal/main.cr:127:3 in 'main'
  from src/env/__libc_start_main.c:95:2 in 'libc_start_main_stage2'

Any idea how to handle this? Should I go back to the original db instead?

toddsundsted commented 1 year ago

it looks like the same problem but this time with the objects table. i thought we'd checked that, but something could have be added in the meantime. you could dump them like you did for actors and see if they come in deleted, not deleted pairs. if so, you should be able to delete those to.

toddsundsted commented 1 year ago

the troubling thing is that this has happened to at least two users. the constraints will keep the problem out of the database in the future, but i'd sure love to understand what's causing it in the first place...

vrthra commented 1 year ago

Ok, after

sqlite3 db/ktistec.db "delete from objects where iri in (select iri from objects group by iri having count(id) > 1);"

everything works. I will check the logs once in a while to see if anything is amiss. Thank you for your help!

toddsundsted commented 1 year ago

thanks @vrthra. did the object duplicate come in pairs where the first record was deleted (had a timestamp in the deleted_at column) and the second, later, record did not?

vrthra commented 1 year ago

Yes, it was also in pairs, with the same pattern.

EvanKrall commented 1 year ago

I had the same issue after upgrading to 2.0.0-6 from 2.0.0-4 (though I think I was running a build slightly after -4.) I was able to delete all the duplicate actors with

delete from actors where iri in (select iri from actors group by iri having count(id) > 1) and deleted_at is null;

and I only had one result for select iri from objects group by iri having count(id) > 1; so I just deleted that duplicate manually with delete from objects where iri = 'https://...' and deleted_at is null;

felixkrohn commented 1 year ago

Same here. Thanks for that one-liner @EvanKrall !