HeiseForenWiki:Datenrekonstruktion

Durch den Verlust von Verwaltungsinformationen bei der HeiseForenWiki:Downtime war die Kombination eines Backups des Wiki in einer 1.3er Version mit Daten aus der Version 1.4.5 erforderlich.
Ausserdem sollten die Spameinträge automatisch beseitigt werden.
Dazu wurde ein SQL-Script erstellt, dass diese Schritte ausführt, auch um die Änderungen nachvollziehbar zu machen.
Alle Schritte wurden auf einem System mit MySQL 4.1.11 ausgeführt - manche dieser Befehle sind in früheren Versionen des MySQL Servers so nicht lauffähig, speziell das 'delete...where xy in (select...)' geht auf 3er Versionen nicht.
Zuerst wurde festgelegt, dass alle Einträge ab dem 26.9.05 23:00:00 zu verwerfen waren. Es mag sein, dass einzelne Änderungen nach diesem Datum nicht von Bots oder Spammern stammen und eigentlich gutgemeint waren; es wurde aber darauf verzichtet, das genauer aufzudröseln.

Aus dieser Festlegung lässt sich daher die Liste der Spam-Einträge wie folgt erstellen:

create table spamed_pages select old_title as title, old_namespace as ns, 
  count(old_title) as n_attacks,cur_id as spamed_id 
  from old join cur on old_title = cur_title 
     where cur_namespace =  old_namespace and old_timestamp > 20050926230000 group by old_title, old_namespace;

Das ergab folgende Liste:

+------------------------------------+----+-----------+-----------+
| title                              | ns | n_attacks | spamed_id |
+------------------------------------+----+-----------+-----------+
| Anmeldung                          |  4 |        79 |       700 |
| Editierhilfe                       |  4 |        30 |       656 |
| Erste_Schritte                     |  4 |        37 |       659 |
| GNU_Freie_Dokumentationslizenz     |  0 |        45 |       694 |
| Gut                                |  0 |        20 |      1401 |
| Hauptseite                         |  1 |        51 |       941 |
| Hilfe                              |  4 |        42 |       657 |
| Hommingberger_Gepardenforelle      |  0 |        56 |      1381 |
| Inhalt:Allgemein_Forenspezifisches |  0 |        20 |       696 |
| Inhalt:Allgemeines                 |  0 |        21 |       695 |
| Inhalt:Heise-Forenspezifisches     |  0 |        21 |       697 |
| Inhalt:Technisches                 |  0 |        17 |       698 |
| Jm                                 |  2 |        23 |      1023 |
| Lizenzbestimmungen                 |  4 |        20 |      1688 |
| Mediawiki_145                      |  4 |        16 |      1648 |
| Newbie                             |  0 |        39 |       623 |
| Wikipedia                          |  0 |        44 |       699 |
| Wunschliste                        |  4 |        22 |       690 |
| Über_HeiseForenWiki                |  5 |        34 |       691 |
+------------------------------------+----+-----------+-----------+

Eine Komplikation ergibt sich daraus, dass in MediaWiki die Tabellen für die aktuelle Version eines Artikels und die archivierten Fassungen vorhergehender Versionen in 2 unterschiedlichen Tabellen stehen. Vermutlich ist dies aus Gründen der Performace so angelegt. Die jeweiligen Tabellen haben diese Felder:

mysql> describe old;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| old_id            | int(8) unsigned     |      | PRI | NULL    | auto_increment |
| old_namespace     | tinyint(2) unsigned |      | MUL | 0       |                |
| old_title         | varchar(255)        |      |     |         |                |
| old_text          | mediumtext          |      |     |         |                |
| old_comment       | tinyblob            |      |     |         |                |
| old_user          | int(5) unsigned     |      | MUL | 0       |                |
| old_user_text     | varchar(255)        |      | MUL |         |                |
| old_timestamp     | varchar(14)         |      | MUL |         |                |
| old_minor_edit    | tinyint(1)          |      |     | 0       |                |
| old_flags         | tinyblob            |      |     |         |                |
| inverse_timestamp | varchar(14)         |      |     |         |                |
+-------------------+---------------------+------+-----+---------+----------------+
mysql> describe cur;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| cur_id            | int(8) unsigned     |      | PRI | NULL    | auto_increment |
| cur_namespace     | tinyint(2) unsigned |      | MUL | 0       |                |
| cur_title         | varchar(255)        |      | MUL |         |                |
| cur_text          | mediumtext          |      |     |         |                |
| cur_comment       | tinyblob            |      |     |         |                |
| cur_user          | int(5) unsigned     |      | MUL | 0       |                |
| cur_user_text     | varchar(255)        |      | MUL |         |                |
| cur_timestamp     | varchar(14)         |      | MUL |         |                |
| cur_restrictions  | tinyblob            |      |     |         |                |
| cur_counter       | bigint(20) unsigned |      |     | 0       |                |
| cur_is_redirect   | tinyint(1) unsigned |      |     | 0       |                |
| cur_minor_edit    | tinyint(1) unsigned |      |     | 0       |                |
| cur_is_new        | tinyint(1) unsigned |      |     | 0       |                |
| cur_random        | double unsigned     |      | MUL | 0       |                |
| cur_touched       | varchar(14)         |      |     |         |                |
| inverse_timestamp | varchar(14)         |      |     |         |                |
+-------------------+---------------------+------+-----+---------+----------------+

Jedenfalls braucht man zusätzlich zu den Informationen aus der Lists der gespamten Seiten noch die id desjenigen Eintrags aus der Tabelle old, der den letzten gültigen Eintrag vor der Spamattacke speichert. Dazu wird zuerst ermittelt, welcher Eintrag den neuesten Zeitstempel trägt, der vor der deadline liegt:

