Очистка базы 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: заметка
  • parentid — идентификатор публикации, котором привязан ответ/комментарий.
  • account — идентификатор зарегистрированного пользователя. 0 — анонимный посетитель.
  • created — время создания публикации.
Сначала удалим все, что антиспам недопустил к публикации. Все материалы с типами Q_QUEUED, A_QUEUED и C_QUEUED от анонимных посетителей.
 
Запрос 
select count(*) from qa_posts where type like ‘%_QUEUED’ and acount like ‘0’;
Выдал ту же цифру, что и указано в разделе «Модерация».
+———-+
| count(*) |
+———-+
|    28740 |
+———-+
1 row in set (0.10 sec)
Смело удаляем все эти строки
delete quick from qa_posts where type like ‘%_QUEUED’ and acount like ‘0’;
Выполнение этой команды у меня заняло 15 секунд.
MariaDB [ask]> delete quick from qa_posts where type like ‘%_QUEUED’ and acount like ‘0’;
Query OK, 28740 rows affected (15.06 sec)
Так же удалим все скрытые публикации. В основном все они ожидают удаления. Оставим старые и удалим все скрытые материалы с идентификатором больше 1000.
delete quick from qa_posts where type like ‘%_HIDDEN’ and postid > 1000;
 
Осталось всего 2313 строки. Что так же не соответствует реальному положению дел. Большая часть этих сообщений — спам, который прошел через Akismet.

Из них больше полутора тысяч это комментарии. Просто уверен, что это на 90% спам.
Поэтому продолжаем работать именно с этим типом сообщений.
На данный момент на сайте 70 вопросов и 97 ответов. В них есть какой-то процент спама, Путем несложных математических вычислений предполагаем, что все комментарии с идентификатором больше сотни с большой вероятностью является спамом, удаляем их.
 delete quick from qa_posts where type like ‘C’ and postid >179;
После этого очищаем кеш:
truncate qa_cache
..и переходим к самому интересному — оптимизации размера таблицы qa_posts, которая занимает, как я говорил выше, около ста мегабайт. Да, я не ошибся. Она и сейчас занимает этот размер по причине того, что в innodb отсутствует такое понятие как дефрагментация/оптимизация таблиц.
Вместо этого потребуется пересоздать таблицу.
 
  • Создаем новую таблицу по образу о подобию прежней:
create table qa_posts_new like qa_posts;
  • Копируем данные из старой таблицы в новую
insert into qa_posts_new select * from qa_posts;
  • Переименовываем старую таблицу
alter table qa_posts rename qa_posts_old;
  • Переименовываем новую таблицу
alter table qa_posts_new rename qa_posts;
  • Удаляем старую таблицу
drop table qa_posts_old;
При удалении таблицы может появиться такая ошибка:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 Она означает, что пытаются удалиться строки, на которые ссылаются внешние ключи. Я особо не разбирался в структуре базы движка question2answer. В скрытых публикациях остались неудаленные, которые я намеренно пропустил. Их не больше десятка, поэтому я удалил спам-комментарии из административной панели движка.
Слукавил. Проблема оказалась несколько глубже, чем предполагалось ранее. Ошибка пошла выше и не было возможности корректно добавить вопрос/ответ.
Возникала ошибка:
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: «http://ask.sabini.ch/?qa=ask&cat=»
Потребовалось найти и поправить все ссылки внешних ключей, которые изменились с qa_posts на qa_posts_old.
Все нижеуказанные команды пересоздают ключи с указанием правильной таблицы.
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;
Размер таблицы стал меньше одного мегабайта.

PS: я выше как-то упустил из виду, что перед тем как лезть в базу, лучше сделать ее резервную копию. Уж незабудьте ;-)
 
 
=

Другие публикации по теме:

zabbix «вопросики» в админке... Я особо не смотрел систему мониторинга Zabbix, но периодически встречаюсь с ней. В админке после переключения на русский язык в поле "История:" (...
Узнать пароль от root mysql в ISPmanager5... В новой ветке панели управления ISPmanager5 от компании ИСПсистем, все настройки хранятся в базе данных sqlite3.Для того, чтобы узнать root-пароль...
Персональная книжная полка. Обновления... Я периодически публикую обзоры на различную прочитанную художественную литературу, но ни разу, кажется, не рассказывал, как устроена моя книжная полка...

Поделиться информацией с друзьями!

Чтобы не пропустить обновления, подпишись на RSS или почтовую рассылку (свой выбор сделали уже 128 человек!)

2 комментария к «Очистка базы question2answer от спама»

  1. Как избавиться на движке Question2Answer от [no viewer found for format: BBCODE] в старом контенте?

    Нажимаешь редактировать и появляется текст, но таких страниц с [no viewer found for format: BBCODE] очень много и вручную не отридактируешь!

  2. Возможно раньше и был!Тоже сразу подумал про плагины разные, но опыта кот наплакал!Сейчас его(при мне) нет!Что подскажите делать в такой ситуации?
    Установлены сейчас вот такие:
    Basic AdSense v1.0 от Question2Answer
    Comment Voting v0.1 от NoahY
    Embed YouTube v0.2 от NoahY
    Event Logger v1.1 от Question2Answer
    Example Page v1.1 от Question2Answer
    Logarithmic Tag Cloud Widget v1.0b от NoahY
    Mouseover Layer v1.0.1 от Question2Answer
    OpenSearch Support v1.0 от Question2Answer
    Widget Anywhere v1.2 от Scott Vivian
    Q2AM Simple Adverts v1.1 от Q2A Market (…)
    Random question widget v0.1 от NoahY
    reCAPTCHA v1.0 от Question2Answer
    Ulogin Login v1.0
    Tag Cloud Widget v1.0.1 от Question2Answer
    WYSIWYG Editor v1.1.1 от Question2Answer
    XML Sitemap v1.1.1 от Question2Answer

Оставить комментарий