Очистка базы question2answer от спама

У меня есть поддомен ask.sabini.ch, где у меня установлен скрипт Question2answer. Это движок сервиса вопросов-ответов типа “ответы.майл.ру” и подобного.

Нередко я им пользуюсь для публикации кратких заметок. Плюс использую для обратной связи с посетителями блога.

Ведь не всегда им хочется прокомментировать что-то по теме. Иногда и новые вопросы возникают. Там нередко эти вопросы и появляются. Я стараюсь отвечать на них сразу.

Начиная где-то с сентября прошлого года я немного забросил тот сайт и на данный момент там образовалось около 30 тысяч спам-сообщений. Основная масса из них обработались антиспам-плагином на основе Akismet и зависли на модерации. Какая-то часть проскочила.

Чтобы удалить вручную потребуется в два раза больше нажатий кнопки мыши, чем сообщений на модерации (первый клик “отказать”, второй – “удалить”). Поэтому было решено идти другим путем. Путем работы с базой данных напрямую.

Движок question2answer  использует mysql и формат базы – innodb. У меня сервер баз данных настроен таким образом, чтобы каждая таблица базы хранилась отдельно.

Таблица со всеми публикациями сейчас занимает около ста мегабайт. Не так уж и много, но полезной информации в этих данных не так много. К размеру таблицы вернемся чуть позже.

Командой show columns from qa_posts; получаем список всех полей и их возможные значения.

Интерес могут составить следующие поля:

• postid – идентификатор публикации.
• type – тип публикации. Может иметь вид:

1. Q: вопрос
2. Q_HIDDEN: скрытый вопрос
3. Q_QUEUED: вопрос на обработке у администратора/модератора
4. A: ответ
5. A_HIDDEN: скрытый ответ
6. A_QUEUED: ответ на обработке у администратора/модератора
7. C: комментарий
8. C_HIDDEN: скрытый комментарий
9. C_QUEUED: комментарий на обработке у администратора/модератора
10. NOTE: заметка

+----------+
| count(*) |
+----------+
|    28740 |
+----------+
1 row in set (0.10 sec)

MariaDB [ask]> delete quick from qa_posts where type like '%_QUEUED' and acount like '0';
Query OK, 28740 rows affected (15.06 sec)

delete quick from qa_posts where type like '%_HIDDEN' and postid > 1000;

delete quick from qa_posts where type like 'C' and postid >179;

truncate qa_cache;

create table qa_posts_new like qa_posts;

alter table qa_posts rename qa_posts_old;

alter table qa_posts_new rename qa_posts;

drop table qa_posts_old;

2014/01/15 14:18:05 [error] 87925#0: *569199 FastCGI sent in stderr: “PHP message: PHP Question2Answer MySQL query error 1452: Cannot add or update a child row: a foreign key constraint fails (`ask`.`qa_titlewords`, CONSTRAINT `qa_titlewords_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `qa_posts_old` (`postid`) ON DELETE CASCADE) – Query: INSERT INTO qa_titlewords (postid, wordid) VALUES (31612,22096)” while reading response header from upstream, client: 188.120.252.186, server: ask.sabini.ch, request: “POST /?qa=ask&cat= HTTP/1.1”, upstream: “fastcgi://unix:/tmp/php-fpm.sock:”, host: “ask.sabini.ch”, referrer: “https://ask.sabini.ch/?qa=ask&cat=”

ALTER TABLE qa_posttags DROP FOREIGN KEY qa_posttags_ibfk_1;

ALTER TABLE qa_posttags ADD CONSTRAINT qa_posttags_ibfk_1 FOREIGN KEY (`postid`) REFERENCES `qa_posts` (`postid`) ON DELETE CASCADE;

ALTER TABLE qa_tagwords DROP FOREIGN KEY qa_tagwords_ibfk_1;

ALTER TABLE qa_tagwords ADD CONSTRAINT `qa_tagwords_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `qa_posts` (`postid`) ON DELETE CASCADE;

ALTER TABLE qa_titlewords DROP FOREIGN KEY qa_titlewords_ibfk_1;

ALTER TABLE qa_titlewords ADD CONSTRAINT `qa_titlewords_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `qa_posts` (`postid`) ON DELETE CASCADE;

ALTER TABLE qa_uservotes DROP FOREIGN KEY qa_uservotes_ibfk_1;

ALTER TABLE qa_uservotes ADD CONSTRAINT `qa_uservotes_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `qa_posts` (`postid`) ON DELETE CASCADE;