3 * Cleans up invalid titles in various tables.
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.
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.
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
21 * @ingroup Maintenance
24 require_once __DIR__
. '/Maintenance.php';
27 * Maintenance script that cleans up invalid titles in various tables.
30 * @ingroup Maintenance
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 = [
37 [ 'redirect', 'rd', 'idField' => 'rd_from' ],
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
48 [ 'pagelinks', 'pl', 'idField' => 'pl_from' ],
49 [ 'templatelinks', 'tl', 'idField' => 'tl_from' ],
50 [ 'categorylinks', 'cl', 'idField' => 'cl_from', 'nsField' => 14, 'titleField' => 'cl_to' ],
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.
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.
66 The script runs with the expectation that STDOUT is redirected to a file.
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.',
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 );
77 $this->setBatchSize( 500 );
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 );
88 $this->outputStatus( 'Done!' );
89 if ( $this->hasOption( 'fix' ) ) {
90 $this->outputStatus( ' Cleaned up invalid DB keys on ' . wfWikiID() . "!\n" );
95 * Prints text to STDOUT, and STDERR if STDOUT was redirected to a file.
96 * Used for progress reporting.
98 * @param string $str Text to write to both places
99 * @param string|null $channel Ignored
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" );
106 fwrite( STDERR
, $str );
110 * Prints text to STDOUT. Used for logging output.
112 * @param string $str Text to write
114 protected function writeToReport( $str ) {
115 fwrite( STDOUT
, $str );
119 * Identifies, and optionally cleans up, invalid titles.
121 * @param array $tableParams A child array of self::$tables
123 protected function cleanupTable( $tableParams ) {
124 $table = $tableParams[0];
125 $prefix = $tableParams[1];
126 $idField = $tableParams['idField'] ??
"{$prefix}_id";
127 $nsField = $tableParams['nsField'] ??
"{$prefix}_namespace";
128 $titleField = $tableParams['titleField'] ??
"{$prefix}_title";
130 $this->outputStatus( "Looking for invalid $titleField entries in $table...\n" );
132 // Do all the select queries on the replicas, as they are slow (they use
133 // unanchored LIKEs). Naturally this could cause problems if rows are
134 // modified after selecting and before deleting/updating, but working on
135 // the hypothesis that invalid rows will be old and in all likelihood
136 // unreferenced, we should be fine to do it like this.
137 $dbr = $this->getDB( DB_REPLICA
, 'vslow' );
139 // Find all TitleValue-invalid titles.
140 $percent = $dbr->anyString(); // DBMS-agnostic equivalent of '%' LIKE wildcard
146 'title' => $titleField,
148 // The REGEXP operator is not cross-DBMS, so we have to use lots of LIKEs
150 $titleField . $dbr->buildLike( $percent, ' ', $percent ),
151 $titleField . $dbr->buildLike( $percent, "\r", $percent ),
152 $titleField . $dbr->buildLike( $percent, "\n", $percent ),
153 $titleField . $dbr->buildLike( $percent, "\t", $percent ),
154 $titleField . $dbr->buildLike( '_', $percent ),
155 $titleField . $dbr->buildLike( $percent, '_' ),
158 [ 'LIMIT' => $this->getBatchSize() ]
161 $this->outputStatus( "Number of invalid rows: " . $res->numRows() . "\n" );
162 if ( !$res->numRows() ) {
163 $this->outputStatus( "\n" );
167 // Write a table of titles to the report file. Also keep a list of the found
168 // IDs, as we might need it later for DB updates
169 $this->writeToReport( sprintf( "%10s | ns | dbkey\n", $idField ) );
171 foreach ( $res as $row ) {
172 $this->writeToReport( sprintf( "%10d | %3d | %s\n", $row->id
, $row->ns
, $row->title
) );
176 // If we're doing a dry run, output the new titles we would use for the UPDATE
177 // queries (if relevant), and finish
178 if ( !$this->hasOption( 'fix' ) ) {
179 if ( $table === 'logging' ||
$table === 'archive' ) {
180 $this->writeToReport( "The following updates would be run with the --fix flag:\n" );
181 foreach ( $res as $row ) {
182 $newTitle = self
::makeValidTitle( $row->title
);
183 $this->writeToReport(
184 "$idField={$row->id}: update '{$row->title}' to '$newTitle'\n" );
188 if ( $table !== 'page' && $table !== 'redirect' ) {
189 $this->outputStatus( "Run with --fix to clean up these rows\n" );
191 $this->outputStatus( "\n" );
195 // Fix the bad data, using different logic for the various tables
196 $dbw = $this->getDB( DB_MASTER
);
200 // This shouldn't happen on production wikis, and we already have a script
201 // to handle 'page' rows anyway, so just notify the user and let them decide
203 $this->outputStatus( <<<TEXT
204 IMPORTANT: This script does not fix invalid entries in the $table table.
205 Consider repairing these rows, and rows in related tables, by hand.
206 You may like to run, or borrow logic from, the cleanupTitles.php script.
214 // Rename the title to a corrected equivalent. Any foreign key relationships
215 // to the page_title field are already broken, so this will just make sure
216 // users can still access the log entries/deleted revisions from the interface
217 // using a valid page title.
219 "Updating these rows, setting $titleField to the closest valid DB key...\n" );
220 $affectedRowCount = 0;
221 foreach ( $res as $row ) {
222 $newTitle = self
::makeValidTitle( $row->title
);
223 $this->writeToReport(
224 "$idField={$row->id}: updating '{$row->title}' to '$newTitle'\n" );
226 $dbw->update( $table,
227 [ $titleField => $newTitle ],
228 [ $idField => $row->id
],
230 $affectedRowCount +
= $dbw->affectedRows();
233 $this->outputStatus( "Updated $affectedRowCount rows on $table.\n" );
237 case 'recentchanges':
240 // Since these broken titles can't exist, there's really nothing to watch,
241 // nothing can be categorised in them, and they can't have been changed
242 // recently, so we can just remove these rows.
243 $this->outputStatus( "Deleting invalid $table rows...\n" );
244 $dbw->delete( $table, [ $idField => $ids ], __METHOD__
);
246 $this->outputStatus( 'Deleted ' . $dbw->affectedRows() . " rows from $table.\n" );
249 case 'protected_titles':
250 // Since these broken titles can't exist, there's really nothing to protect,
251 // so we can just remove these rows. Made more complicated by this table
252 // not having an ID field
253 $this->outputStatus( "Deleting invalid $table rows...\n" );
254 $affectedRowCount = 0;
255 foreach ( $res as $row ) {
256 $dbw->delete( $table,
257 [ $nsField => $row->ns
, $titleField => $row->title
],
259 $affectedRowCount +
= $dbw->affectedRows();
262 $this->outputStatus( "Deleted $affectedRowCount rows from $table.\n" );
266 case 'templatelinks':
267 case 'categorylinks':
268 // Update links tables for each page where these bogus links are supposedly
269 // located. If the invalid rows don't go away after these jobs go through,
270 // they're probably being added by a buggy hook.
271 $this->outputStatus( "Queueing link update jobs for the pages in $idField...\n" );
272 foreach ( $res as $row ) {
273 $wp = WikiPage
::newFromID( $row->id
);
275 RefreshLinks
::fixLinksFromArticle( $row->id
);
277 // This link entry points to a nonexistent page, so just get rid of it
278 $dbw->delete( $table,
279 [ $idField => $row->id
, $nsField => $row->ns
, $titleField => $row->title
],
284 $this->outputStatus( "Link update jobs have been added to the job queue.\n" );
288 $this->outputStatus( "\n" );
293 * Fix possible validation issues in the given title (DB key).
295 * @param string $invalidTitle
298 protected static function makeValidTitle( $invalidTitle ) {
299 return strtr( trim( $invalidTitle, '_' ),
300 [ ' ' => '_', "\r" => '', "\n" => '', "\t" => '_' ] );
304 $maintClass = CleanupInvalidDbKeys
::class;
305 require_once RUN_MAINTENANCE_IF_MAIN
;