User:Karima Rafes

French Profil

I'm a Semantic Web developer and the CEO of the company BorderCloud.

My extensions

Extensions I've created:

LinkedWiki

My Libs

Libs I've created:

4Store-php

My notes on MediaWiki

After a big problem of spam, I had to clean my database "à la mano". You can see here my queries SQL for my mediawiki 1.16.

IMPORTANT : save your database

Save your database before to do anything !

step1 : Find the spams in the database

I have a little wiki. I know the users.

I create a table with the pages of others users.

CREATE TABLE temp_page_spam
SELECT p.page_id,p.page_title
FROM  page p WHERE page_id NOT IN (
SELECT rev_page
FROM revision r
WHERE 
 r.rev_user_text = "Karima Rafes"
OR r.rev_user_text = "WikiSysop"
OR r.rev_user_text = "..."
);

I create a table with the name and revision of others users.

CREATE TABLE temp_revision_spam
SELECT rev_text_id,rev_user_text
FROM revision , temp_page_spam,USER
WHERE rev_page = temp_page_spam.page_id;

In theory not necessary !

DELETE FROM temp_revision_spam r
WHERE 
 r.rev_user_text = "WikiSysop";

step2 : Delete the spams

DELETE FROM text WHERE old_id IN
(SELECT rev_text_id
FROM temp_revision_spam);

DELETE FROM revision WHERE rev_page IN
(SELECT page_id
FROM temp_page_spam);

DELETE FROM page WHERE page_id IN
(SELECT page_id
FROM temp_page_spam);

DELETE FROM watchlist WHERE wl_title NOT IN
(SELECT p1.page_title
FROM page p1 );

DELETE FROM recentchanges WHERE rc_title NOT IN
(SELECT p1.page_title
FROM page p1 );

step3 : Look the recentchanges and check with Nuke

After you can clean all logs.

DELETE FROM recentchanges
WHERE 
rc_user_text = "WikiSysop";

step4 : Delete the bad Users

DELETE FROM USER WHERE user_name IN 
(SELECT rev_user_text 
FROM temp_revision_spam);

step5 : Delete tempory table

DROP TABLE temp_page_spam;
DROP TABLE temp_revision_spam;

step6 : rebuild index mediawiki

php maintenance/rebuildall.php