In case someone like me should have played so much with the alpha 1.5 version, here is a quick and dirty script to remove all old revisions. !!! This is a developer tool, don't use it on your precious parts, and make a backup before you use it !!! It's probably rather safe on the first time you run it, because, backups are made, but on the second run, if something goes wrong, data loss might occur.

Dieter Menne dieter.menne@menne-biomed.de

-- Script to cleanup all revision in MediaWiki 1.5 alpha (not for earlier version) -- This is quick and dirty, but all original table are preserver with _old -- If everything works as request, you can delete the xx_old tables created -- by this script. -- Only tested once, don't try it with your database without making a -- backup first -- dieter.menne@menne-biomed.de -- This script does not use nested queries, it should work with MySql 4.0

CREATE TABLE `mw_text1` ( `old_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT , `old_namespace` tinyint( 2 ) unsigned NOT NULL default '0', `old_title` varchar( 255 ) binary NOT NULL default '', `old_text` mediumtext NOT NULL , `old_comment` tinyblob NOT NULL , `old_user` int( 5 ) unsigned NOT NULL default '0', `old_user_text` varchar( 255 ) binary NOT NULL default '', `old_timestamp` varchar( 14 ) binary NOT NULL default '', `old_minor_edit` tinyint( 1 ) NOT NULL default '0', `old_flags` tinyblob NOT NULL , `inverse_timestamp` varchar( 14 ) binary NOT NULL default '', PRIMARY KEY ( `old_id` ) , KEY `old_timestamp` ( `old_timestamp` ) , KEY `name_title_timestamp` ( `old_namespace` , `old_title` , `inverse_timestamp` ) , KEY `user_timestamp` ( `old_user` , `inverse_timestamp` ) , KEY `usertext_timestamp` ( `old_user_text` , `inverse_timestamp` ) ) TYPE = MYISAM ;

-- Table with latest changes CREATE TABLE `latest` ( `latest_id` INT NOT NULL );

-- get unique insert into latest select old_id from mw_text left outer join mw_page on old_id=page_latest where page_latest is not NULL;

-- Copy unique into text1 insert into mw_text1 ( select mw_text1.* from mw_text join latest on latest_id=old_id);

CREATE TABLE `mw_revision1` ( `rev_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT , `rev_page` int( 8 ) unsigned NOT NULL default '0', `rev_comment` tinyblob NOT NULL , `rev_user` int( 5 ) unsigned NOT NULL default '0', `rev_user_text` varchar( 255 ) binary NOT NULL default '', `rev_timestamp` varchar( 14 ) binary NOT NULL default '', `rev_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0', `inverse_timestamp` varchar( 14 ) binary NOT NULL default '', PRIMARY KEY ( `rev_page` , `rev_id` ) , UNIQUE KEY `rev_id` ( `rev_id` ) , KEY `rev_timestamp` ( `rev_timestamp` ) , KEY `page_timestamp` ( `rev_page` , `inverse_timestamp` ) , KEY `user_timestamp` ( `rev_user` , `inverse_timestamp` ) , KEY `usertext_timestamp` ( `rev_user_text` , `inverse_timestamp` ) ) TYPE = MYISAM ;

-- Copy most recent revisions insert into mw_revision1 ( select mw_revision.* from mw_revision join latest on latest_id=rev_id);

--- Remove list of recent changes, we first make a copy and the -- truncate the original. This could have been made easier without the -- copy, but I wanted to play safe. CREATE TABLE `mw_recentchanges_old` ( `rc_id` int( 8 ) NOT NULL AUTO_INCREMENT , `rc_timestamp` varchar( 14 ) binary NOT NULL default '', `rc_cur_time` varchar( 14 ) binary NOT NULL default '', `rc_user` int( 10 ) unsigned NOT NULL default '0', `rc_user_text` varchar( 255 ) binary NOT NULL default '', `rc_namespace` tinyint( 3 ) NOT NULL default '0', `rc_title` varchar( 255 ) binary NOT NULL default '', `rc_comment` varchar( 255 ) binary NOT NULL default '', `rc_minor` tinyint( 3 ) unsigned NOT NULL default '0', `rc_bot` tinyint( 3 ) unsigned NOT NULL default '0', `rc_new` tinyint( 3 ) unsigned NOT NULL default '0', `rc_cur_id` int( 10 ) unsigned NOT NULL default '0', `rc_this_oldid` int( 10 ) unsigned NOT NULL default '0', `rc_last_oldid` int( 10 ) unsigned NOT NULL default '0', `rc_type` tinyint( 3 ) unsigned NOT NULL default '0', `rc_moved_to_ns` tinyint( 3 ) unsigned NOT NULL default '0', `rc_moved_to_title` varchar( 255 ) binary NOT NULL default '', `rc_patrolled` tinyint( 3 ) unsigned NOT NULL default '0', `rc_ip` varchar( 15 ) NOT NULL default '', PRIMARY KEY ( `rc_id` ) , KEY `rc_timestamp` ( `rc_timestamp` ) , KEY `rc_namespace_title` ( `rc_namespace` , `rc_title` ) , KEY `rc_cur_id` ( `rc_cur_id` ) , KEY `new_name_timestamp` ( `rc_new` , `rc_namespace` , `rc_timestamp` ) , KEY `rc_ip` ( `rc_ip` ) ) TYPE = MYISAM ;

-- Copy into database INSERT INTO `mw_recentchanges_old` SELECT * FROM `mw_recentchanges` ; -- Remove date from original TRUNCATE TABLE mw_recentchanges;

--- Swap table names ALTER TABLE `mw_revision` RENAME `mw_revision_old` ; ALTER TABLE `mw_text` RENAME `mw_text_old` ; ALTER TABLE `mw_text1` RENAME `mw_text` ; ALTER TABLE `mw_revision1` RENAME `mw_revision` ; -- We don't need the latest any more DROP Table latest;