Skip to main content

spam?
!Friendica Support

Is this good for me? Don't feel like.


> select count(*) from gserver where url LIKE '%troll.cf%';
+----------+
| count(*) |
+----------+
| 13837755 |
+----------+
1 row in set (1 min 20.495 sec)



| 172846 | https://30m1uebec.activitypub-troll.cf | http://30m1uebec.activitypub-troll.cf | | | | 0 | 0 | | | unkn | | 0 | | 2022-12-03 19:51:17 | 0001-01-01 00:00:00 | 2023-01-03 20:01:25 | 0001-01-01 00:00:00 | 0 | 0 | 0 | 2023-02-03 20:01:25 | NULL | NULL | NULL | NULL | NULL | NULL |

| 172847 | https://1ml1up799.activitypub-troll.cf | http://1ml1up799.activitypub-troll.cf | | | | 0 | 0 | | | unkn | | 0 | | 2022-12-03 19:51:18 | 0001-01-01 00:00:00 | 2023-01-03 20:01:26 | 0001-01-01 00:00:00 | 0 | 0 | 0 | 2023-02-03 20:01:26 | NULL | NULL | NULL | NULL | NULL | NULL |

| 172848 | https://2ckkegfqs.activitypub-troll.cf | http://2ckkegfqs.activitypub-troll.cf | | | | 0 | 0 | | | unkn | | 0 | | 2022-12-03 19:51:20 | 0001-01-01 00:00:00 | 2023-01-03 20:01:28 | 0001-01-01 00:00:00 | 0 | 0 | 0 | 2023-02-03 20:01:28 | NULL | NULL | NULL | NULL | NULL | NULL |

| 172849 | https://q2g4bs0i.activitypub-troll.cf | http://q2g4bs0i.activitypub-troll.cf | | | | 0 | 0 | | | unkn | | 0 | | 2022-12-03 19:51:21 | 0001-01-01 00:00:00 | 2023-01-03 20:01:28 | 0001-01-01 00:00:00 | 0 | 0 | 0 | 2023-02-03 20:01:28 | NULL | NULL | NULL | NULL | NULL | NULL |
!Friendica Support
in reply to grin

Michael Vogel
Michael Vogel
• •
Update to 2023.01 and use the server block feature to block *.activitypub-troll.cf.
in reply to grin

Jonas ✅
Jonas ✅
• •

Content warning: spam?

@Friendica Support @grin
in reply to grin

Lorenz
Lorenz
• •
is it possible via cli as well? It takes hours doing in chunks
in reply to Lorenz

Jonas ✅
Jonas ✅
• •
@Lorenz @grin I don't think so. You can block it via cli but it does not purge the corresponding entries in the database.
@grin @Lorenz
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin Yes, that is possible: ./bin/console serverblock add <pattern> <reason>
@grin @Lorenz
in reply to grin

Lorenz
Lorenz
• •
@jonas thanks, so that was quick:
bin/console serverblock add *.activitypub-troll.cf spam
in reply to Lorenz

grin
grin
• •
Yes but that haven't cleaned the db, have it.
in reply to grin

Lorenz
Lorenz
• •
no but at least there will be no activity fram from those servers, I hope
in reply to grin

Lorenz
Lorenz
• •
The queue is empty, wow, there were more than 100 000 elements in the queue before!!
in reply to grin

Lorenz
Lorenz
• •
now i have the command running from here to empty the database https://github.com/friendica/friendica/issues/12705#issuecomment-1398887054
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin The constraint between contact and gserver is ON DELETE RESTRICT which is a security measurement, that you do not delete accidentally something and then ending up with a nearly empty database. So you have to delete all those records there, too. Please do not change the constraint to CASCADE.
@grin @Lorenz
in reply to Roland Häder

Lorenz
Lorenz
• •
Was the command wrong? Not sure if it had deleted anything, it just replied with
Query OK, 13779424 rows affected (1 hour 16 min 19.826 sec)
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin Yes, it did delete a lot, those which are affected by the DELETE statement. The statement ran through because no records in contact linked to any of those (see column gsid).
@grin @Lorenz
in reply to Roland Häder

Lorenz
Lorenz
• •
@Roland Häder Ok, thanks. I don't see any difference in the available space on the server, though.
@Roland Häder
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin Yes, you won't Because of #InnoDB is one or two big data "blobs" and not like MyISAM distinct files for each table. Just run the optimization SQL command. Yes, it will tell you that InnoDB doesn't support this and does a recreate+analyze for you instead.
#InnoDB @grin @Lorenz
in reply to Roland Häder

Roland Häder
Roland Häder
• •
@grin @Lorenz Friendica does this with major tables from time to time for you. But unfortunately the code for that is still a lot scattered all over the code:
@grin @Lorenz
in reply to Roland Häder

