Problem with messages in forum(?)

[espruino-discuss3]

Posted at 2020-01-27 by JumJum

According status, I've 1 new message.
If I open messages, there is nothing.
Even older messages disappeared.

[espruino-discuss3]

Posted at 2020-01-27 by AkosLukacs

+1
Might be related to someone sending spam in private messages, and Gordon disabling that?

---

Posted at 2020-01-27 by @gfwilliams

Yes, I'm afraid over the weekend someone sent spam personal messages to a bunch of forum members (over 300 by the look of it!). David (the guy that made and runs the forum) has been removing those messages, but a lot of people will still have had email notifications which obviously can't be taken back.

Right now all the PMs have been removed, but they should be back soon.

Amazingly the spam messages all appear to have been sent manually!. Someone was just sitting there at a PC sending out all the messages over the course of several hours - David is adding some sanity checks to prevent this sort of thing (one user sending messages to loads of different people) happening in the future, but at the end of the day there's very little we can do about the forum spam posts that have been appearing lately (short of deleting them quickly) if they're made by actual humans.

---

Posted at 2020-01-27 by Andrewmk

Is there a 'report spam' function somewhere that I've missed? I got the spam PM and I've noticed spam postings in the forums before now.

Andrew

---

Posted at 2020-01-27 by @gfwilliams

No, there isn't. If you see a spam post then please just PM me with it - however especially in the official boards forum I see all the new messages and so delete spam ones pretty quick (when I'm at work!).

---

Posted at 2020-01-27 by Andrewmk

OK, ta.

---

Posted at 2020-01-27 by Velocio

I've banned the user... but as it's a human, with a Gmail account, and doing this manually... it's not proving to be a very effective ban.

I'll be looking at what I can do later this evening to improve this, and to make the bans more effective and whether I can put in logic like "if you haven't posted publicly you can't do more than n private conversations"

---

Posted at 2020-01-27 by AkosLukacs

Is there a way to report spammers as a website owner to gmail / google?

---

Posted at 2020-01-27 by Velocio

Not that I know of.

I typically report them to stopforumspam and this forum uses that database as well.

---

Posted at 2020-01-27 by @allObjects

...Have no single message anymore...

Are they coming back? / Will they be restored?

I really, really, really hope that the messages will be back! - Have no issue if they Sat or Sun is missing, but deleting all is not exactly what I would look for - I just accepted the fact that once in a while I get a spam massage from a spam user...

---

Posted at 2020-01-27 by Velocio

Will they be restored?

Yes!

So the messages are still in the database, but I screwed up a query when I nuked the spam.

For reference a "huddle" is kinda like a direct message conversation.

The data structure is "huddle exists and contains comments, huddle profiles maps huddles to those who can view them and contribute further messages"... the profiles exist, the comments exist, but when I nuked the spam I figured I had left orphaned huddles so best delete them. I wrote a complex piece of SQL to "find huddles which have only deleted comments, remove the profiles and delete the huddle"... but the bit that is "have only deleted comments" was wrong, and the query went too far and has deleted the huddles for all on this site.

So... how to repair? Well... just get the valid huddles and huddle profiles bits back from a backup.

Where are my backups? Tarsnap.

I've been pulling a backup all day, but I back up the whole database and so far it's at 14GB downloaded and decrypted, and then I need to pull it somewhere and restore the two tables, find the rows to be copied back and then put them back.

And lesson learned? Don't delete spam manually even when I don't have access to test environments... ban first (safe) and delete later with a proven and tested script.

---

Posted at 2020-01-27 by Velocio

Oh... and I could construct knowledge that a huddle existed from the comments... i.e. who commented = who has permission, and the comments know which huddle they belonged to.

The missing thing is I don't know the title of the huddles.

Oh well, this backup can't take forever.

---

Posted at 2020-01-28 by AkosLukacs

Deleting / updating too much data in prod. Been there, done that :)
After talking some more experienced devs, they did it too. But now I always wrap the whole thing in a transaction that rolls back immediately. Something like this:

begin tran
 // select stuff before
 // do delete / update
 // run the same select to check and check number of rows affected
rollback tran

And re-run with commit if it's ok.

---

Posted at 2020-01-28 by Velocio

Messages should now be restored.

Dealing with the other spate of spam now.

---

Posted at 2020-01-28 by Velocio

Dealt with the other spammer... more safely too.

---

Posted at 2020-01-28 by @gfwilliams

Perfect - thank you!

---

Posted at 2020-01-28 by @allObjects

@velocio, thank you very much for getting it back so quickly and solidly... :)