On Fri, Dec 20, 2019 at 3:17 AM Valerio Pelliccioni vmp@silkwood.it wrote:
given that 'BrownHairedGirl' was one of my 'spammers', you can do something like this:
MariaDB [tta]> select rev_user, rev_user_text FROM revision WHERE rev_user_text = 'BrownHairedGirl';
and you'll get something like this:
| rev_user | rev_user_text |
| 0 | BrownHairedGirl | | 0 | BrownHairedGirl | | 0 | BrownHairedGirl | | 0 | BrownHairedGirl | | 0 | BrownHairedGirl | | 0 | BrownHairedGirl |
Thanks.
We found them in the logging table. We deleted the spammer's log entries with:
DELETE FROM wikicryptopp_logging WHERE NOT EXISTS(SELECT NULL FROM wikicryptopp_user users WHERE users.user_name = log_user_text);
The query grabs the user's name from the logging table, and deletes the row in the logging table if the user's name does not exist in the user table.
For our wiki, the query deleted about 17,000 entries. After the query cleanupUsersWithNoId.php ran as expected.
What got us in this state was, a spammer got in. We deleted all users from the user table with id > 7. We had to recreate a few admin's accounts, but it nuked all the spam accounts. So users table was clean but logging table was dirty.
Jeff