Lorenz
Lorenz
• •
@Roland Häder Thanks! I am not fluent in mysql / mariadb. There are lots of optimization commands, it seems. Which one should I use?
@Roland Häder
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin OPTIMIZE TABLE `name` is a good start. MariaDB does change that for InnoDB tables (like Friendica uses) to recreating the table and copying all rows over then analyzes the table for gathering statistics:
https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me
@grin @Lorenz
in reply to Roland Häder

Lorenz
Lorenz
• •
@Roland Häder I run
OPTIMIZE TABLE gserver; and it deleted more than 3GB!
@Roland Häder
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin After such massive drop of records, no wonder that this happens. I'm glad you finally freed up some wasted space. :-) So good job there. This needs to be included in the purging worker job that purges contact records from blocked domains and then purges them from the gserver table.
@grin @Lorenz
in reply to Roland Häder

grin
grin
• •
So no, optimize table doesn't do anything for innodb. Copying/renaming is painful for huge tables.

Correction: after removing (better) optimize started, and recreated in a flash. Thanks!
This entry was edited (1 year ago)
in reply to grin

Roland Häder
Roland Häder
• •
@grin @Lorenz Yes, you need a lot more free disk space for "optimizing" those large InnoDB tables because of how the optimization works.
@grin @Lorenz
in reply to Roland Häder

Anders Rytter Hansen
Anders Rytter Hansen
• •
if you enable innodb-file-per-table it wont keep unused space reserved.
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin And wow, your node broke a record: 13,779,424 records from these trolls.
@grin @Lorenz
in reply to Roland Häder