create table to_replace select max(old_timestamp) as good_time, old_title as title,
    ns, n_attacks, spamed_id 
    from old join spamed_pages on old_title = title 
        where old_namespace = ns and old_timestamp < 20050926230000 
          group by old_title;

Jetzt kann man noch die old_id dieses letzten guten Eintrags aus der old herausfinden, die wird zunächst als Spalte 'lg_id' hinzugefügt und dann anhand der bereits bekannten 'good_time' eingetragen:

alter table to_replace add lg_id integer;

update to_replace set lg_id = 
   (select old_id from old where old_timestamp = good_time and old_title = title and old_namespace = ns);

sodass sich insgesamt diese Liste ergibt:

+----------------+------------------------------------+----+-----------+-----------+-------+
| good_time      | title                              | ns | n_attacks | spamed_id | lg_id |
+----------------+------------------------------------+----+-----------+-----------+-------+
| 20040311184023 | Anmeldung                          |  4 |        79 |       700 |  6741 |
| 20050416182413 | Editierhilfe                       |  4 |        30 |       656 |  6740 |
| 20041219104846 | Erste_Schritte                     |  4 |        37 |       659 |  6732 |
| 20050624000344 | GNU_Freie_Dokumentationslizenz     |  0 |        45 |       694 |  6731 |
| 20050701163645 | Gut                                |  0 |        20 |      1401 |  6762 |
| 20050401162822 | Hauptseite                         |  1 |        51 |       941 |  6739 |
| 20040409055542 | Hilfe                              |  4 |        42 |       657 |  6735 |
| 20050701091623 | Hommingberger_Gepardenforelle      |  0 |        56 |      1381 |  6734 |
| 20050825093256 | Inhalt:Allgemein_Forenspezifisches |  0 |        20 |       696 |  6759 |
| 20040921202718 | Inhalt:Allgemeines                 |  0 |        21 |       695 |  6756 |
| 20050831144546 | Inhalt:Heise-Forenspezifisches     |  0 |        21 |       697 |  6730 |
| 20040531214350 | Inhalt:Technisches                 |  0 |        17 |       698 |  6749 |
| 20050719121959 | Jm                                 |  2 |        23 |      1023 |  6745 |
| 20050826160145 | Lizenzbestimmungen                 |  4 |        20 |      1688 |  6755 |
| 20050705214127 | Mediawiki_145                      |  4 |        16 |      1648 |  6747 |
| 20040813120217 | Newbie                             |  0 |        39 |       623 |  6733 |
| 20050624000100 | Wikipedia                          |  0 |        44 |       699 |  6746 |
| 20050826234332 | Wunschliste                        |  4 |        22 |       690 |  6750 |
| 20040608234835 | Über_HeiseForenWiki                |  5 |        34 |       691 |  6737 |
+----------------+------------------------------------+----+-----------+-----------+-------+

Jetzt ist das Gröbste geschafft, die Informationen, was wodurch zu ersetzen ist, sind ermittelt.
Weil sich leider updates mit mehreren Feldern direkt in SQL eigentlich nicht vernünftig erstellen lassen - in PostGres ist das über eine eingebaute Skriptsprache einfacher - wird zunächst eine Tabelle erzeugt, die die beabsichtigten Änderungen schon enthält:

create table newcur as select cur_id, cur_namespace, cur_title, old_text as cur_text, 
   old_comment as cur_comment, old_user as cur_user, old_user_text as cur_user_text, 
   old_timestamp as cur_timestamp, cur_restrictions, cur_counter, cur_is_redirect, 
   old_minor_edit as cur_minor_edit, cur_is_new, cur_random, 
   20051003120000 as cur_touched, 99999999999999 - old_timestamp as inverse_timestamp 
     from to_replace 
       join cur on spamed_id = cur_id 
       join old on lg_id = old_id;

Die Ausgabe dieser Tabelle sparen wir uns, weil es wortwörtlich den Rahmen sprengen würde ;).

Die geplanten Löschungen werden jetzt in der Tabelle cur2delete gesichert, und dann gleich auch durchgeführt:

create table cur2delete as select * from cur where cur_id in (select spamed_id from to_replace);
delete from cur where cur_id in (select spamed_id from to_replace);

(ob dieses delete from ... where <key> in (select...) funktioniert, hängt von der verwendeten Version ab. getestet wurde das mit mysql 4.1.11) gleich gefolgt vom Insert aus der schon vorbereiteten newcur:

insert into cur select * from newcur;

jetzt werden noch die gespammten Einträge aus der old weggeschmissen, als Kriterium einfach der Zeitstempel:

delete from old where old_timestamp > 20050926230000;

was bleibt ist noch, die Einträge aus der old zu löschen, die jetzt bereits in der cur stehen:

delete from old where old_id in (select lg_id from to_replace);

(geht so erst ab Version 4.1.xx) Ohne diesen letzten Schritt würde die Liste der letzten Änderungen nicht richtig funktionieren.

alles ganz einfach eigentlich...

...nur muss es das Wiki dann auch noch richtig darstellen.
glücklicherweise gibt es im Verzeichnis ./maintenance einer MediaWiki-Installation ein PHP-Script, das genau diese Änmderungen auch in die internen Verwaltungsinformationen überträgt: 'rebuildall.php'.
In den Hilfedateien ist eigentlich nur von 'rebuildindexes.php' die Rede, aber dieses Script hat dann den Rest übernommen.