maintenance/cleanupInvalidDbKeys.php

   1 <?php
   2 /**
   3  * Cleans up invalid titles in various tables.
   4  *
   5  * This program is free software; you can redistribute it and/or modify
   6  * it under the terms of the GNU General Public License as published by
   7  * the Free Software Foundation; either version 2 of the License, or
   8  * (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  13  * GNU General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU General Public License along
  16  * with this program; if not, write to the Free Software Foundation, Inc.,
  17  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  18  * http://www.gnu.org/copyleft/gpl.html
  19  *
  20  * @file
  21  * @ingroup Maintenance
  22  */
  23
  24 require_once __DIR__ . '/Maintenance.php';
  25
  26 /**
  27  * Maintenance script that cleans up invalid titles in various tables.
  28  *
  29  * @since 1.29
  30  * @ingroup Maintenance
  31  */
  32 class CleanupInvalidDbKeys extends Maintenance {
  33         /** @var array List of tables to clean up, and the field prefix for that table */
  34         protected static $tables = [
  35                 // Data tables
  36                 [ 'page', 'page' ],
  37                 [ 'redirect', 'rd', 'idField' => 'rd_from' ],
  38                 [ 'archive', 'ar' ],
  39                 [ 'logging', 'log' ],
  40                 [ 'protected_titles', 'pt', 'idField' => 0 ],
  41                 [ 'category', 'cat', 'nsField' => 14 ],
  42                 [ 'recentchanges', 'rc' ],
  43                 [ 'watchlist', 'wl' ],
  44                 // The querycache tables' qc(c)_title and qcc_titletwo may contain titles,
  45                 // but also usernames or other things like that, so we leave them alone
  46
  47                 // Links tables
  48                 [ 'pagelinks', 'pl', 'idField' => 'pl_from' ],
  49                 [ 'templatelinks', 'tl', 'idField' => 'tl_from' ],
  50                 [ 'categorylinks', 'cl', 'idField' => 'cl_from', 'nsField' => 14, 'titleField' => 'cl_to' ],
  51         ];
  52
  53         public function __construct() {
  54                 parent::__construct();
  55                 $this->addDescription( <<<'TEXT'
  56 This script cleans up the title fields in various tables to remove entries that
  57 will be rejected by the constructor of TitleValue.  This constructor throws an
  58 exception when invalid data is encountered, which will not normally occur on
  59 regular page views, but can happen on query special pages.
  60
  61 The script targets titles matching the regular expression /^_|[ \r\n\t]|_$/.
  62 Because any foreign key relationships involving these titles will already be
  63 broken, the titles are corrected to a valid version or the rows are deleted
  64 entirely, depending on the table.
  65
  66 The script runs with the expectation that STDOUT is redirected to a file.
  67 TEXT
  68                 );
  69                 $this->addOption( 'fix', 'Actually clean up invalid titles. If this parameter is ' .
  70                         'not specified, the script will report invalid titles but not clean them up.',
  71                         false, false );
  72                 $this->addOption( 'table', 'The table(s) to process. This option can be specified ' .
  73                         'more than once (e.g. -t category -t watchlist). If not specified, all available ' .
  74                         'tables will be processed. Available tables are: ' .
  75                         implode( ', ', array_column( static::$tables, 0 ) ), false, true, 't', true );
  76
  77                 $this->setBatchSize( 500 );
  78         }
  79
  80         public function execute() {
  81                 $tablesToProcess = $this->getOption( 'table' );
  82                 foreach ( static::$tables as $tableParams ) {
  83                         if ( !$tablesToProcess || in_array( $tableParams[0], $tablesToProcess ) ) {
  84                                 $this->cleanupTable( $tableParams );
  85                         }
  86                 }
  87
  88                 $this->outputStatus( 'Done!' );
  89                 if ( $this->hasOption( 'fix' ) ) {
  90                         $this->outputStatus( ' Cleaned up invalid DB keys on ' . wfWikiID() . "!\n" );
  91                 }
  92         }
  93
  94         /**
  95          * Prints text to STDOUT, and STDERR if STDOUT was redirected to a file.
  96          * Used for progress reporting.
  97          *
  98          * @param string $str Text to write to both places
  99          * @param string|null $channel Ignored
 100          */
 101         protected function outputStatus( $str, $channel = null ) {
 102                 // Make it easier to find progress lines in the STDOUT log
 103                 if ( trim( $str ) ) {
 104                         fwrite( STDOUT, '*** ' . trim( $str ) . "\n" );
 105                 }
 106                 fwrite( STDERR, $str );
 107         }
 108
 109         /**
 110          * Prints text to STDOUT. Used for logging output.
 111          *
 112          * @param string $str Text to write
 113          */
 114         protected function writeToReport( $str ) {
 115                 fwrite( STDOUT, $str );
 116         }
 117
 118         /**
 119          * Identifies, and optionally cleans up, invalid titles.
 120          *
 121          * @param array $tableParams A child array of self::$tables
 122          */
 123         protected function cleanupTable( $tableParams ) {
 124                 $table = $tableParams[0];
 125                 $prefix = $tableParams[1];
 126                 $idField = isset( $tableParams['idField'] ) ?
 127                         $tableParams['idField'] :
 128                         "{$prefix}_id";
 129                 $nsField = isset( $tableParams['nsField'] ) ?
 130                         $tableParams['nsField'] :
 131                         "{$prefix}_namespace";
 132                 $titleField = isset( $tableParams['titleField'] ) ?
 133                         $tableParams['titleField'] :
 134                         "{$prefix}_title";
 135
 136                 $this->outputStatus( "Looking for invalid $titleField entries in $table...\n" );
 137
 138                 // Do all the select queries on the replicas, as they are slow (they use
 139                 // unanchored LIKEs). Naturally this could cause problems if rows are
 140                 // modified after selecting and before deleting/updating, but working on
 141                 // the hypothesis that invalid rows will be old and in all likelihood
 142                 // unreferenced, we should be fine to do it like this.
 143                 $dbr = $this->getDB( DB_REPLICA, 'vslow' );
 144
 145                 // Find all TitleValue-invalid titles.
 146                 $percent = $dbr->anyString(); // DBMS-agnostic equivalent of '%' LIKE wildcard
 147                 $res = $dbr->select(
 148                         $table,
 149                         [
 150                                 'id' => $idField,
 151                                 'ns' => $nsField,
 152                                 'title' => $titleField,
 153                         ],
 154                         // The REGEXP operator is not cross-DBMS, so we have to use lots of LIKEs
 155                         [ $dbr->makeList( [
 156                                 $titleField . $dbr->buildLike( $percent, ' ', $percent ),
 157                                 $titleField . $dbr->buildLike( $percent, "\r", $percent ),
 158                                 $titleField . $dbr->buildLike( $percent, "\n", $percent ),
 159                                 $titleField . $dbr->buildLike( $percent, "\t", $percent ),
 160                                 $titleField . $dbr->buildLike( '_', $percent ),
 161                                 $titleField . $dbr->buildLike( $percent, '_' ),
 162                         ], LIST_OR ) ],
 163                         __METHOD__,
 164                         [ 'LIMIT' => $this->getBatchSize() ]
 165                 );
 166
 167                 $this->outputStatus( "Number of invalid rows: " . $res->numRows() . "\n" );
 168                 if ( !$res->numRows() ) {
 169                         $this->outputStatus( "\n" );
 170                         return;
 171                 }
 172
 173                 // Write a table of titles to the report file. Also keep a list of the found
 174                 // IDs, as we might need it later for DB updates
 175                 $this->writeToReport( sprintf( "%10s |  ns | dbkey\n", $idField ) );
 176                 $ids = [];
 177                 foreach ( $res as $row ) {
 178                         $this->writeToReport( sprintf( "%10d | %3d | %s\n", $row->id, $row->ns, $row->title ) );
 179                         $ids[] = $row->id;
 180                 }
 181
 182                 // If we're doing a dry run, output the new titles we would use for the UPDATE
 183                 // queries (if relevant), and finish
 184                 if ( !$this->hasOption( 'fix' ) ) {
 185                         if ( $table === 'logging' || $table === 'archive' ) {
 186                                 $this->writeToReport( "The following updates would be run with the --fix flag:\n" );
 187                                 foreach ( $res as $row ) {
 188                                         $newTitle = self::makeValidTitle( $row->title );
 189                                         $this->writeToReport(
 190                                                 "$idField={$row->id}: update '{$row->title}' to '$newTitle'\n" );
 191                                 }
 192                         }
 193
 194                         if ( $table !== 'page' && $table !== 'redirect' ) {
 195                                 $this->outputStatus( "Run with --fix to clean up these rows\n" );
 196                         }
 197                         $this->outputStatus( "\n" );
 198                         return;
 199                 }
 200
 201                 // Fix the bad data, using different logic for the various tables
 202                 $dbw = $this->getDB( DB_MASTER );
 203                 switch ( $table ) {
 204                         case 'page':
 205                         case 'redirect':
 206                                 // This shouldn't happen on production wikis, and we already have a script
 207                                 // to handle 'page' rows anyway, so just notify the user and let them decide
 208                                 // what to do next.
 209                                 $this->outputStatus( <<<TEXT
 210 IMPORTANT: This script does not fix invalid entries in the $table table.
 211 Consider repairing these rows, and rows in related tables, by hand.
 212 You may like to run, or borrow logic from, the cleanupTitles.php script.
 213
 214 TEXT
 215                                 );
 216                                 break;
 217
 218                         case 'archive':
 219                         case 'logging':
 220                                 // Rename the title to a corrected equivalent. Any foreign key relationships
 221                                 // to the page_title field are already broken, so this will just make sure
 222                                 // users can still access the log entries/deleted revisions from the interface
 223                                 // using a valid page title.
 224                                 $this->outputStatus(
 225                                         "Updating these rows, setting $titleField to the closest valid DB key...\n" );
 226                                 $affectedRowCount = 0;
 227                                 foreach ( $res as $row ) {
 228                                         $newTitle = self::makeValidTitle( $row->title );
 229                                         $this->writeToReport(
 230                                                 "$idField={$row->id}: updating '{$row->title}' to '$newTitle'\n" );
 231
 232                                         $dbw->update( $table,
 233                                                 [ $titleField => $newTitle ],
 234                                                 [ $idField => $row->id ],
 235                                                 __METHOD__ );
 236                                         $affectedRowCount += $dbw->affectedRows();
 237                                 }
 238                                 wfWaitForSlaves();
 239                                 $this->outputStatus( "Updated $affectedRowCount rows on $table.\n" );
 240
 241                                 break;
 242
 243                         case 'recentchanges':
 244                         case 'watchlist':
 245                         case 'category':
 246                                 // Since these broken titles can't exist, there's really nothing to watch,
 247                                 // nothing can be categorised in them, and they can't have been changed
 248                                 // recently, so we can just remove these rows.
 249                                 $this->outputStatus( "Deleting invalid $table rows...\n" );
 250                                 $dbw->delete( $table, [ $idField => $ids ], __METHOD__ );
 251                                 wfWaitForSlaves();
 252                                 $this->outputStatus( 'Deleted ' . $dbw->affectedRows() . " rows from $table.\n" );
 253                                 break;
 254
 255                         case 'protected_titles':
 256                                 // Since these broken titles can't exist, there's really nothing to protect,
 257                                 // so we can just remove these rows. Made more complicated by this table
 258                                 // not having an ID field
 259                                 $this->outputStatus( "Deleting invalid $table rows...\n" );
 260                                 $affectedRowCount = 0;
 261                                 foreach ( $res as $row ) {
 262                                         $dbw->delete( $table,
 263                                                 [ $nsField => $row->ns, $titleField => $row->title ],
 264                                                 __METHOD__ );
 265                                         $affectedRowCount += $dbw->affectedRows();
 266                                 }
 267                                 wfWaitForSlaves();
 268                                 $this->outputStatus( "Deleted $affectedRowCount rows from $table.\n" );
 269                                 break;
 270
 271                         case 'pagelinks':
 272                         case 'templatelinks':
 273                         case 'categorylinks':
 274                                 // Update links tables for each page where these bogus links are supposedly
 275                                 // located. If the invalid rows don't go away after these jobs go through,
 276                                 // they're probably being added by a buggy hook.
 277                                 $this->outputStatus( "Queueing link update jobs for the pages in $idField...\n" );
 278                                 foreach ( $res as $row ) {
 279                                         $wp = WikiPage::newFromID( $row->id );
 280                                         if ( $wp ) {
 281                                                 RefreshLinks::fixLinksFromArticle( $row->id );
 282                                         } else {
 283                                                 // This link entry points to a nonexistent page, so just get rid of it
 284                                                 $dbw->delete( $table,
 285                                                         [ $idField => $row->id, $nsField => $row->ns, $titleField => $row->title ],
 286                                                         __METHOD__ );
 287                                         }
 288                                 }
 289                                 wfWaitForSlaves();
 290                                 $this->outputStatus( "Link update jobs have been added to the job queue.\n" );
 291                                 break;
 292                 }
 293
 294                 $this->outputStatus( "\n" );
 295                 return;
 296         }
 297
 298         /**
 299          * Fix possible validation issues in the given title (DB key).
 300          *
 301          * @param string $invalidTitle
 302          * @return string
 303          */
 304         protected static function makeValidTitle( $invalidTitle ) {
 305                 return strtr( trim( $invalidTitle, '_' ),
 306                         [ ' ' => '_', "\r" => '', "\n" => '', "\t" => '_' ] );
 307         }
 308 }
 309
 310 $maintClass = CleanupInvalidDbKeys::class;
 311 require_once RUN_MAINTENANCE_IF_MAIN;