Lorenz
Lorenz
• •
@Roland Häder Wow, I feel honoured ;) It is only a small instance, just for me, on a VPS with 2GB RAM
@Roland Häder
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin And you maybe want to block their IP address(es) on your firewall, too. I had to uninstall mine (#Bastille-Firewall had a very easy ncurses-based setup assistant and easy to edit configuration file) and currently I don't know how to setup #Shorewall correctly. :-(
#Bastille-Firewall #Shorewall @grin @Lorenz
in reply to Roland Häder

grin
grin
• •
It seems sbcloud.cc was the origin. Problem is that you usually do not know which IP to ban, not easy to trace the problem, the logs don't help much.
in reply to grin

Lorenz
Lorenz
• •
hm.... sbcloud looks legit. The startpage is Element (for Matrix chat server), then there is fed.sbcloud.cc which is used only by five users
in reply to Lorenz

grin
grin
• •
Deleting from the table took 2 hours. Still wondering how to shrink it since it's too big for having another copy.
in reply to grin

Roland Häder
Roland Häder
• •
@grin @Lorenz Just execute OPTIMIZE TABLE `gserver` and it will be recreated+analyzed for you.
@grin @Lorenz
in reply to grin

Roland Häder
Roland Häder
• •
@grin Can you please edit your post and put those parts with an URL in it into a code block?
@grin
in reply to grin

Anders Rytter Hansen
Anders Rytter Hansen
• •
Oh damn. I guess I should block that on my server.
in reply to grin

Lorenz
Lorenz
• •
And now an attack by gab.best! Have to block them now as well
in reply to grin

grin
grin
• •
I have banned and purged sbcloud.cc from everywhere, based on this

2023-01-29T10:27:59Z worker [INFO]: Server peer update start {"url":"https://fed.sbcloud.cc","worker_id":"85e31dd","worker_cmd":"UpdateServerPeers"} - {"file":"UpdateServerPeers.php","line":54,"function":"execute","uid":"a33038","process_id":295381}
2023-01-29T10:27:59Z worker [INFO]: Server is unknown. Start discovery. {"Server":"https://1chs090ty.activitypub-troll.cf","worker_id":"85e31dd","worker_cmd":"UpdateServerPeers"} - {"file":"GServer.php","line":358,"function":"check","uid":"a33038","process_id":295381}


Since then worker doesn't pull in spambots again.

Now, it would be neat to know:
1. What exatly happened (I don't know the protocol that deeply)
2. Who did what
3. How to prevent that from happening in the future (both network-wise and locally)

#spambot #spam
#spam #spambot
in reply to grin

grin
grin
• •
@Roland Häder @Lorenz !Friendica Support The toot this one replies to would have been shared to the people mentioned here, but I cannot seem to have a way to edit it accordingly; editing doesn't expand name references, nor can seem to be able to tag people... I hope they can see the parent toot of this....
I am not sure I'll ever grok how this is supposed to work, who gets notified when and who see what where how.
@Roland Häder @Lorenz !Friendica Support
in reply to grin

Roland Häder
Roland Häder
• •
@grin sbcloud.cc looks very legitimate to me, no sign of spam/scam. Please take a look at this:
$ host sbcloud.cc
sbcloud.cc has address 172.67.182.3
sbcloud.cc has address 104.21.59.174
sbcloud.cc has IPv6 address 2606:4700:3037::ac43:b603
sbcloud.cc has IPv6 address 2606:4700:3030::6815:3bae
$ host 1chs090ty.activitypub-troll.cf
Host 1chs090ty.activitypub-troll.cf not found: 3(NXDOMAIN)
$ host activitypub-troll.cf
Host activitypub-troll.cf not found: 3(NXDOMAIN)
$

So Even the domain activitypub-troll.cf doesn't exist, but sbcloud.cc does. I see no relation between these two domains as they share nothing in common.
@grin
in reply to Roland Häder

grin
grin
• •
Why? You think that having dns is proof that no bad traffic comes from there? Especially since you seem to realise that the spammed addresses were fakes, yet you seem to expect "blocking" a non-existent server. You based your opinion on about zero amount of facts, but you seem to be quite assured that you are, somehow, right.

But anyway, stopped spam for me, you're free to do whatever you deem proper, including looking at the dns when the AP networks get abused. :shrug:

I wish there were useful logs: those would be better for abuse management than... dns.
in reply to grin

Roland Häder
Roland Häder
• •
@grin No, they are unrelated so far. Or is that fake (see NXDOMAIN) domain originating from their IP address?
@grin
in reply to grin

Lorenz
Lorenz
• •
Even after I have blocked these servers more than two weeks ago, the gserver table had more than 8GB! Now I run the same delete command again, and the table now has 10GB. What happened? Somebody knows what to do? Weird stuff. 

MariaDB [friendicadb]> DELETE FROM `gserver` WHERE `url` LIKE '%activitypub-troll.cf%' OR `url` LIKE '%gab.best%';
Query OK, 37499832 rows affected (5 hours 46 min 51.045 sec)


UPDATE: I run OPTIMIZE TABLE gserver; - and now, wow! the table is nearly empty, just 31 MB, and now it seems I did not have to upgrade my VPS!
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin We need to add this to the purge worker job to have it automatically done for you. Or maybe optimize all tables? Just a SHOW TABLES FROM `friendica`; and exlude all views? A query on mysql schema is to much "vendor-specific" and the script already "knows" which are views and which are tables.

PS: Your both avatars are not showing up here, even after a "Refetch contact data".
@grin @Lorenz
This entry was edited (1 year ago)
in reply to Roland Häder

Lorenz
Lorenz
• •
I tried to optimize all tables, but that lasted too long, so I stopped it.

I am surprised to hear that the avatar is not showing. What can be the reason? What can I do?
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin Do you have access to your server through SSH? Then try: $ screen -dmS mysql mysql -p -u <user> <database> And insert your data. Please don't include your password in the parameter list as this is visible with ps -ax. Then you can let the optimization run. You can access it with screen -r mysql and leave it with ALT+AD (exact order!) without quitting it.
@grin @Lorenz
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz After some attempts they both suddenly loaded and show up here. Edit: Only yours is showing. Maybe this error is related to:
2023-02-13T20:26:39Z worker [ERROR]: Uncaught exception in worker execution {"class":"Friendica\\Core\\Storage\\Exception\\StorageException","message":"Database storage failed to update ","code":500,"file":"/var/www/.../src/Core/Storage/Type/Database.php:94","trace":"#0 /var/www/.../src/Model/Photo.php(449): Friendica\\Core\\Storage\\Type\\Database->put()\n#1 /var/www/.../src/Model/Photo.php(636): Friendica\\Model\\Photo::store()\n#2 /var/www/.../src/Model/Contact.php(2307): Friendica\\Model\\Photo::importProfilePhoto()\n#3 /var/www/.../src/Model/Contact.php(2792): Friendica\\Model\\Contact::updateAvatar()\n#4 /var/www/.../src/Model/Contact.php(2585): Friendica\\Model\\Contact::updateFromProbeArray()\n#5 /var/www/.../src/Worker/UpdateContact.php(47): Friendica\\Model\\Contact::updateFromProbe()\n#6 [internal function]: Friendica\\Worker\\UpdateContact::execute()\n#7 /var/www/.../src/Core/Worker.php(572): call_user_func_array()\n#8 /var/www/.../src/Core/Worker.php(386): Friendica\\Core\\Worker::execFunction()\n#9 /var/www/.../src/Core/Worker.php(121): Friendica\\Core\\Worker::execute()\n#10 /var/www/.../bin/worker.php(83): Friendica\\Core\\Worker::processQueue()\n#11 {main}","previous":"Exception: Got a packet bigger than 'max_allowed_packet' bytes in /var/www/.../src/Core/Storage/Type/Database.php:94\nStack trace:\n#0 /var/www/.../src/Model/Photo.php(449): Friendica\\Core\\Storage\\Type\\Database->put()\n#1 /var/www/.../src/Model/Photo.php(636): Friendica\\Model\\Photo::store()\n#2 /var/www/.../src/Model/Contact.php(2307): Friendica\\Model\\Photo::importProfilePhoto()\n#3 /var/www/.../src/Model/Contact.php(2792): Friendica\\Model\\Contact::updateAvatar()\n#4 /var/www/.../src/Model/Contact.php(2585): Friendica\\Model\\Contact::updateFromProbeArray()\n#5 /var/www/.../src/Worker/UpdateContact.php(47): Friendica\\Model\\Contact::updateFromProbe()\n#6 [internal function]: Friendica\\Worker\\UpdateContact::execute()\n#7 /var/www/.../src/Core/Worker.php(572): call_user_func_array()\n#8 /var/www/.../src/Core/Worker.php(386): Friendica\\Core\\Worker::execFunction()\n#9 /var/www/.../src/Core/Worker.php(121): Friendica\\Core\\Worker::execute()\n#10 /var/www/.../bin/worker.php(83): Friendica\\Core\\Worker::processQueue()\n#11 {main}","worker_id":"cec5e9a","worker_cmd":"UpdateContact"} - {"file":"Worker.php","line":577,"function":"execFunction","request-id":"63ea9ba037f4d","uid":"a61a92","process_id":21743}
@Lorenz
This entry was edited (1 year ago)
in reply to Roland Häder

Lorenz
Lorenz
• •
Was the error on my or your or Friendica's side? Last time I checked the photo showed up on Mastodon instances
in reply to Roland Häder

Lorenz
Lorenz
• •
Exception: Got a packet bigger than 'max_allowed_packet' bytes

Seems to be on your end then?
in reply to grin

Lorenz
Lorenz
• •
Two months later same issue:


MariaDB [friendicadb]> DELETE FROM `gserver` WHERE `url` LIKE '%activitypub-troll.cf%' OR `url` LIKE '%gab.best%';
Query OK, 38621191 rows affected (4 hours 3 min 46.706 sec)


more than 9GB freed up!
in reply to grin

Raroun
Raroun
• •
Running 2023-03-rc on the last commit.
86k server from *.gab.best.

select count(*) from gserver where url LIKE '%troll.cf%' OR `url` LIKE '%gab.best%';
+----------+
| 86378 |
+----------+
DELETE FROM `gserver` WHERE `url` LIKE '%activitypub-troll.cf%' OR `url` LIKE '%gab.best%';
Query OK, 86378 rows affected (1.143 sec)

Changed Block pattern from gab.best to *.gab.best.
Obiviously i missed the wildcard.
in reply to Raroun

Lorenz
Lorenz
• •

The thing is I have added the wildcard and blocked the other troll-domain, and nevertheless, I still get all their spam.

so within one week the result:

MariaDB [friendicadb]> DELETE FROM `gserver` WHERE `url` LIKE '%activitypub-troll.cf%' OR `url` LIKE '%gab.best%';
Query OK, 17018290 rows affected (1 hour 48 min 11.643 sec)
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin @Raroun I added a small fix that prevented this flooding of your gserver table, I wonder why is that happening? Someone must have introduced a bug or has removed it. My version here on my instance is working perfectly fine.
@grin @Raroun @Lorenz
in reply to Lorenz

Roland Häder
Roland Häder
• •
@Lorenz @grin @Raroun SELECT * FROM `gserver` WHERE `url` LIKE '%activitypub-troll.cf%' OR `url` LIKE '%gab.best%'; gives me zero rows here.
@grin @Raroun @Lorenz
in reply to Lorenz

OldKid
OldKid
• •

@Lorenz Please block only *.activitypub-troll.cf the other blocks should not do anything.

@Roland Häder if I remember correctly your fix was added to the 2023.03-rc branch. The instance of @Lorenz runs on 2023.01, so still without the fix.

@Roland Häder @Lorenz
in reply to grin

Lorenz
Lorenz
• •
@Roland Häder did you add the fix for 2023.01 - 1502 or the newest dev-releases?
@Roland Häder
in reply to grin

Raroun
Raroun
• •
@Roland Häder @OldKid @Lorenz
The pull request is marked in the 2023-03 Milestone, so I guess its in the actual RC and later in 2023-03-stable.
Link to pull request #12700
@Roland Häder @OldKid @Lorenz
This entry was edited (1 year ago)
⇧