| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
/** |
| 8 |
* Chunked, time-budgeted backfill of canonical_url on legacy redirect/logsv2 rows. |
| 9 |
* |
| 10 |
* Legacy rows (pre-4.1.x) lack canonical_url, so the view-build JOIN between |
| 11 |
* redirects.canonical_url and logsv2.canonical_url has to fall back to |
| 12 |
* CONCAT/TRIM and cannot use idx_canonical_url. This component drains the NULL |
| 13 |
* backlog across successive daily cron ticks and browser-triggered admin AJAX |
| 14 |
* drains until every row has been populated, at which point reads can drop the |
| 15 |
* COALESCE fallback entirely. |
| 16 |
* |
| 17 |
* Reached by {@see ABJ_404_Solution_DatabaseUpgradeDailyMaintenance} (daily cron) |
| 18 |
* and the browser-triggered lazy-backfill AJAX endpoint. |
| 19 |
*/ |
| 20 |
class ABJ_404_Solution_DatabaseUpgradeCanonicalUrlBackfill extends ABJ_404_Solution_DatabaseUpgradeComponent { |
| 21 |
|
| 22 |
// Backfill tuning values live on the coordinator and are exposed through |
| 23 |
// DatabaseUpgradeComponent accessors so the delegate does not duplicate |
| 24 |
// constants owned by ABJ_404_Solution_DatabaseUpgradesEtc. |
| 25 |
|
| 26 |
/** |
| 27 |
* Populate {wp_abj404_redirects}.canonical_url for any rows still NULL, |
| 28 |
* one chunk at a time. Each chunk runs: |
| 29 |
* |
| 30 |
* UPDATE redirects SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM url)) |
| 31 |
* WHERE canonical_url IS NULL LIMIT N |
| 32 |
* |
| 33 |
* Idempotent -- once every row has canonical_url set, the WHERE matches |
| 34 |
* zero rows and the function returns immediately. The chunk loop is |
| 35 |
* bounded by both row count (CANONICAL_URL_BACKFILL_CHUNK_SIZE) and wall |
| 36 |
* clock (CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC) so a 350K-row site |
| 37 |
* converges over successive daily cron ticks without ever blocking a |
| 38 |
* request long enough to hit PHP max_execution_time. |
| 39 |
* |
| 40 |
* Skips silently when: |
| 41 |
* - the redirects table is missing (degraded site state) |
| 42 |
* - the canonical_url column is missing (column add hasn't happened |
| 43 |
* yet, e.g. immediately after upgrade before verifyColumns ran) |
| 44 |
* - the previous run errored -- repair flow surfaces the error |
| 45 |
* |
| 46 |
* @return int Number of rows updated in this invocation. |
| 47 |
*/ |
| 48 |
public function backfillRedirectsCanonicalUrl(): int { |
| 49 |
global $wpdb; |
| 50 |
if (!isset($wpdb)) { |
| 51 |
return 0; |
| 52 |
} |
| 53 |
$redirectsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_redirects}'); |
| 54 |
|
| 55 |
// SHOW TABLES existence probe -- same shape as verifyTableMaterialized() |
| 56 |
// in DatabaseUpgradesEtc.php:854. The DAO's tableExists() helper is |
| 57 |
// private so we can't reach it from here, and routing through |
| 58 |
// queryAndGetResults() would log a benign "table doesn't exist" error |
| 59 |
// on freshly-installed sites before runInitialCreateTables() has run. |
| 60 |
// DAO-bypass-approved: schema existence probe -- see comment above. |
| 61 |
$found = $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($redirectsTable) . "'"); |
| 62 |
if ($found !== $redirectsTable) { |
| 63 |
return 0; |
| 64 |
} |
| 65 |
if ($this->columnExists($redirectsTable, 'canonical_url') !== true) { |
| 66 |
return 0; |
| 67 |
} |
| 68 |
|
| 69 |
$chunkSize = (int)$this->getCanonicalUrlBackfillChunkSize(); |
| 70 |
$timeBudget = (float)$this->getCanonicalUrlBackfillTimeBudgetSec(); |
| 71 |
$start = abj_clock()->nowFloat(); |
| 72 |
$totalUpdated = 0; |
| 73 |
|
| 74 |
while ((abj_clock()->nowFloat() - $start) < $timeBudget) { |
| 75 |
$query = "UPDATE " . $redirectsTable . |
| 76 |
" SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM url))" . |
| 77 |
" WHERE canonical_url IS NULL" . |
| 78 |
" LIMIT " . $chunkSize; |
| 79 |
|
| 80 |
$result = $this->dbCore->queryAndGetResults($query); |
| 81 |
$lastError = isset($result['last_error']) && is_string($result['last_error']) ? $result['last_error'] : ''; |
| 82 |
if ($lastError !== '') { |
| 83 |
$this->logger->warn("backfillRedirectsCanonicalUrl: stopping after error: " . $lastError); |
| 84 |
return $totalUpdated; |
| 85 |
} |
| 86 |
|
| 87 |
$rowsAffected = isset($result['rows_affected']) && is_numeric($result['rows_affected']) |
| 88 |
? (int)$result['rows_affected'] : 0; |
| 89 |
$totalUpdated += $rowsAffected; |
| 90 |
if ($rowsAffected < $chunkSize) { |
| 91 |
break; |
| 92 |
} |
| 93 |
} |
| 94 |
|
| 95 |
if ($totalUpdated > 0) { |
| 96 |
$this->logger->infoMessage(sprintf( |
| 97 |
"backfillRedirectsCanonicalUrl: populated canonical_url on %d redirect rows in %.2fs.", |
| 98 |
$totalUpdated, |
| 99 |
abj_clock()->nowFloat() - $start |
| 100 |
)); |
| 101 |
} |
| 102 |
|
| 103 |
$this->maybeFlipRedirectsCanonicalUrlBackfillCompleteFlag($redirectsTable); |
| 104 |
return $totalUpdated; |
| 105 |
} |
| 106 |
|
| 107 |
/** |
| 108 |
* Mirror logsv2 path: if the NULL backlog is fully drained, flip the |
| 109 |
* completion flag so the hits-rebuild phase2 JOIN can drop the |
| 110 |
* redirects-side COALESCE wrap and probe idx_canonical_url directly. |
| 111 |
* Cheap LIMIT 1 probe -- IS NULL is sargable on a B-tree over a |
| 112 |
* nullable column. |
| 113 |
* |
| 114 |
* @param string $redirectsTable |
| 115 |
* @return void |
| 116 |
*/ |
| 117 |
private function maybeFlipRedirectsCanonicalUrlBackfillCompleteFlag(string $redirectsTable): void { |
| 118 |
if (!function_exists('get_option') |
| 119 |
|| get_option($this->getRedirectsCanonicalUrlBackfillCompleteOption())) { |
| 120 |
return; |
| 121 |
} |
| 122 |
$remainingProbe = $this->dbCore->queryAndGetResults( |
| 123 |
"SELECT 1 FROM " . $redirectsTable . " WHERE canonical_url IS NULL LIMIT 1" |
| 124 |
); |
| 125 |
$remainingRows = is_array($remainingProbe['rows'] ?? null) ? $remainingProbe['rows'] : []; |
| 126 |
$remainingError = isset($remainingProbe['last_error']) && is_string($remainingProbe['last_error']) ? $remainingProbe['last_error'] : ''; |
| 127 |
if ($remainingError !== '' || !empty($remainingRows) || !function_exists('update_option')) { |
| 128 |
return; |
| 129 |
} |
| 130 |
update_option($this->getRedirectsCanonicalUrlBackfillCompleteOption(), '1', false); |
| 131 |
$this->logger->infoMessage( |
| 132 |
"backfillRedirectsCanonicalUrl: backlog cleared -- flipped " . |
| 133 |
$this->getRedirectsCanonicalUrlBackfillCompleteOption() . |
| 134 |
"; phase2 JOIN can now drop the redirects COALESCE fallback." |
| 135 |
); |
| 136 |
} |
| 137 |
|
| 138 |
/** |
| 139 |
* Populate {wp_abj404_logsv2}.canonical_url for any rows still NULL, |
| 140 |
* one chunk at a time. Each chunk runs: |
| 141 |
* |
| 142 |
* UPDATE logsv2 SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM requested_url)) |
| 143 |
* WHERE canonical_url IS NULL LIMIT N |
| 144 |
* |
| 145 |
* Mirrors backfillRedirectsCanonicalUrl() with one budget difference -- |
| 146 |
* 15-second wall budget (vs 25 for redirects) so browser-triggered AJAX |
| 147 |
* drains stay bounded while the daily cron remains the silent backstop. On |
| 148 |
* a Bruno-class 250K-row backlog this converges in ~3-10 days on daily cron |
| 149 |
* alone, faster if the admin regularly visits the tab. |
| 150 |
* |
| 151 |
* Once the backlog is fully cleared (no rows where canonical_url IS NULL), |
| 152 |
* sets the abj404_logsv2_canonical_url_backfill_complete option so reads |
| 153 |
* can drop the COALESCE fallback in getRedirectsForViewTempTable.sql and |
| 154 |
* use the no-COALESCE form (logsv2.canonical_url = redirects.canonical_url). |
| 155 |
* |
| 156 |
* Skips silently when: |
| 157 |
* - the logsv2 table is missing (degraded site state) |
| 158 |
* - the canonical_url column is missing (column add hasn't happened |
| 159 |
* yet, e.g. immediately after upgrade before verifyColumns ran) |
| 160 |
* - the previous run errored -- repair flow surfaces the error |
| 161 |
* |
| 162 |
* @param ?float $timeBudgetSec Wall-clock budget for this invocation. When |
| 163 |
* null (the daily-cron path), uses LOGSV2_CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC |
| 164 |
* (15s) for fast unattended convergence. The browser-driven AJAX poller |
| 165 |
* passes a small value so each request stays well under its client timeout. |
| 166 |
* @return int Number of rows updated in this invocation. |
| 167 |
*/ |
| 168 |
public function backfillLogsv2CanonicalUrl(?float $timeBudgetSec = null): int { |
| 169 |
global $wpdb; |
| 170 |
if (!isset($wpdb)) { |
| 171 |
return 0; |
| 172 |
} |
| 173 |
$logsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 174 |
|
| 175 |
// SHOW TABLES existence probe -- same shape as in |
| 176 |
// backfillRedirectsCanonicalUrl(). Routing through queryAndGetResults |
| 177 |
// would log a benign "table doesn't exist" error on freshly-installed |
| 178 |
// sites before runInitialCreateTables() has run. |
| 179 |
// DAO-bypass-approved: schema existence probe -- see comment above. |
| 180 |
$found = $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($logsTable) . "'"); |
| 181 |
if ($found !== $logsTable) { |
| 182 |
return 0; |
| 183 |
} |
| 184 |
if ($this->columnExists($logsTable, 'canonical_url') !== true) { |
| 185 |
return 0; |
| 186 |
} |
| 187 |
|
| 188 |
$chunkSize = (int)$this->getCanonicalUrlBackfillChunkSize(); |
| 189 |
$timeBudget = $timeBudgetSec !== null |
| 190 |
? max(0.0, $timeBudgetSec) |
| 191 |
: (float)$this->getLogsv2CanonicalUrlBackfillTimeBudgetSec(); |
| 192 |
$start = abj_clock()->nowFloat(); |
| 193 |
$totalUpdated = 0; |
| 194 |
|
| 195 |
while ((abj_clock()->nowFloat() - $start) < $timeBudget) { |
| 196 |
$query = "UPDATE " . $logsTable . |
| 197 |
" SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM requested_url))" . |
| 198 |
" WHERE canonical_url IS NULL" . |
| 199 |
" LIMIT " . $chunkSize; |
| 200 |
|
| 201 |
$result = $this->dbCore->queryAndGetResults($query); |
| 202 |
$lastError = isset($result['last_error']) && is_string($result['last_error']) ? $result['last_error'] : ''; |
| 203 |
if ($lastError !== '') { |
| 204 |
$this->logger->warn("backfillLogsv2CanonicalUrl: stopping after error: " . $lastError); |
| 205 |
return $totalUpdated; |
| 206 |
} |
| 207 |
|
| 208 |
$rowsAffected = isset($result['rows_affected']) && is_numeric($result['rows_affected']) |
| 209 |
? (int)$result['rows_affected'] : 0; |
| 210 |
$totalUpdated += $rowsAffected; |
| 211 |
if ($rowsAffected < $chunkSize) { |
| 212 |
break; |
| 213 |
} |
| 214 |
} |
| 215 |
|
| 216 |
if ($totalUpdated > 0) { |
| 217 |
$this->logger->infoMessage(sprintf( |
| 218 |
"backfillLogsv2CanonicalUrl: populated canonical_url on %d logsv2 rows in %.2fs.", |
| 219 |
$totalUpdated, |
| 220 |
abj_clock()->nowFloat() - $start |
| 221 |
)); |
| 222 |
} |
| 223 |
|
| 224 |
// If the backlog is now drained, flip the completion flag so reads |
| 225 |
// can drop the COALESCE fallback. Cheap LIMIT 1 probe -- at most reads |
| 226 |
// one row's worth of data via the canonical_url IS NULL filter (uses |
| 227 |
// idx_canonical_url because IS NULL is sargable on a B-tree on a |
| 228 |
// nullable column). |
| 229 |
if (!get_option($this->getLogsv2CanonicalUrlBackfillCompleteOption())) { |
| 230 |
$remainingProbe = $this->dbCore->queryAndGetResults( |
| 231 |
"SELECT 1 FROM " . $logsTable . " WHERE canonical_url IS NULL LIMIT 1" |
| 232 |
); |
| 233 |
$remainingRows = is_array($remainingProbe['rows'] ?? null) ? $remainingProbe['rows'] : []; |
| 234 |
$remainingError = isset($remainingProbe['last_error']) && is_string($remainingProbe['last_error']) ? $remainingProbe['last_error'] : ''; |
| 235 |
if ($remainingError === '' && empty($remainingRows)) { |
| 236 |
update_option($this->getLogsv2CanonicalUrlBackfillCompleteOption(), '1', false); |
| 237 |
$this->logger->infoMessage( |
| 238 |
"backfillLogsv2CanonicalUrl: backlog cleared -- flipped " . |
| 239 |
$this->getLogsv2CanonicalUrlBackfillCompleteOption() . |
| 240 |
"; reads can now drop the COALESCE fallback." |
| 241 |
); |
| 242 |
} |
| 243 |
} |
| 244 |
|
| 245 |
return $totalUpdated; |
| 246 |
} |
| 247 |
|
| 248 |
/** |
| 249 |
* Cheap "does this column exist on this table" probe via SHOW COLUMNS. |
| 250 |
* Case-insensitive on the column name to match MySQL/MariaDB driver |
| 251 |
* variations in returned column-name casing. |
| 252 |
* |
| 253 |
* Three answers, not two: true, false, and null for "the server did not |
| 254 |
* tell me". Collapsing the third into false said "the column is absent" |
| 255 |
* whenever the read was refused, and the ensure*Column() helpers act on |
| 256 |
* absence by issuing ALTER TABLE ... ADD COLUMN -- so a denied read, a |
| 257 |
* connection lost mid-upgrade or a table caught mid-rename produced a blind |
| 258 |
* schema change against a table nothing had managed to introspect. Callers |
| 259 |
* therefore test against true or false explicitly; null means "leave it |
| 260 |
* alone and look again next tick". |
| 261 |
* |
| 262 |
* @param string $tableName Fully-qualified table name. |
| 263 |
* @param string $columnName Column to look for. |
| 264 |
* @return bool|null |
| 265 |
*/ |
| 266 |
public function columnExists(string $tableName, string $columnName): ?bool { |
| 267 |
$result = $this->dbCore->queryAndGetResults("SHOW COLUMNS FROM " . $tableName, |
| 268 |
array('log_errors' => false)); |
| 269 |
$lastError = isset($result['last_error']) && is_scalar($result['last_error']) |
| 270 |
? (string)$result['last_error'] : ''; |
| 271 |
if ($lastError !== '' || !is_array($result['rows'] ?? null) || empty($result['rows'])) { |
| 272 |
// Unreadable, or a live table reporting no columns at all, which is |
| 273 |
// not a state a real table can be in and so is not an answer either. |
| 274 |
return null; |
| 275 |
} |
| 276 |
$rows = $result['rows']; |
| 277 |
$needle = strtolower($columnName); |
| 278 |
foreach ($rows as $row) { |
| 279 |
if (!is_array($row)) { continue; } |
| 280 |
foreach ($row as $key => $value) { |
| 281 |
if (strtolower((string)$key) !== 'field' || !is_scalar($value)) { continue; } |
| 282 |
if (strtolower((string)$value) === $needle) { |
| 283 |
return true; |
| 284 |
} |
| 285 |
} |
| 286 |
} |
| 287 |
return false; |
| 288 |
} |
| 289 |
|
| 290 |
/** |
| 291 |
* Add the canonical_url column to logsv2 with online DDL when supported. |
| 292 |
* |
| 293 |
* A small idempotent helper that runs ahead of the generic |
| 294 |
* verifyColumns() flow so the column add can use |
| 295 |
* ALGORITHM=INPLACE, LOCK=NONE on InnoDB 5.6 or newer (no table lock during the |
| 296 |
* rewrite). On engines that don't support online DDL for ADD COLUMN the |
| 297 |
* explicit clause causes the statement to fail with |
| 298 |
* ER_ALTER_OPERATION_NOT_SUPPORTED, so we fall back to a bare ALTER, which |
| 299 |
* is what verifyColumns() also runs as the safety net. |
| 300 |
* |
| 301 |
* The matching idx_canonical_url is added by the standard |
| 302 |
* ABJ_404_Solution_DatabaseUpgradeIndexes::verifyIndexes() flow. Index adds |
| 303 |
* use online DDL by default on InnoDB 5.6 or newer, so a separate ensure |
| 304 |
* helper isn't required for the index. |
| 305 |
* |
| 306 |
* @param string $logsTable |
| 307 |
* @return void |
| 308 |
*/ |
| 309 |
public function ensureLogsv2CanonicalUrlColumn(string $logsTable): void { |
| 310 |
if ($this->columnExists($logsTable, 'canonical_url') !== false) { |
| 311 |
// Present, or unknown. Only a definite absence justifies an ALTER. |
| 312 |
return; |
| 313 |
} |
| 314 |
$inplaceQuery = "ALTER TABLE " . $logsTable . |
| 315 |
" ADD COLUMN `canonical_url` VARCHAR(2048) DEFAULT NULL," . |
| 316 |
" ALGORITHM=INPLACE, LOCK=NONE"; |
| 317 |
$result = $this->dbCore->queryAndGetResults($inplaceQuery, |
| 318 |
array('log_too_slow' => false, 'log_errors' => false)); |
| 319 |
if (empty($result['last_error'])) { |
| 320 |
$this->logger->infoMessage("Added canonical_url to {$logsTable} (ALGORITHM=INPLACE, LOCK=NONE)."); |
| 321 |
return; |
| 322 |
} |
| 323 |
$lastError = isset($result['last_error']) && is_scalar($result['last_error']) |
| 324 |
? (string)$result['last_error'] : ''; |
| 325 |
if ($this->schemaChangeWasAlreadyApplied($lastError)) { |
| 326 |
// Another request added it between the columnExists() probe above |
| 327 |
// and this ALTER. The bare fallback would meet the same column and |
| 328 |
// the same answer, so the column is there and there is nothing to |
| 329 |
// fall back to. |
| 330 |
$this->logger->infoMessage("canonical_url on {$logsTable} was added by another process " . |
| 331 |
"while this one was adding it."); |
| 332 |
return; |
| 333 |
} |
| 334 |
// Engine didn't support online DDL for ADD COLUMN, so the bare ALTER |
| 335 |
// falls back to whatever algorithm the engine picks (COPY on MyISAM / very |
| 336 |
// old InnoDB). On modern InnoDB the bare ALTER is itself implicitly |
| 337 |
// INPLACE for ADD COLUMN ... DEFAULT NULL, so this branch only runs |
| 338 |
// on legacy engines where some lock is unavoidable. |
| 339 |
$bareQuery = "ALTER TABLE " . $logsTable . |
| 340 |
" ADD COLUMN `canonical_url` VARCHAR(2048) DEFAULT NULL"; |
| 341 |
$bare = $this->dbCore->queryAndGetResults($bareQuery, |
| 342 |
array('log_too_slow' => false)); |
| 343 |
if (empty($bare['last_error'])) { |
| 344 |
$this->logger->infoMessage("Added canonical_url to {$logsTable} (bare ALTER fallback)."); |
| 345 |
} |
| 346 |
} |
| 347 |
|
| 348 |
/** |
| 349 |
* Add the canonical_url column to the redirects table with online DDL |
| 350 |
* when supported. |
| 351 |
* |
| 352 |
* Sibling of {@see ensureLogsv2CanonicalUrlColumn()} applied to the |
| 353 |
* redirects side. The column shipped in 4.1.11 and is normally added by dbDelta |
| 354 |
* on plugin update. On hosts where dbDelta silently fails to ALTER ADD |
| 355 |
* it, every captured-404 INSERT errors out with "Unknown column |
| 356 |
* 'canonical_url' in 'field list'" until verifyColumns eventually |
| 357 |
* retries the column add. One site in the May 10 debug zip emitted |
| 358 |
* 1671 such errors over 10 days on 4.1.12. Calling this helper eagerly |
| 359 |
* from runInitialCreateTables() shortens that window: every cron tick |
| 360 |
* that runs the bootstrap loop retries the ALTER on its own, |
| 361 |
* independent of the verifyColumns DDL diff path. |
| 362 |
* |
| 363 |
* @param string $redirectsTable |
| 364 |
* @return void |
| 365 |
*/ |
| 366 |
public function ensureRedirectsCanonicalUrlColumn(string $redirectsTable): void { |
| 367 |
if ($this->columnExists($redirectsTable, 'canonical_url') !== false) { |
| 368 |
// Present, or unknown. Only a definite absence justifies an ALTER. |
| 369 |
return; |
| 370 |
} |
| 371 |
$inplaceQuery = "ALTER TABLE " . $redirectsTable . |
| 372 |
" ADD COLUMN `canonical_url` VARCHAR(2048) DEFAULT NULL," . |
| 373 |
" ALGORITHM=INPLACE, LOCK=NONE"; |
| 374 |
$result = $this->dbCore->queryAndGetResults($inplaceQuery, |
| 375 |
array('log_too_slow' => false, 'log_errors' => false)); |
| 376 |
if (empty($result['last_error'])) { |
| 377 |
$this->logger->infoMessage("Added canonical_url to {$redirectsTable} (ALGORITHM=INPLACE, LOCK=NONE)."); |
| 378 |
return; |
| 379 |
} |
| 380 |
$lastError = isset($result['last_error']) && is_scalar($result['last_error']) |
| 381 |
? (string)$result['last_error'] : ''; |
| 382 |
if ($this->schemaChangeWasAlreadyApplied($lastError)) { |
| 383 |
// Another request added it between the columnExists() probe above |
| 384 |
// and this ALTER. The bare fallback would meet the same column and |
| 385 |
// the same answer, so the column is there and there is nothing to |
| 386 |
// fall back to. |
| 387 |
$this->logger->infoMessage("canonical_url on {$redirectsTable} was added by another process " . |
| 388 |
"while this one was adding it."); |
| 389 |
return; |
| 390 |
} |
| 391 |
$bareQuery = "ALTER TABLE " . $redirectsTable . |
| 392 |
" ADD COLUMN `canonical_url` VARCHAR(2048) DEFAULT NULL"; |
| 393 |
$bare = $this->dbCore->queryAndGetResults($bareQuery, |
| 394 |
array('log_too_slow' => false)); |
| 395 |
if (empty($bare['last_error'])) { |
| 396 |
$this->logger->infoMessage("Added canonical_url to {$redirectsTable} (bare ALTER fallback)."); |
| 397 |
} |
| 398 |
} |
| 399 |
} |
| 400 |
|
