| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
trait ABJ_404_Solution_DatabaseUpgradesEtc_MaintenanceTrait { |
| 8 |
|
| 9 |
/** @return void */ |
| 10 |
function updateTableEngineToInnoDB() { |
| 11 |
// get a list of all tables. |
| 12 |
global $wpdb; |
| 13 |
$result = $this->dao->getTableEngines(); |
| 14 |
// if any rows are found then update the tables. |
| 15 |
$resultRows = isset($result['rows']) && is_array($result['rows']) ? $result['rows'] : []; |
| 16 |
if (!empty($resultRows)) { |
| 17 |
foreach ($resultRows as $row) { |
| 18 |
if (!is_array($row)) { |
| 19 |
continue; |
| 20 |
} |
| 21 |
$tableName = array_key_exists('table_name', $row) ? (string)$row['table_name'] : |
| 22 |
(array_key_exists('TABLE_NAME', $row) ? (string)$row['TABLE_NAME'] : ''); |
| 23 |
$engine = array_key_exists('engine', $row) ? (string)$row['engine'] : |
| 24 |
(array_key_exists('ENGINE', $row) ? (string)$row['ENGINE'] : ''); |
| 25 |
|
| 26 |
$query = null; |
| 27 |
// All plugin tables use InnoDB: crash-safe, no row-count ceiling, no table-level |
| 28 |
// locking. The former MyISAM special-case for logsv2 ("OPTIMIZE TABLE is slow |
| 29 |
// otherwise") no longer applies — OPTIMIZE TABLE on InnoDB has been equivalent to |
| 30 |
// ALTER TABLE ... ENGINE=InnoDB since MySQL 5.6 (rebuilds tablespace in-place). |
| 31 |
// InnoDB also eliminates the MyISAM-specific "table is full" failure mode on sites |
| 32 |
// with disk pressure (MyISAM .MYI files cannot grow past 4 GiB by default). |
| 33 |
if (strtolower($engine) != 'innodb') { |
| 34 |
$this->logger->infoMessage("Updating " . $tableName . " to InnoDB."); |
| 35 |
$query = 'alter table `' . $tableName . '` engine = InnoDB;'; |
| 36 |
} |
| 37 |
|
| 38 |
if ($query == null) { |
| 39 |
// no updates are necessary for this table. |
| 40 |
continue; |
| 41 |
} |
| 42 |
|
| 43 |
$result = $this->dbCore->queryAndGetResults($query, array("log_errors" => false)); |
| 44 |
$this->logger->infoMessage("I changed an engine: " . $query); |
| 45 |
$lastError = isset($result['last_error']) && is_string($result['last_error']) ? $result['last_error'] : ''; |
| 46 |
|
| 47 |
if ($lastError !== '' && |
| 48 |
strpos($lastError, 'Index column size too large') !== false) { |
| 49 |
|
| 50 |
// delete the indexes, try again, and create the indexes later. |
| 51 |
$this->deleteIndexes($tableName); |
| 52 |
|
| 53 |
$this->dbCore->queryAndGetResults($query, |
| 54 |
array("ignore_errors" => array("Unknown storage engine"))); |
| 55 |
$this->logger->infoMessage("I tried to change an engine again: " . $query); |
| 56 |
} |
| 57 |
} |
| 58 |
} |
| 59 |
} |
| 60 |
|
| 61 |
/** Retrieve the collation for a given table name. |
| 62 |
* @param string $tableName |
| 63 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if retrieval failed. |
| 64 |
*/ |
| 65 |
function getTableCollation($tableName) { |
| 66 |
// Try SHOW CREATE TABLE first |
| 67 |
$result = $this->getTableCollationFromShowCreate($tableName); |
| 68 |
|
| 69 |
if ($result !== null) { |
| 70 |
return $result; |
| 71 |
} |
| 72 |
|
| 73 |
// Fallback to information_schema query |
| 74 |
$result = $this->getTableCollationFromInformationSchema($tableName); |
| 75 |
|
| 76 |
if ($result !== null) { |
| 77 |
return $result; |
| 78 |
} |
| 79 |
|
| 80 |
$this->logger->warn("Could not retrieve collation for $tableName from SHOW CREATE TABLE or information_schema."); |
| 81 |
return null; |
| 82 |
} |
| 83 |
|
| 84 |
/** Parse collation/charset from SHOW CREATE TABLE output. |
| 85 |
* @param string $tableName |
| 86 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if parsing failed. |
| 87 |
*/ |
| 88 |
function getTableCollationFromShowCreate($tableName) { |
| 89 |
$query = "SHOW CREATE TABLE `$tableName`"; |
| 90 |
$results = $this->dbCore->queryAndGetResults($query); |
| 91 |
|
| 92 |
// Check for query errors or empty results |
| 93 |
if (!empty($results['last_error'])) { |
| 94 |
$this->logger->debugMessage("SHOW CREATE TABLE failed for $tableName: " . $results['last_error']); |
| 95 |
return null; |
| 96 |
} |
| 97 |
|
| 98 |
$rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; |
| 99 |
$firstRow = isset($rows[0]) && is_array($rows[0]) ? $rows[0] : null; |
| 100 |
if ($firstRow === null) { |
| 101 |
$this->logger->debugMessage("SHOW CREATE TABLE returned no data for $tableName."); |
| 102 |
return null; |
| 103 |
} |
| 104 |
|
| 105 |
// Use array_values to handle varying column name cases ('Create Table', 'CREATE TABLE', etc.) |
| 106 |
// SHOW CREATE TABLE returns: [table_name, create_statement] |
| 107 |
$row = array_values($firstRow); |
| 108 |
if (count($row) < 2 || empty($row[1])) { |
| 109 |
$this->logger->debugMessage("SHOW CREATE TABLE returned unexpected format for $tableName."); |
| 110 |
return null; |
| 111 |
} |
| 112 |
|
| 113 |
$createTableSQL = $row[1]; |
| 114 |
|
| 115 |
// Match multiple MySQL/MariaDB output formats for charset: |
| 116 |
// - CHARSET=utf8mb4 |
| 117 |
// - DEFAULT CHARSET=utf8mb4 |
| 118 |
// - CHARACTER SET=utf8mb4 |
| 119 |
// - DEFAULT CHARACTER SET=utf8mb4 |
| 120 |
// - CHARACTER SET utf8mb4 (no equals sign, space separator) |
| 121 |
// - CHARSET = utf8mb4 (spaces around equals) |
| 122 |
// Note: (?:\s*=\s*|\s+) requires either "=" (with optional spaces) or at least one space |
| 123 |
preg_match('/(?:DEFAULT\s+)?(?:CHARSET|CHARACTER\s+SET)(?:\s*=\s*|\s+)([\w\d]+)/i', $createTableSQL, $charsetMatch); |
| 124 |
|
| 125 |
// Match multiple formats for collation: |
| 126 |
// - COLLATE=utf8mb4_unicode_ci |
| 127 |
// - DEFAULT COLLATE=utf8mb4_unicode_ci |
| 128 |
// - COLLATE utf8mb4_unicode_ci (no equals sign, space separator) |
| 129 |
// - COLLATE = utf8mb4_unicode_ci (spaces around equals) |
| 130 |
preg_match('/(?:DEFAULT\s+)?COLLATE(?:\s*=\s*|\s+)([\w\d_]+)/i', $createTableSQL, $collationMatch); |
| 131 |
|
| 132 |
$charset = $charsetMatch[1] ?? null; |
| 133 |
$collation = $collationMatch[1] ?? null; |
| 134 |
|
| 135 |
// If we got charset but no explicit collation, derive default collation from charset |
| 136 |
if ($charset && !$collation) { |
| 137 |
$collation = $this->getDefaultCollationForCharset($charset); |
| 138 |
} |
| 139 |
|
| 140 |
return ($collation && $charset) ? [$collation, $charset] : null; |
| 141 |
} |
| 142 |
|
| 143 |
/** Query information_schema for table collation (fallback method). |
| 144 |
* @param string $tableName |
| 145 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if query failed. |
| 146 |
*/ |
| 147 |
function getTableCollationFromInformationSchema($tableName) { |
| 148 |
global $wpdb; |
| 149 |
|
| 150 |
$queryResult = $this->dbCore->queryAndGetResults( |
| 151 |
"SELECT TABLE_COLLATION, " . |
| 152 |
"SUBSTRING_INDEX(TABLE_COLLATION, '_', 1) as TABLE_CHARSET " . |
| 153 |
"FROM information_schema.tables " . |
| 154 |
"WHERE TABLE_NAME = %s AND TABLE_SCHEMA = DATABASE()", |
| 155 |
['query_params' => [$tableName]] |
| 156 |
); |
| 157 |
|
| 158 |
$lastError = isset($queryResult['last_error']) && is_string($queryResult['last_error']) ? $queryResult['last_error'] : ''; |
| 159 |
if ($lastError !== '') { |
| 160 |
$this->logger->debugMessage("information_schema query failed for $tableName: " . $lastError); |
| 161 |
return null; |
| 162 |
} |
| 163 |
|
| 164 |
$results = isset($queryResult['rows']) && is_array($queryResult['rows']) ? $queryResult['rows'] : []; |
| 165 |
if (empty($results) || !is_array($results[0])) { |
| 166 |
$this->logger->debugMessage("Table $tableName not found in information_schema (may not exist)."); |
| 167 |
return null; |
| 168 |
} |
| 169 |
|
| 170 |
// Handle case-insensitive column names (some MySQL configs return uppercase) |
| 171 |
$row = array_change_key_case($results[0], CASE_UPPER); |
| 172 |
$collation = $row['TABLE_COLLATION'] ?? null; |
| 173 |
$charset = $row['TABLE_CHARSET'] ?? null; |
| 174 |
|
| 175 |
if (empty($collation)) { |
| 176 |
return null; |
| 177 |
} |
| 178 |
|
| 179 |
// Handle edge case where charset extraction might fail |
| 180 |
if (empty($charset)) { |
| 181 |
$charset = explode('_', $collation)[0]; |
| 182 |
} |
| 183 |
|
| 184 |
return [$collation, $charset]; |
| 185 |
} |
| 186 |
|
| 187 |
/** Get the default collation for a given charset. |
| 188 |
* @param string $charset |
| 189 |
* @return string|null Default collation or null if unknown. |
| 190 |
*/ |
| 191 |
function getDefaultCollationForCharset($charset) { |
| 192 |
// Common charset to default collation mappings |
| 193 |
$defaults = [ |
| 194 |
'utf8mb4' => 'utf8mb4_general_ci', |
| 195 |
'utf8' => 'utf8_general_ci', |
| 196 |
'utf8mb3' => 'utf8mb3_general_ci', |
| 197 |
'latin1' => 'latin1_swedish_ci', |
| 198 |
'ascii' => 'ascii_general_ci', |
| 199 |
]; |
| 200 |
|
| 201 |
$charsetLower = strtolower($charset); |
| 202 |
return $defaults[$charsetLower] ?? null; |
| 203 |
} |
| 204 |
|
| 205 |
/** |
| 206 |
* Keep collation identifiers SQL-safe. |
| 207 |
* |
| 208 |
* @param string $collation |
| 209 |
* @return string |
| 210 |
*/ |
| 211 |
private function sanitizeCollationIdentifier($collation) { |
| 212 |
if (!is_string($collation) || $collation === '') { |
| 213 |
return ''; |
| 214 |
} |
| 215 |
return preg_replace('/[^A-Za-z0-9_]/', '', $collation) ?? ''; |
| 216 |
} |
| 217 |
|
| 218 |
/** |
| 219 |
* Resolve the utf8mb4 collation target for plugin-table normalization. |
| 220 |
* |
| 221 |
* Priority: |
| 222 |
* 1) Active wpdb connection collation if utf8mb4 |
| 223 |
* 2) Most common existing utf8mb4 plugin-table collation |
| 224 |
* 3) Database default collation variable if utf8mb4 |
| 225 |
* 4) Safe fallback (utf8mb4_unicode_ci) |
| 226 |
* |
| 227 |
* @param array<int, string> $tableNames |
| 228 |
* @param array<string, array{0: string, 1: string}|null> $tableCollations Optional map: table => [collation, charset] |
| 229 |
* @return string |
| 230 |
*/ |
| 231 |
private function resolveTargetUtf8mb4Collation($tableNames, $tableCollations = []) { |
| 232 |
global $wpdb; |
| 233 |
|
| 234 |
if (!empty($wpdb->collate)) { |
| 235 |
$wpdbCollation = $this->sanitizeCollationIdentifier((string)$wpdb->collate); |
| 236 |
if ($wpdbCollation !== '' && stripos($wpdbCollation, 'utf8mb4') !== false) { |
| 237 |
return $wpdbCollation; |
| 238 |
} |
| 239 |
} |
| 240 |
|
| 241 |
$counts = []; |
| 242 |
foreach ($tableNames as $tableName) { |
| 243 |
$row = $tableCollations[$tableName] ?? $this->getTableCollation($tableName); |
| 244 |
if (!is_array($row)) { |
| 245 |
continue; |
| 246 |
} |
| 247 |
$collation = $this->sanitizeCollationIdentifier((string)$row[0]); |
| 248 |
$charset = strtolower((string)$row[1]); |
| 249 |
if ($collation !== '' && $charset === 'utf8mb4' && stripos($collation, 'utf8mb4') !== false) { |
| 250 |
$counts[$collation] = ($counts[$collation] ?? 0) + 1; |
| 251 |
} |
| 252 |
} |
| 253 |
if (!empty($counts)) { |
| 254 |
arsort($counts); |
| 255 |
return array_key_first($counts); |
| 256 |
} |
| 257 |
|
| 258 |
$vars = $this->dbCore->queryAndGetResults("SHOW VARIABLES LIKE 'collation_database'"); |
| 259 |
$varRows = isset($vars['rows']) && is_array($vars['rows']) ? $vars['rows'] : []; |
| 260 |
if (!empty($varRows)) { |
| 261 |
$row = is_array($varRows[0]) ? $varRows[0] : []; |
| 262 |
$value = isset($row['Value']) ? $row['Value'] : (isset($row['value']) ? $row['value'] : ''); |
| 263 |
$value = $this->sanitizeCollationIdentifier((string)$value); |
| 264 |
if ($value !== '' && stripos($value, 'utf8mb4') !== false) { |
| 265 |
return $value; |
| 266 |
} |
| 267 |
} |
| 268 |
|
| 269 |
return 'utf8mb4_unicode_ci'; |
| 270 |
} |
| 271 |
|
| 272 |
/** |
| 273 |
* Ensure our tables use utf8mb4 (do not alter WordPress core tables). |
| 274 |
* @return void |
| 275 |
*/ |
| 276 |
function correctCollations() { |
| 277 |
global $wpdb; |
| 278 |
|
| 279 |
// Discover all plugin tables dynamically so new tables are automatically included. |
| 280 |
// Use queryAndGetResults() so the SHOW TABLES call goes through the same DAO |
| 281 |
// layer as all other queries (enables testability via mock injection). |
| 282 |
// {wp_prefix} is resolved by doTableNameReplacements inside queryAndGetResults. |
| 283 |
$rawResult = $this->dbCore->queryAndGetResults("SHOW TABLES LIKE '{wp_prefix}abj404_%'"); |
| 284 |
$abjTableNames = []; |
| 285 |
if (isset($rawResult['rows']) && is_array($rawResult['rows'])) { |
| 286 |
foreach ($rawResult['rows'] as $row) { |
| 287 |
$abjTableNames[] = is_array($row) ? reset($row) : (string)$row; |
| 288 |
} |
| 289 |
} |
| 290 |
|
| 291 |
$tableCollations = []; |
| 292 |
foreach ($abjTableNames as $tableName) { |
| 293 |
$tableCollations[$tableName] = $this->getTableCollation($tableName); |
| 294 |
} |
| 295 |
|
| 296 |
$targetCharset = 'utf8mb4'; |
| 297 |
$targetCollation = $this->resolveTargetUtf8mb4Collation($abjTableNames, $tableCollations); |
| 298 |
|
| 299 |
// Track whether any ALTER actually fired. Drift correction can change |
| 300 |
// the byte-level representation of redirect URLs (latin1 -> utf8mb4), |
| 301 |
// which means a snapshot built against the pre-correction encoding |
| 302 |
// could mis-compare against post-correction lookups. The watermark |
| 303 |
// bump signals "source data changed; reconsider at the next stage |
| 304 |
// boundary" without dropping in-flight runner state. BATCHED policy |
| 305 |
// per refactor-staged-view-build-watermark.md: one bump per handler |
| 306 |
// regardless of how many tables ALTERed. |
| 307 |
$anyAlterFired = false; |
| 308 |
|
| 309 |
foreach ($abjTableNames as $tableName) { |
| 310 |
$abjTableData = $tableCollations[$tableName] ?? null; |
| 311 |
|
| 312 |
if ($abjTableData === null) { |
| 313 |
$this->logger->warn("Failed to retrieve collation for $tableName."); |
| 314 |
continue; // Skip this table if collation can't be determined |
| 315 |
} |
| 316 |
|
| 317 |
[$abjTableCollation, $abjTableCharset] = $abjTableData; |
| 318 |
|
| 319 |
$needsUpdate = !($abjTableCharset === $targetCharset && $abjTableCollation === $targetCollation); |
| 320 |
if (!$needsUpdate) { |
| 321 |
// Table default matches, but individual columns can still drift (e.g., some columns left as *_bin). |
| 322 |
$columnMismatch = $this->tableHasMismatchedCharacterColumnCollation($tableName, $targetCharset, $targetCollation); |
| 323 |
if ($columnMismatch === true) { |
| 324 |
$needsUpdate = true; |
| 325 |
$this->logger->infoMessage("Detected column-level collation mismatch on {$tableName}; normalizing to {$targetCharset}/{$targetCollation}"); |
| 326 |
} else if ($columnMismatch === null) { |
| 327 |
$this->logger->warn("Could not verify column collations for {$tableName}; skipping collation normalization."); |
| 328 |
continue; |
| 329 |
} |
| 330 |
} |
| 331 |
if (!$needsUpdate) { |
| 332 |
continue; |
| 333 |
} |
| 334 |
|
| 335 |
$this->logger->infoMessage("Updating charset/collation on {$tableName} from {$abjTableCharset}/{$abjTableCollation} to {$targetCharset}/{$targetCollation}"); |
| 336 |
|
| 337 |
$query = "ALTER TABLE {table_name} CONVERT TO CHARSET " . $targetCharset . |
| 338 |
" COLLATE " . $targetCollation; |
| 339 |
$query = str_replace('{table_name}', $tableName, $query); |
| 340 |
$results = $this->dbCore->queryAndGetResults($query, |
| 341 |
array('ignore_errors' => array("Index column size too large"))); |
| 342 |
|
| 343 |
$lastErr = isset($results['last_error']) && is_string($results['last_error']) ? $results['last_error'] : ''; |
| 344 |
if ($lastErr !== '' && |
| 345 |
strpos($lastErr, "Index column size too large") !== false) { |
| 346 |
|
| 347 |
$this->logger->warn("Charset/collation change for $tableName failed: Index column size too large. Deleting indexes and retrying..."); |
| 348 |
|
| 349 |
// delete indexes and try again. |
| 350 |
$this->deleteIndexes($tableName); |
| 351 |
|
| 352 |
$retryResults = $this->dbCore->queryAndGetResults($query); |
| 353 |
if (!empty($retryResults['last_error'])) { |
| 354 |
$this->logger->warn("Charset/collation retry for $tableName failed: " . $retryResults['last_error']); |
| 355 |
} else { |
| 356 |
$this->logger->infoMessage("Successfully changed charset/collation of $tableName after retry."); |
| 357 |
$anyAlterFired = true; |
| 358 |
} |
| 359 |
|
| 360 |
} else if (empty($results['last_error'])) { |
| 361 |
$this->logger->infoMessage("Successfully changed charset/collation of $tableName to {$targetCharset}/{$targetCollation}"); |
| 362 |
$anyAlterFired = true; |
| 363 |
} else { |
| 364 |
$this->logger->warn("Charset/collation change for $tableName failed: " . $results['last_error']); |
| 365 |
} |
| 366 |
} |
| 367 |
|
| 368 |
if ($anyAlterFired) { |
| 369 |
// Post-correction state may render or compare differently from the |
| 370 |
// pre-correction view_done snapshot. The bump is non-destructive |
| 371 |
// (no DROP, no progress clear) so it cannot race an in-flight |
| 372 |
// staged build: the runner reads the watermark at the next stage |
| 373 |
// boundary and either aborts cleanly or completes a build whose |
| 374 |
// built_watermark covers the post-correction data. |
| 375 |
$this->viewBuild->bumpMutationWatermark(); |
| 376 |
} |
| 377 |
} |
| 378 |
|
| 379 |
/** |
| 380 |
* Detect character column collation drift on a table. |
| 381 |
* |
| 382 |
* Some environments can end up with per-column collations that differ from the table default |
| 383 |
* (e.g., `utf8mb4_bin` on one VARCHAR column while the table default is `utf8mb4_unicode_520_ci`). |
| 384 |
* This causes MySQL errors in string operations (REPLACE/LOWER) that mix collations. |
| 385 |
* |
| 386 |
* @param string $tableName Fully qualified table name (with prefix) |
| 387 |
* @param string $targetCharset Expected charset (e.g., utf8mb4) |
| 388 |
* @param string $targetCollation Expected collation (e.g., utf8mb4_unicode_ci) |
| 389 |
* @return bool|null True if mismatch found, false if all match, null if query failed |
| 390 |
*/ |
| 391 |
private function tableHasMismatchedCharacterColumnCollation($tableName, $targetCharset, $targetCollation) { |
| 392 |
$results = $this->dbCore->queryAndGetResults("SHOW FULL COLUMNS FROM " . $tableName); |
| 393 |
if (!empty($results['last_error'])) { |
| 394 |
$this->logger->warn("Failed to read columns for {$tableName}: " . $results['last_error']); |
| 395 |
return null; |
| 396 |
} |
| 397 |
/** @var array<int, array<string, mixed>> $rows */ |
| 398 |
$rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; |
| 399 |
if (empty($rows)) { |
| 400 |
return false; |
| 401 |
} |
| 402 |
|
| 403 |
$collationKey = null; |
| 404 |
$firstRow = $rows[0]; |
| 405 |
foreach (array_keys($firstRow) as $key) { |
| 406 |
if ($this->f->strtolower((string)$key) === 'collation') { |
| 407 |
$collationKey = $key; |
| 408 |
break; |
| 409 |
} |
| 410 |
} |
| 411 |
if ($collationKey === null) { |
| 412 |
$this->logger->warn("SHOW FULL COLUMNS returned no Collation column for {$tableName}"); |
| 413 |
return null; |
| 414 |
} |
| 415 |
|
| 416 |
foreach ($rows as $row) { |
| 417 |
if (!is_array($row)) { |
| 418 |
continue; |
| 419 |
} |
| 420 |
$rawColCollation = $row[$collationKey] ?? null; |
| 421 |
if ($rawColCollation === null || !is_string($rawColCollation) || trim($rawColCollation) === '') { |
| 422 |
continue; // Non-character columns |
| 423 |
} |
| 424 |
$colCollation = trim($rawColCollation); |
| 425 |
$colCharset = explode('_', $colCollation)[0] ?? ''; |
| 426 |
|
| 427 |
if ($colCharset !== $targetCharset || $colCollation !== $targetCollation) { |
| 428 |
return true; |
| 429 |
} |
| 430 |
} |
| 431 |
|
| 432 |
return false; |
| 433 |
} |
| 434 |
|
| 435 |
/** @return void */ |
| 436 |
public function runDailyInsuranceCheck() { |
| 437 |
// Always verify current site only |
| 438 |
// Per-site cron execution ensures network coverage without O(N²) duplication |
| 439 |
$this->verifyAndRepairCurrentSite(); |
| 440 |
} |
| 441 |
|
| 442 |
/** |
| 443 |
* Self-healing boot prologue. The single named token every "heavy boot" |
| 444 |
* entry point routes through before doing useful work. |
| 445 |
* |
| 446 |
* Pattern 1 in docs/PROACTIVE_BUG_DISCOVERY.md: bugs recurred because each |
| 447 |
* recovery primitive (repairStrippedViewCacheTable, updateTableEngineToInnoDB, |
| 448 |
* createIndexes, correctCollations, adoptOrphanedTables) was reachable from |
| 449 |
* one boot path only. Activation reached some, the daily cron reached |
| 450 |
* others, fresh installs reached different subsets. Centralising the |
| 451 |
* fan-out here makes the reachability invariant testable |
| 452 |
* (SelfHealingPrologueReachabilityTest) instead of relying on a developer |
| 453 |
* to remember to wire every primitive into every new entry point. |
| 454 |
* |
| 455 |
* Documented order, delegated to verifyAndRepairCurrentSite() (the |
| 456 |
* existing per-site insurance check): |
| 457 |
* |
| 458 |
* 1. Discover required tables from create*Table.sql files (same source |
| 459 |
* of truth as runInitialCreateTables()). |
| 460 |
* 2. If ANY table is missing, call createDatabaseTables(false), which |
| 461 |
* funnels through reallyCreateDatabaseTables() into runInitialCreateTables(), |
| 462 |
* reaching: |
| 463 |
* a. repairStrippedViewCacheTable() (3.3.3 column-drop recovery), |
| 464 |
* b. verifyTableMaterialized() (d9024114 per-table CREATE verify), |
| 465 |
* c. verifyColumns() (schema-drift tolerance), |
| 466 |
* then updateTableEngineToInnoDB(), correctCollations(), createIndexes(), |
| 467 |
* and renameAbj404TablesToLowerCase() into adoptOrphanedTables(). |
| 468 |
* 3. If all tables exist, run the drift-correction sweep: |
| 469 |
* a. correctCollations() (utf8mb4 drift), |
| 470 |
* b. createIndexes() (lost-index recovery after hosting migrations), |
| 471 |
* c. updateTableEngineToInnoDB() (MyISAM reversion recovery). |
| 472 |
* 4. adoptOrphanedTables() covers prefix migrations even when tables |
| 473 |
* under the current prefix exist. |
| 474 |
* |
| 475 |
* Idempotent: safe to call multiple times in the same request. The |
| 476 |
* SHOW TABLES check makes the tables-exist branch cheap (~1ms per table). |
| 477 |
* |
| 478 |
* Light-path entry points (frontend 404 dispatch, admin AJAX, REST, |
| 479 |
* WP-CLI commands, on-demand caches like permalink-cache rebuild) opt |
| 480 |
* out via the SelfHealingPrologueReachabilityTest allowlist because |
| 481 |
* (a) the prologue runs nightly via the daily cron tick so drift is |
| 482 |
* caught within 24h, and (b) running it on every request would be a |
| 483 |
* perf regression and risks cron-lock contention under high traffic. |
| 484 |
* Those paths rely on |
| 485 |
* `queryAndGetResults::attemptMissingTableRepairAndRetry()` for |
| 486 |
* per-query recovery instead. |
| 487 |
* |
| 488 |
* @return void |
| 489 |
*/ |
| 490 |
public function runSelfHealPrologue() { |
| 491 |
$this->verifyAndRepairCurrentSite(); |
| 492 |
} |
| 493 |
|
| 494 |
/** |
| 495 |
* Verify and repair tables for the current site only. |
| 496 |
* |
| 497 |
* Derives the list of required tables dynamically from create*Table.sql files |
| 498 |
* (same source of truth as runInitialCreateTables()), so new tables are |
| 499 |
* automatically included without any code changes here. |
| 500 |
* |
| 501 |
* If ANY table is missing, triggers full table creation/repair. |
| 502 |
* |
| 503 |
* @return void |
| 504 |
*/ |
| 505 |
private function verifyAndRepairCurrentSite() { |
| 506 |
global $wpdb; |
| 507 |
|
| 508 |
// Derive required tables from SQL DDL files — same source of truth as runInitialCreateTables(). |
| 509 |
$requiredTables = []; |
| 510 |
foreach ($this->discoverPermanentDDLFiles() as $ddlEntry) { |
| 511 |
$requiredTables[] = $ddlEntry['bareTableName']; |
| 512 |
} |
| 513 |
|
| 514 |
$missingTables = []; |
| 515 |
$normalizedPrefix = $this->dbCore->getLowercasePrefix(); |
| 516 |
|
| 517 |
// Check each required table |
| 518 |
foreach ($requiredTables as $tableName) { |
| 519 |
$fullTableName = $this->dbCore->getPrefixedTableName($tableName); |
| 520 |
// DAO-bypass-approved: Schema-bootstrap inside repairMissingTables() — runs before CREATE TABLE; routing through DAO would trigger the same missing-table auto-repair we are about to invoke ourselves (recursion) |
| 521 |
$tableExists = $wpdb->get_var("SHOW TABLES LIKE '{$fullTableName}'"); |
| 522 |
|
| 523 |
if (!$tableExists) { |
| 524 |
$missingTables[] = $tableName; |
| 525 |
} |
| 526 |
} |
| 527 |
|
| 528 |
// If any tables are missing, run repair |
| 529 |
if (!empty($missingTables)) { |
| 530 |
$this->logger->infoMessage(sprintf( |
| 531 |
"Site %d (prefix: %s, normalized: %s) is missing %d table(s): %s. Running repair...", |
| 532 |
get_current_blog_id(), |
| 533 |
$wpdb->prefix, |
| 534 |
$normalizedPrefix, |
| 535 |
count($missingTables), |
| 536 |
implode(', ', $missingTables) |
| 537 |
)); |
| 538 |
|
| 539 |
// Repair: call the same idempotent routine activation uses |
| 540 |
// This is safe because createDatabaseTables() is idempotent |
| 541 |
$this->createDatabaseTables(false); // false = not updating to new version |
| 542 |
|
| 543 |
$this->logger->infoMessage("Table repair complete for site " . get_current_blog_id()); |
| 544 |
} else { |
| 545 |
// Tables exist - insurance: verify/correct collations, ensure indexes exist, |
| 546 |
// and enforce InnoDB engine. This catches collation drift (including column-level |
| 547 |
// drift), missed index additions, and MyISAM reversions from hosting migrations |
| 548 |
// or table restores — without waiting for the next plugin upgrade. |
| 549 |
$this->correctCollations(); |
| 550 |
$this->createIndexes(); |
| 551 |
$this->updateTableEngineToInnoDB(); |
| 552 |
} |
| 553 |
|
| 554 |
// Check for orphaned tables under a stale/changed prefix and adopt their data. |
| 555 |
// This catches hosting migrations or wp-config prefix changes that leave plugin |
| 556 |
// tables under the old prefix. The method is idempotent — no-op when nothing to adopt. |
| 557 |
// (On the missing-tables path above, createDatabaseTables() already triggers adoption |
| 558 |
// via renameAbj404TablesToLowerCase(), but running it again is harmless and covers |
| 559 |
// edge cases where tables exist under the current prefix but orphans remain.) |
| 560 |
$this->adoptOrphanedTables(); |
| 561 |
} |
| 562 |
|
| 563 |
/** |
| 564 |
* Clean up expired rate limit transients from wp_options table. |
| 565 |
* |
| 566 |
* WordPress transients are supposed to auto-delete when they expire, but in practice |
| 567 |
* they can accumulate over time. This maintenance task removes expired rate limit |
| 568 |
* transients to prevent wp_options table bloat. |
| 569 |
* |
| 570 |
* Called during daily maintenance cron job. |
| 571 |
* |
| 572 |
* @return array<string, mixed> Statistics: ['deleted' => int, 'errors' => int] |
| 573 |
*/ |
| 574 |
function cleanupExpiredRateLimitTransients() { |
| 575 |
global $wpdb; |
| 576 |
|
| 577 |
$this->logger->debugMessage("Cleaning up expired rate limit transients..."); |
| 578 |
|
| 579 |
$stats = ['deleted' => 0, 'errors' => 0]; |
| 580 |
|
| 581 |
// Delete expired rate limit transients |
| 582 |
// WordPress stores transients as two rows: _transient_* and _transient_timeout_* |
| 583 |
// The timeout row contains the expiration timestamp |
| 584 |
// We delete both the value and timeout rows for expired transients |
| 585 |
|
| 586 |
$currentTime = time(); |
| 587 |
|
| 588 |
// Find all expired rate limit timeout keys |
| 589 |
// DAO-bypass-approved: WP-core wp_options probe; $wpdb->prepare is read-only string formatting, executed via $wpdb->get_col below |
| 590 |
$query = $wpdb->prepare( |
| 591 |
"SELECT option_name FROM {$wpdb->options} |
| 592 |
WHERE option_name LIKE %s |
| 593 |
AND option_value < %d", |
| 594 |
$wpdb->esc_like('_transient_timeout_abj404_rate_limit_') . '%', |
| 595 |
$currentTime |
| 596 |
); |
| 597 |
|
| 598 |
// DAO-bypass-approved: Outside-plugin-tables wp_options cleanup probe (parallels DataAccessTrait_ViewQueries:478 transient clear) |
| 599 |
$expiredTimeouts = $wpdb->get_col($query); |
| 600 |
|
| 601 |
$lastError = (string)($wpdb->last_error ?? ''); |
| 602 |
if ($lastError !== '') { |
| 603 |
if (!$this->dbCore->classifyAndHandleInfrastructureError($lastError)) { |
| 604 |
$this->logger->errorMessage("Failed to query for expired rate limit transients: " . $lastError); |
| 605 |
} |
| 606 |
return ['deleted' => 0, 'errors' => 1, 'error' => $lastError]; |
| 607 |
} |
| 608 |
|
| 609 |
if (!empty($expiredTimeouts)) { |
| 610 |
$this->logger->debugMessage("Found " . count($expiredTimeouts) . " expired rate limit transients to delete."); |
| 611 |
|
| 612 |
foreach ($expiredTimeouts as $timeoutKey) { |
| 613 |
// Get the corresponding value key (remove '_timeout' from the name) |
| 614 |
$valueKey = str_replace('_transient_timeout_', '_transient_', $timeoutKey); |
| 615 |
|
| 616 |
// Delete both the timeout and value rows |
| 617 |
$timeoutDeleted = delete_option($timeoutKey); |
| 618 |
$valueDeleted = delete_option($valueKey); |
| 619 |
|
| 620 |
if ($timeoutDeleted || $valueDeleted) { |
| 621 |
$stats['deleted']++; |
| 622 |
} else { |
| 623 |
$stats['errors']++; |
| 624 |
} |
| 625 |
} |
| 626 |
|
| 627 |
$this->logger->debugMessage("Deleted {$stats['deleted']} expired rate limit transients, {$stats['errors']} errors."); |
| 628 |
} else { |
| 629 |
$this->logger->debugMessage("No expired rate limit transients found."); |
| 630 |
} |
| 631 |
|
| 632 |
return $stats; |
| 633 |
} |
| 634 |
|
| 635 |
/** |
| 636 |
* Run all database maintenance tasks. |
| 637 |
* |
| 638 |
* This is the main orchestrator method called by the daily maintenance cron job. |
| 639 |
* It coordinates all database-related maintenance tasks in the proper order. |
| 640 |
* |
| 641 |
* Called by: abj404_dailyMaintenanceCronJobListener() in 404-solution.php |
| 642 |
* |
| 643 |
* @return void |
| 644 |
*/ |
| 645 |
public function runDatabaseMaintenanceTasks() { |
| 646 |
// Insurance: Verify tables exist (per-site or network-wide based on activation mode) |
| 647 |
// This catches failed activations, database corruption, and edge cases. |
| 648 |
// Routed through runSelfHealPrologue() so the SelfHealingPrologueReachabilityTest |
| 649 |
// can confirm the daily cron reaches the canonical prologue token. |
| 650 |
$this->runSelfHealPrologue(); |
| 651 |
|
| 652 |
// Ngram cache maintenance: sync missing entries and cleanup orphaned ones |
| 653 |
$this->syncMissingNGrams(); |
| 654 |
$this->cleanupOrphanedNGrams(); |
| 655 |
|
| 656 |
// Clean up expired rate limit transients to prevent wp_options bloat |
| 657 |
$this->cleanupExpiredRateLimitTransients(); |
| 658 |
|
| 659 |
// Flag redirects whose destination URL is generating 404s (drives redirect suspension) |
| 660 |
abj_service('redirects_repository')->flagDeadDestinationRedirects(); |
| 661 |
|
| 662 |
// Expire auto-created redirects that exceed the configured age threshold |
| 663 |
abj_service('redirects_repository')->expireOldAutoRedirects(); |
| 664 |
|
| 665 |
// Backfill canonical_url on legacy redirect rows so the captured-page |
| 666 |
// JOIN to logs_hits.requested_url stays index-friendly. Chunked + rate- |
| 667 |
// limited so the daily cron continues progress without blocking large |
| 668 |
// sites; converges on its own across successive runs. |
| 669 |
$this->backfillRedirectsCanonicalUrl(); |
| 670 |
|
| 671 |
// Same idea for logsv2: legacy rows (pre-4.1.x) lack canonical_url, so |
| 672 |
// the hits-rebuild JOIN falls back to CONCAT/TRIM and can't use |
| 673 |
// idx_canonical_url. Chunked + rate-limited so even a multi-hundred-K |
| 674 |
// logsv2 backlog converges across successive cron ticks. Tighter |
| 675 |
// 15-second budget (vs redirects' 25) because this same function is |
| 676 |
// also reachable from the Captured-404s tab shutdown hook — |
| 677 |
// see scheduleLogsv2CanonicalUrlBackfill(). |
| 678 |
$this->backfillLogsv2CanonicalUrl(); |
| 679 |
|
| 680 |
// Nightly internal-link scan: find broken internal links in published content. |
| 681 |
if (class_exists('ABJ_404_Solution_InternalLinkScanner')) { |
| 682 |
$scanner = new ABJ_404_Solution_InternalLinkScanner(); |
| 683 |
$scanner->runNightlyScan(); |
| 684 |
} |
| 685 |
|
| 686 |
$this->refreshViewDoneSnapshotInline(); |
| 687 |
} |
| 688 |
|
| 689 |
/** |
| 690 |
* Invalidate the staged view_done snapshot and drive the staged build to |
| 691 |
* completion inline so admin tables on quiet sites still see at most a |
| 692 |
* 24-hour-old snapshot. Bounded by an iteration cap so a build that yields |
| 693 |
* indefinitely (lease contention, transient lock failures) cannot stall |
| 694 |
* the daily maintenance window. |
| 695 |
* |
| 696 |
* Runs after the other daily tasks so canonical_url backfills, dead-dest |
| 697 |
* flagging, and auto-redirect expiry are already reflected in the freshly |
| 698 |
* rebuilt view_done. |
| 699 |
* |
| 700 |
* @return void |
| 701 |
*/ |
| 702 |
private function refreshViewDoneSnapshotInline(): void { |
| 703 |
$viewRead = abj_service('view_read_service'); |
| 704 |
$viewBuild = abj_service('view_build_orchestrator'); |
| 705 |
$rebuildHealth = null; |
| 706 |
if (class_exists('ABJ_404_Solution_ServiceContainer') |
| 707 |
&& ABJ_404_Solution_ServiceContainer::safeHas('rebuild_health')) { |
| 708 |
$service = ABJ_404_Solution_ServiceContainer::safeGet('rebuild_health'); |
| 709 |
$rebuildHealth = $service instanceof ABJ_404_Solution_RebuildHealthState ? $service : null; |
| 710 |
} |
| 711 |
if ($rebuildHealth instanceof ABJ_404_Solution_RebuildHealthState |
| 712 |
&& !$rebuildHealth->beginDailyMaintenanceRebuildAttempt()) { |
| 713 |
return; |
| 714 |
} |
| 715 |
if (!is_object($viewRead) |
| 716 |
|| !method_exists($viewRead, 'invalidateViewSnapshotCache') |
| 717 |
|| !is_object($viewBuild) |
| 718 |
|| !method_exists($viewBuild, 'advanceViewBuildOnce')) { |
| 719 |
return; |
| 720 |
} |
| 721 |
$viewRead->invalidateViewSnapshotCache(); |
| 722 |
// 11 staged sub-stages with up to a few yields each on resumable |
| 723 |
// stages (S2/S4/S5); 30 ticks comfortably covers a full rebuild. |
| 724 |
for ($i = 0; $i < 30; $i++) { |
| 725 |
$progress = $viewBuild->advanceViewBuildOnce(); |
| 726 |
if (!is_array($progress)) { break; } |
| 727 |
if (($progress['status'] ?? '') === 'ready') { break; } |
| 728 |
if (!empty($progress['locked'])) { break; } |
| 729 |
} |
| 730 |
} |
| 731 |
|
| 732 |
// Constants CANONICAL_URL_BACKFILL_CHUNK_SIZE and |
| 733 |
// CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC are defined on the using class |
| 734 |
// (ABJ_404_Solution_DatabaseUpgradesEtc) because trait constants require |
| 735 |
// PHP 8.2+ and the plugin supports PHP 7.4. self::* below resolves to |
| 736 |
// the using class at compile time. |
| 737 |
|
| 738 |
/** |
| 739 |
* Populate {wp_abj404_redirects}.canonical_url for any rows still NULL, |
| 740 |
* one chunk at a time. Each chunk runs: |
| 741 |
* |
| 742 |
* UPDATE redirects SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM url)) |
| 743 |
* WHERE canonical_url IS NULL LIMIT N |
| 744 |
* |
| 745 |
* Idempotent — once every row has canonical_url set, the WHERE matches |
| 746 |
* zero rows and the function returns immediately. The chunk loop is |
| 747 |
* bounded by both row count (CANONICAL_URL_BACKFILL_CHUNK_SIZE) and wall |
| 748 |
* clock (CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC) so a 350K-row site |
| 749 |
* converges over successive daily cron ticks without ever blocking a |
| 750 |
* request long enough to hit PHP max_execution_time. |
| 751 |
* |
| 752 |
* Skips silently when: |
| 753 |
* - the redirects table is missing (degraded site state) |
| 754 |
* - the canonical_url column is missing (column add hasn't happened |
| 755 |
* yet, e.g. immediately after upgrade before verifyColumns ran) |
| 756 |
* - the previous run errored — repair flow surfaces the error |
| 757 |
* |
| 758 |
* @return int Number of rows updated in this invocation. |
| 759 |
*/ |
| 760 |
public function backfillRedirectsCanonicalUrl(): int { |
| 761 |
global $wpdb; |
| 762 |
if (!isset($wpdb)) { |
| 763 |
return 0; |
| 764 |
} |
| 765 |
$redirectsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_redirects}'); |
| 766 |
|
| 767 |
// SHOW TABLES existence probe — same shape as verifyTableMaterialized() |
| 768 |
// in DatabaseUpgradesEtc.php:854. The DAO's tableExists() helper is |
| 769 |
// private so we can't reach it from here, and routing through |
| 770 |
// queryAndGetResults() would log a benign "table doesn't exist" error |
| 771 |
// on freshly-installed sites before runInitialCreateTables() has run. |
| 772 |
// DAO-bypass-approved: schema existence probe — see comment above. |
| 773 |
$found = $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($redirectsTable) . "'"); |
| 774 |
if ($found !== $redirectsTable) { |
| 775 |
return 0; |
| 776 |
} |
| 777 |
if (!$this->columnExists($redirectsTable, 'canonical_url')) { |
| 778 |
return 0; |
| 779 |
} |
| 780 |
|
| 781 |
$chunkSize = (int)self::CANONICAL_URL_BACKFILL_CHUNK_SIZE; |
| 782 |
$timeBudget = (float)self::CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC; |
| 783 |
$start = microtime(true); |
| 784 |
$totalUpdated = 0; |
| 785 |
|
| 786 |
while ((microtime(true) - $start) < $timeBudget) { |
| 787 |
$query = "UPDATE " . $redirectsTable . |
| 788 |
" SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM url))" . |
| 789 |
" WHERE canonical_url IS NULL" . |
| 790 |
" LIMIT " . $chunkSize; |
| 791 |
|
| 792 |
$result = $this->dbCore->queryAndGetResults($query); |
| 793 |
$lastError = isset($result['last_error']) && is_string($result['last_error']) ? $result['last_error'] : ''; |
| 794 |
if ($lastError !== '') { |
| 795 |
$this->logger->warn("backfillRedirectsCanonicalUrl: stopping after error: " . $lastError); |
| 796 |
return $totalUpdated; |
| 797 |
} |
| 798 |
|
| 799 |
$rowsAffected = isset($result['rows_affected']) && is_numeric($result['rows_affected']) |
| 800 |
? (int)$result['rows_affected'] : 0; |
| 801 |
$totalUpdated += $rowsAffected; |
| 802 |
if ($rowsAffected < $chunkSize) { |
| 803 |
break; |
| 804 |
} |
| 805 |
} |
| 806 |
|
| 807 |
if ($totalUpdated > 0) { |
| 808 |
$this->logger->infoMessage(sprintf( |
| 809 |
"backfillRedirectsCanonicalUrl: populated canonical_url on %d redirect rows in %.2fs.", |
| 810 |
$totalUpdated, |
| 811 |
microtime(true) - $start |
| 812 |
)); |
| 813 |
} |
| 814 |
return $totalUpdated; |
| 815 |
} |
| 816 |
|
| 817 |
/** |
| 818 |
* Populate {wp_abj404_logsv2}.canonical_url for any rows still NULL, |
| 819 |
* one chunk at a time. Each chunk runs: |
| 820 |
* |
| 821 |
* UPDATE logsv2 SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM requested_url)) |
| 822 |
* WHERE canonical_url IS NULL LIMIT N |
| 823 |
* |
| 824 |
* Mirrors backfillRedirectsCanonicalUrl() with one budget difference — |
| 825 |
* 15-second wall budget (vs 25 for redirects) because this function is |
| 826 |
* also reachable from the Captured-404s admin tab via |
| 827 |
* scheduleLogsv2CanonicalUrlBackfill(), and the shutdown hook holds a |
| 828 |
* PHP-FPM worker for the full budget. 15s leaves enough headroom for |
| 829 |
* concurrent traffic on shared hosts. On a Bruno-class 250K-row backlog |
| 830 |
* this converges in ~3-10 days on daily cron alone, faster if the admin |
| 831 |
* regularly visits the tab. |
| 832 |
* |
| 833 |
* Once the backlog is fully cleared (no rows where canonical_url IS NULL), |
| 834 |
* sets the abj404_logsv2_canonical_url_backfill_complete option so reads |
| 835 |
* can drop the COALESCE fallback in getRedirectsForViewTempTable.sql and |
| 836 |
* use the no-COALESCE form (logsv2.canonical_url = redirects.canonical_url). |
| 837 |
* |
| 838 |
* Skips silently when: |
| 839 |
* - the logsv2 table is missing (degraded site state) |
| 840 |
* - the canonical_url column is missing (column add hasn't happened |
| 841 |
* yet, e.g. immediately after upgrade before verifyColumns ran) |
| 842 |
* - the previous run errored — repair flow surfaces the error |
| 843 |
* |
| 844 |
* @return int Number of rows updated in this invocation. |
| 845 |
*/ |
| 846 |
public function backfillLogsv2CanonicalUrl(): int { |
| 847 |
global $wpdb; |
| 848 |
if (!isset($wpdb)) { |
| 849 |
return 0; |
| 850 |
} |
| 851 |
$logsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 852 |
|
| 853 |
// SHOW TABLES existence probe — same shape as in |
| 854 |
// backfillRedirectsCanonicalUrl(). Routing through queryAndGetResults |
| 855 |
// would log a benign "table doesn't exist" error on freshly-installed |
| 856 |
// sites before runInitialCreateTables() has run. |
| 857 |
// DAO-bypass-approved: schema existence probe — see comment above. |
| 858 |
$found = $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($logsTable) . "'"); |
| 859 |
if ($found !== $logsTable) { |
| 860 |
return 0; |
| 861 |
} |
| 862 |
if (!$this->columnExists($logsTable, 'canonical_url')) { |
| 863 |
return 0; |
| 864 |
} |
| 865 |
|
| 866 |
$chunkSize = (int)self::CANONICAL_URL_BACKFILL_CHUNK_SIZE; |
| 867 |
$timeBudget = (float)self::LOGSV2_CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC; |
| 868 |
$start = microtime(true); |
| 869 |
$totalUpdated = 0; |
| 870 |
|
| 871 |
while ((microtime(true) - $start) < $timeBudget) { |
| 872 |
$query = "UPDATE " . $logsTable . |
| 873 |
" SET canonical_url = CONCAT('/', TRIM(BOTH '/' FROM requested_url))" . |
| 874 |
" WHERE canonical_url IS NULL" . |
| 875 |
" LIMIT " . $chunkSize; |
| 876 |
|
| 877 |
$result = $this->dbCore->queryAndGetResults($query); |
| 878 |
$lastError = isset($result['last_error']) && is_string($result['last_error']) ? $result['last_error'] : ''; |
| 879 |
if ($lastError !== '') { |
| 880 |
$this->logger->warn("backfillLogsv2CanonicalUrl: stopping after error: " . $lastError); |
| 881 |
return $totalUpdated; |
| 882 |
} |
| 883 |
|
| 884 |
$rowsAffected = isset($result['rows_affected']) && is_numeric($result['rows_affected']) |
| 885 |
? (int)$result['rows_affected'] : 0; |
| 886 |
$totalUpdated += $rowsAffected; |
| 887 |
if ($rowsAffected < $chunkSize) { |
| 888 |
break; |
| 889 |
} |
| 890 |
} |
| 891 |
|
| 892 |
if ($totalUpdated > 0) { |
| 893 |
$this->logger->infoMessage(sprintf( |
| 894 |
"backfillLogsv2CanonicalUrl: populated canonical_url on %d logsv2 rows in %.2fs.", |
| 895 |
$totalUpdated, |
| 896 |
microtime(true) - $start |
| 897 |
)); |
| 898 |
} |
| 899 |
|
| 900 |
// If the backlog is now drained, flip the completion flag so reads |
| 901 |
// can drop the COALESCE fallback. Cheap LIMIT 1 probe — at most reads |
| 902 |
// one row's worth of data via the canonical_url IS NULL filter (uses |
| 903 |
// idx_canonical_url because IS NULL is sargable on a B-tree on a |
| 904 |
// nullable column). |
| 905 |
if (!get_option(self::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION)) { |
| 906 |
$remainingProbe = $this->dbCore->queryAndGetResults( |
| 907 |
"SELECT 1 FROM " . $logsTable . " WHERE canonical_url IS NULL LIMIT 1" |
| 908 |
); |
| 909 |
$remainingRows = is_array($remainingProbe['rows'] ?? null) ? $remainingProbe['rows'] : []; |
| 910 |
$remainingError = isset($remainingProbe['last_error']) && is_string($remainingProbe['last_error']) ? $remainingProbe['last_error'] : ''; |
| 911 |
if ($remainingError === '' && empty($remainingRows)) { |
| 912 |
update_option(self::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION, '1', false); |
| 913 |
$this->logger->infoMessage( |
| 914 |
"backfillLogsv2CanonicalUrl: backlog cleared — flipped " . |
| 915 |
self::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION . |
| 916 |
"; reads can now drop the COALESCE fallback." |
| 917 |
); |
| 918 |
} |
| 919 |
} |
| 920 |
|
| 921 |
return $totalUpdated; |
| 922 |
} |
| 923 |
|
| 924 |
/** |
| 925 |
* Register a deferred backfill of logsv2.canonical_url, deduped per |
| 926 |
* request. Called from the Captured-404s admin tab render so the |
| 927 |
* legacy NULL backlog clears on each visit (15-second budget per |
| 928 |
* invocation, ~25K-75K rows per invocation on shared hosting). |
| 929 |
* |
| 930 |
* Why shutdown outside AJAX and WP-Cron during AJAX: |
| 931 |
* - shutdown always fires; wp-cron silently doesn't on |
| 932 |
* DISABLE_WP_CRON=true sites without a server-side cron worker |
| 933 |
* (a real subset of WP installs). |
| 934 |
* - On normal page requests, shutdown keeps convergence independent of |
| 935 |
* wp-cron and the response has already been rendered. |
| 936 |
* - On admin-ajax.php, some hosts/proxies still hold the HTTP response |
| 937 |
* open until shutdown work finishes. Use WP-Cron there so table AJAX |
| 938 |
* cannot time out behind the 15-second backfill budget. |
| 939 |
* |
| 940 |
* Pre-flight gates (in order, cheapest first): |
| 941 |
* 1. Static request-scoped flag — skip if already scheduled. |
| 942 |
* 2. Backfill-complete option — skip permanently once flipped. |
| 943 |
* 3. Column existence — skip on pre-upgrade installs. |
| 944 |
* 4. Cheap "any NULL rows?" probe (LIMIT 1, indexed) — skip if |
| 945 |
* backlog is already drained but the flag wasn't flipped (e.g. |
| 946 |
* first time we observe a clean backlog). |
| 947 |
* |
| 948 |
* @return void |
| 949 |
*/ |
| 950 |
public function scheduleLogsv2CanonicalUrlBackfill(): void { |
| 951 |
if (self::$logsv2CanonicalBackfillScheduled) { |
| 952 |
return; |
| 953 |
} |
| 954 |
if (function_exists('get_option') && get_option(self::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION)) { |
| 955 |
return; |
| 956 |
} |
| 957 |
|
| 958 |
global $wpdb; |
| 959 |
if (!isset($wpdb)) { |
| 960 |
return; |
| 961 |
} |
| 962 |
|
| 963 |
$logsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 964 |
if (!$this->columnExists($logsTable, 'canonical_url')) { |
| 965 |
return; |
| 966 |
} |
| 967 |
|
| 968 |
$probe = $this->dbCore->queryAndGetResults( |
| 969 |
"SELECT 1 FROM " . $logsTable . " WHERE canonical_url IS NULL LIMIT 1", |
| 970 |
array('log_too_slow' => false) |
| 971 |
); |
| 972 |
$rows = is_array($probe['rows'] ?? null) ? $probe['rows'] : []; |
| 973 |
$probeError = isset($probe['last_error']) && is_string($probe['last_error']) ? $probe['last_error'] : ''; |
| 974 |
if ($probeError === '' && empty($rows)) { |
| 975 |
// No NULL rows but flag wasn't set yet — flip it now to skip |
| 976 |
// future probes on this and later requests. |
| 977 |
if (function_exists('update_option')) { |
| 978 |
update_option(self::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION, '1', false); |
| 979 |
} |
| 980 |
return; |
| 981 |
} |
| 982 |
|
| 983 |
self::$logsv2CanonicalBackfillScheduled = true; |
| 984 |
if ($this->shouldScheduleLogsv2CanonicalBackfillViaCron()) { |
| 985 |
if (function_exists('wp_schedule_single_event')) { |
| 986 |
wp_schedule_single_event(time() + 5, 'abj404_logsv2_canonical_backfill'); |
| 987 |
} |
| 988 |
return; |
| 989 |
} |
| 990 |
|
| 991 |
if (function_exists('add_action')) { |
| 992 |
add_action('shutdown', function (): void { $this->backfillLogsv2CanonicalUrl(); }); |
| 993 |
} |
| 994 |
} |
| 995 |
|
| 996 |
/** @return bool */ |
| 997 |
private function shouldScheduleLogsv2CanonicalBackfillViaCron(): bool { |
| 998 |
if (function_exists('wp_doing_ajax') && wp_doing_ajax()) { |
| 999 |
return true; |
| 1000 |
} |
| 1001 |
$scriptName = isset($_SERVER['SCRIPT_NAME']) && is_string($_SERVER['SCRIPT_NAME']) |
| 1002 |
? $_SERVER['SCRIPT_NAME'] : ''; |
| 1003 |
if ($scriptName !== '' && basename($scriptName) === 'admin-ajax.php') { |
| 1004 |
return true; |
| 1005 |
} |
| 1006 |
$pagenow = isset($GLOBALS['pagenow']) && is_string($GLOBALS['pagenow']) |
| 1007 |
? $GLOBALS['pagenow'] : ''; |
| 1008 |
return $pagenow === 'admin-ajax.php'; |
| 1009 |
} |
| 1010 |
|
| 1011 |
/** |
| 1012 |
* Test-only: reset the per-request shutdown-schedule dedup flag so the |
| 1013 |
* next call to scheduleLogsv2CanonicalUrlBackfill() can register again. |
| 1014 |
* Production callers never invoke this — the flag clears naturally when |
| 1015 |
* the PHP process ends. |
| 1016 |
* |
| 1017 |
* @return void |
| 1018 |
*/ |
| 1019 |
public static function resetLogsv2CanonicalBackfillScheduledFlagForTests(): void { |
| 1020 |
self::$logsv2CanonicalBackfillScheduled = false; |
| 1021 |
} |
| 1022 |
|
| 1023 |
/** |
| 1024 |
* Test-only: read the current state of the per-request dedup flag so |
| 1025 |
* tests can assert that scheduleLogsv2CanonicalUrlBackfill() did or did |
| 1026 |
* not register a shutdown hook. |
| 1027 |
* |
| 1028 |
* @return bool |
| 1029 |
*/ |
| 1030 |
public static function getLogsv2CanonicalBackfillScheduledFlagForTests(): bool { |
| 1031 |
return self::$logsv2CanonicalBackfillScheduled; |
| 1032 |
} |
| 1033 |
|
| 1034 |
/** |
| 1035 |
* Cheap "does this column exist on this table" probe via SHOW COLUMNS. |
| 1036 |
* Case-insensitive on the column name to match MySQL/MariaDB driver |
| 1037 |
* variations in returned column-name casing. |
| 1038 |
* |
| 1039 |
* @param string $tableName Fully-qualified table name. |
| 1040 |
* @param string $columnName Column to look for. |
| 1041 |
* @return bool |
| 1042 |
*/ |
| 1043 |
private function columnExists(string $tableName, string $columnName): bool { |
| 1044 |
$result = $this->dbCore->queryAndGetResults("SHOW COLUMNS FROM " . $tableName, |
| 1045 |
array('log_errors' => false)); |
| 1046 |
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 1047 |
$needle = strtolower($columnName); |
| 1048 |
foreach ($rows as $row) { |
| 1049 |
if (!is_array($row)) { continue; } |
| 1050 |
foreach ($row as $key => $value) { |
| 1051 |
if (strtolower((string)$key) !== 'field') { continue; } |
| 1052 |
if (strtolower((string)$value) === $needle) { |
| 1053 |
return true; |
| 1054 |
} |
| 1055 |
} |
| 1056 |
} |
| 1057 |
return false; |
| 1058 |
} |
| 1059 |
} |
| 1060 |
|
