| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
trait ABJ_404_Solution_DataAccess_MaintenanceTrait { |
| 8 |
|
| 9 |
/** |
| 10 |
* Auto-recover from a collation mismatch detected at query time. |
| 11 |
* |
| 12 |
* Strategy (matches the project's "try, recover, retry, then notify" pattern, |
| 13 |
* but the "notify" step is intentionally omitted per owner directive — collation |
| 14 |
* issues must NEVER surface to the user): |
| 15 |
* |
| 16 |
* 1. Detect "Illegal mix of collations" / "Unknown collation" in $result['last_error']. |
| 17 |
* 2. Honor a 1-hour cooldown transient (`abj404_collation_recovery_cooldown`) so a |
| 18 |
* storm of collation errors doesn't run correctCollations() repeatedly. |
| 19 |
* 3. Call ABJ_404_Solution_DatabaseUpgradesEtc::correctCollations() which converges |
| 20 |
* all plugin tables to a single utf8mb4 collation (column-level + table-level). |
| 21 |
* 4. Set the cooldown transient. |
| 22 |
* 5. Retry the original query once. If the retry succeeds, harvest the result; if |
| 23 |
* it still fails, return — the caller's $reportError logic downgrades collation |
| 24 |
* errors to WARN log entries (no email, no admin notice). |
| 25 |
* |
| 26 |
* Self-recursion is prevented by setting a static guard while correctCollations() runs: |
| 27 |
* the ALTER TABLE statements that correctCollations() emits go back through |
| 28 |
* queryAndGetResults(), and any collation error encountered there must NOT trigger |
| 29 |
* another recovery (it would deadlock on the cooldown). |
| 30 |
* |
| 31 |
* @param string $query |
| 32 |
* @param array<string, mixed> $result passed by reference |
| 33 |
* @param bool $producesRows Whether the query returns result rows. |
| 34 |
* @param 'OBJECT'|'OBJECT_K'|'ARRAY_A'|'ARRAY_N' $resultType wpdb output type for get_results(). |
| 35 |
* @return void |
| 36 |
*/ |
| 37 |
private function recoverFromCollationMismatchAndRetry(string $query, array &$result, bool $producesRows, string $resultType): void { |
| 38 |
// Re-entry guard: if correctCollations()'s own ALTER TABLE hits a collation |
| 39 |
// error, do NOT recurse — return and let the original error propagate. |
| 40 |
if (self::$collationRecoveryInProgress) { |
| 41 |
return; |
| 42 |
} |
| 43 |
|
| 44 |
$cooldownKey = 'abj404_collation_recovery_cooldown'; |
| 45 |
$cooldownUntil = $this->getRuntimeFlag($cooldownKey); |
| 46 |
$onCooldown = is_scalar($cooldownUntil) && (int)$cooldownUntil > $this->clock()->now(); |
| 47 |
|
| 48 |
if (!$onCooldown) { |
| 49 |
self::$collationRecoveryInProgress = true; |
| 50 |
try { |
| 51 |
$this->logger->infoMessage("Collation mismatch detected — running correctCollations() to converge plugin tables."); |
| 52 |
if (class_exists('ABJ_404_Solution_DatabaseUpgradesEtc')) { |
| 53 |
$upgrades = abj_service('database_upgrades'); |
| 54 |
if (method_exists($upgrades, 'correctCollations')) { |
| 55 |
$upgrades->correctCollations(); |
| 56 |
} |
| 57 |
} |
| 58 |
} catch (Throwable $e) { |
| 59 |
$this->logger->warn("correctCollations() threw during collation auto-recovery: " . $e->getMessage()); |
| 60 |
} finally { |
| 61 |
self::$collationRecoveryInProgress = false; |
| 62 |
// Set the 1-hour cooldown regardless of success/failure so we don't |
| 63 |
// hammer correctCollations() on a hot query path. |
| 64 |
$this->setRuntimeFlag($cooldownKey, $this->clock()->now() + 3600, 3600); |
| 65 |
} |
| 66 |
} |
| 67 |
|
| 68 |
// Retry the original query once, whether or not we ran correctCollations(). |
| 69 |
// After a successful run the underlying mismatch should be gone; if cooldown |
| 70 |
// was active the retry is still cheap and may succeed for transient reasons. |
| 71 |
global $wpdb; |
| 72 |
/** @var wpdb $wpdb */ |
| 73 |
$wpdb->flush(); |
| 74 |
if ($producesRows) { |
| 75 |
$result['rows'] = $wpdb->get_results($query, $resultType); |
| 76 |
} else { |
| 77 |
$wpdb->query($query); |
| 78 |
$result['rows'] = array(); |
| 79 |
} |
| 80 |
$this->harvestWpdbResult($result); |
| 81 |
|
| 82 |
if ($result['last_error'] === '') { |
| 83 |
$this->logger->debugMessage("Collation auto-recovery succeeded; query retry passed."); |
| 84 |
} |
| 85 |
} |
| 86 |
|
| 87 |
/** |
| 88 |
* Validate and sanitize a table name extracted from error messages or SQL. |
| 89 |
* Only allows alphanumeric characters and underscores, and requires 'abj404' in the name. |
| 90 |
* |
| 91 |
* @param string $name Raw table name |
| 92 |
* @return string|null Sanitized name, or null if invalid |
| 93 |
*/ |
| 94 |
private function sanitizeTableName(string $name): ?string { |
| 95 |
// Strip any backticks that may already be present |
| 96 |
$name = trim($name, '`'); |
| 97 |
// Only allow safe characters |
| 98 |
if (!preg_match('/^[a-zA-Z0-9_]+$/', $name)) { |
| 99 |
$this->logger->warn("sanitizeTableName: rejected invalid table name: " . substr($name, 0, 100)); |
| 100 |
return null; |
| 101 |
} |
| 102 |
// Must be a plugin table |
| 103 |
if (strpos($name, 'abj404') === false) { |
| 104 |
$this->logger->warn("sanitizeTableName: rejected non-plugin table name: " . $name); |
| 105 |
return null; |
| 106 |
} |
| 107 |
return $name; |
| 108 |
} |
| 109 |
|
| 110 |
/** @param string $errorMessage @return void */ |
| 111 |
function repairTable(string $errorMessage): void { |
| 112 |
|
| 113 |
// Match "Table '...' is marked as crashed" (errno 1194/1195). |
| 114 |
$re1 = "Table '(.*\/)?(.+)' is marked as crashed and "; |
| 115 |
// Match "Incorrect key file for table '...'" (errno 1034). |
| 116 |
// The table name may include a path prefix (./db/name) and a .MYI suffix. |
| 117 |
$re2 = "Incorrect key file for table '(?:.*\/)?([^'.]+?)(?:\\.MYI)?'"; |
| 118 |
|
| 119 |
$matches = array(); |
| 120 |
$this->f->regexMatch($re1, $errorMessage, $matches); |
| 121 |
|
| 122 |
if (empty($matches) || count($matches) <= 2 || $this->f->strlen($matches[2]) === 0) { |
| 123 |
// Try the errno 1034 pattern. Use a single capture group (no path prefix group) |
| 124 |
// so $matches[1] is the bare table name. |
| 125 |
$this->f->regexMatch($re2, $errorMessage, $matches); |
| 126 |
// Shift result to match[2] position expected by the code below. |
| 127 |
if (!empty($matches) && isset($matches[1]) && $this->f->strlen($matches[1]) > 0) { |
| 128 |
$matches[2] = $matches[1]; |
| 129 |
} |
| 130 |
} |
| 131 |
|
| 132 |
if (!empty($matches) && count($matches) > 2 && $this->f->strlen($matches[2]) > 0) { |
| 133 |
$rawTableName = $matches[2]; |
| 134 |
$tableToRepair = $this->sanitizeTableName($rawTableName); |
| 135 |
if ($tableToRepair !== null) { |
| 136 |
$query = "REPAIR TABLE `{$tableToRepair}`"; |
| 137 |
$result = $this->queryAndGetResults($query, array('log_errors' => false)); |
| 138 |
$this->logger->infoMessage("Attempted to repair table " . $tableToRepair . ". Result: " . |
| 139 |
json_encode($result)); |
| 140 |
} else { |
| 141 |
// Non-plugin table or invalid name: the plugin cannot repair it, |
| 142 |
// but we can notify the admin once per day so they can contact their host. |
| 143 |
$this->logger->warn("The table " . $rawTableName . " needs to be " . |
| 144 |
"repaired with something like: repair table " . $rawTableName); |
| 145 |
|
| 146 |
$cooldownKey = 'abj404_corrupted_temp_table_notice_until'; |
| 147 |
$alreadyNotified = function_exists('get_transient') ? get_transient($cooldownKey) : false; |
| 148 |
if (!$alreadyNotified) { |
| 149 |
$noticeMessage = $this->localizeOrDefault( |
| 150 |
'A database temporary table is corrupted — this is usually caused by a full or failing disk. Please contact your host. (MySQL error 1034)'); |
| 151 |
$this->setPluginDbNotice('corrupted_temp_table', $noticeMessage, $errorMessage); |
| 152 |
if (function_exists('set_transient')) { |
| 153 |
// @cache-write-audit: opt-out — admin-notice dedup cooldown |
| 154 |
// (one notice per 24h per failure type), not a query result. |
| 155 |
set_transient($cooldownKey, 1, 86400); |
| 156 |
} |
| 157 |
} |
| 158 |
} |
| 159 |
} |
| 160 |
} |
| 161 |
|
| 162 |
/** @param string $errorMessage @param string $sqlThatWasRun @return void */ |
| 163 |
function repairDuplicateIDs(string $errorMessage, string $sqlThatWasRun): void { |
| 164 |
|
| 165 |
$reForID = 'resulting in duplicate entry \'(.+)\' for key'; |
| 166 |
$reForTableName = "ALTER TABLE (.+) ADD "; |
| 167 |
$matchesForID = null; |
| 168 |
$matchesForTableName = null; |
| 169 |
|
| 170 |
$this->f->regexMatch($reForID, $errorMessage, $matchesForID); |
| 171 |
$this->f->regexMatch($reForTableName, $sqlThatWasRun, $matchesForTableName); |
| 172 |
if (is_array($matchesForID) && isset($matchesForID[1]) && $this->f->strlen($matchesForID[1]) > 0 && |
| 173 |
is_array($matchesForTableName) && isset($matchesForTableName[1]) && $this->f->strlen($matchesForTableName[1]) > 0) { |
| 174 |
|
| 175 |
$idWithDuplicate = $matchesForID[1]; |
| 176 |
$tableName = $this->sanitizeTableName($matchesForTableName[1]); |
| 177 |
if ($tableName === null) { |
| 178 |
$this->logger->warn("repairDuplicateIDs: rejected invalid table name from SQL: " . substr($matchesForTableName[1], 0, 100)); |
| 179 |
return; |
| 180 |
} |
| 181 |
|
| 182 |
// Validate that ID is numeric to prevent SQL injection |
| 183 |
if (!is_numeric($idWithDuplicate)) { |
| 184 |
$this->logger->errorMessage("Invalid ID extracted from error message: " . $idWithDuplicate); |
| 185 |
return; |
| 186 |
} |
| 187 |
|
| 188 |
if ($idWithDuplicate == 1) { |
| 189 |
$idWithDuplicate = 0; |
| 190 |
} |
| 191 |
|
| 192 |
// Use prepared statement to prevent SQL injection |
| 193 |
$result = $this->queryAndGetResults("DELETE FROM `{$tableName}` where id = %d", |
| 194 |
array('log_errors' => false, 'query_params' => array(absint($idWithDuplicate)))); |
| 195 |
$this->logger->infoMessage("Attempted to fix a duplicate entry issue. Table: " . |
| 196 |
$tableName . ", Result: " . json_encode($result)); |
| 197 |
} |
| 198 |
} |
| 199 |
|
| 200 |
/** |
| 201 |
* @param array<int, string> $statementArray |
| 202 |
* @return void |
| 203 |
*/ |
| 204 |
function executeAsTransaction(array $statementArray): void { |
| 205 |
global $wpdb; |
| 206 |
$maxAttempts = 3; |
| 207 |
$lastException = null; |
| 208 |
$lastError = ''; |
| 209 |
|
| 210 |
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) { |
| 211 |
$allIsWell = true; |
| 212 |
$lastError = ''; |
| 213 |
$lastException = null; |
| 214 |
try { |
| 215 |
$wpdb->query('START TRANSACTION'); |
| 216 |
foreach ($statementArray as $statement) { |
| 217 |
$wpdb->query($statement); |
| 218 |
if ($wpdb->last_error != null && trim((string)$wpdb->last_error) !== '') { |
| 219 |
$allIsWell = false; |
| 220 |
$lastError = (string)$wpdb->last_error; |
| 221 |
if (!$this->classifyAndHandleInfrastructureError($lastError)) { |
| 222 |
$this->logger->errorMessage("Error executing SQL transaction: " . $lastError); |
| 223 |
$this->logger->errorMessage("SQL causing the transaction error: " . $statement); |
| 224 |
} |
| 225 |
break; |
| 226 |
} |
| 227 |
} |
| 228 |
} catch (Throwable $ex) { // Fixed: Catch Throwable (Exception + Error) for PHP 7+ compatibility |
| 229 |
$allIsWell = false; |
| 230 |
$lastException = $ex; |
| 231 |
$lastError = $ex->getMessage(); |
| 232 |
} |
| 233 |
|
| 234 |
if ($allIsWell && $lastException == null) { |
| 235 |
$wpdb->query('commit'); |
| 236 |
return; |
| 237 |
} |
| 238 |
|
| 239 |
$wpdb->query('rollback'); |
| 240 |
$retryable = $this->isDeadlockOrLockTimeoutError($lastError); |
| 241 |
if (!$retryable || $attempt >= $maxAttempts) { |
| 242 |
break; |
| 243 |
} |
| 244 |
// Small jitter prevents immediate lock re-collision. |
| 245 |
$sleepMicros = 100000 + random_int(0, 200000); |
| 246 |
usleep($sleepMicros); |
| 247 |
} |
| 248 |
|
| 249 |
if ($lastException != null) { |
| 250 |
throw $lastException; |
| 251 |
} |
| 252 |
if ($lastError !== '') { |
| 253 |
throw new Exception($lastError); |
| 254 |
} |
| 255 |
} |
| 256 |
|
| 257 |
/** |
| 258 |
* @param int|string $post_id |
| 259 |
* @return string|null |
| 260 |
*/ |
| 261 |
function getOldSlug($post_id) { |
| 262 |
// Sanitize post_id to prevent SQL injection |
| 263 |
$post_id = absint($post_id); |
| 264 |
|
| 265 |
// we order by meta_id desc so that the first row will have the most recent value. |
| 266 |
$query = "select meta_value from {wp_postmeta} \nwhere post_id = {post_id} " . |
| 267 |
" and meta_key = '_wp_old_slug' \n" . |
| 268 |
" order by meta_id desc"; |
| 269 |
$query = $this->f->str_replace('{post_id}', (string)$post_id, $query); |
| 270 |
|
| 271 |
$results = $this->queryAndGetResults($query); |
| 272 |
|
| 273 |
$rows = $results['rows']; |
| 274 |
if ($rows == null || empty($rows)) { |
| 275 |
return null; |
| 276 |
} |
| 277 |
|
| 278 |
$rows = is_array($rows) ? $rows : array(); |
| 279 |
$row = is_array($rows[0] ?? null) ? $rows[0] : array(); |
| 280 |
return isset($row['meta_value']) && is_string($row['meta_value']) ? $row['meta_value'] : null; |
| 281 |
} |
| 282 |
|
| 283 |
/** @return void */ |
| 284 |
function truncatePermalinkCacheTable(): void { |
| 285 |
global $wpdb; |
| 286 |
|
| 287 |
$query = "truncate table {wp_abj404_permalink_cache}"; |
| 288 |
$this->queryAndGetResults($query); |
| 289 |
|
| 290 |
// Invalidate coverage ratio since permalink count changed |
| 291 |
abj_service('ngram_filter')->invalidateCoverageCaches(); |
| 292 |
} |
| 293 |
|
| 294 |
/** @param int $post_id @return void */ |
| 295 |
function removeFromPermalinkCache(int $post_id): void { |
| 296 |
global $wpdb; |
| 297 |
|
| 298 |
$query = "delete from {wp_abj404_permalink_cache} where id = %d"; |
| 299 |
$this->queryAndGetResults($query, array('query_params' => array($post_id))); |
| 300 |
|
| 301 |
// Invalidate coverage ratio since permalink count changed |
| 302 |
abj_service('ngram_filter')->invalidateCoverageCaches(); |
| 303 |
} |
| 304 |
|
| 305 |
/** @return array<int, array<string, mixed>>|null */ |
| 306 |
function getIDsNeededForPermalinkCache() { |
| 307 |
$abj404logic = abj_service('plugin_logic'); |
| 308 |
|
| 309 |
// get the valid post types |
| 310 |
$options = $abj404logic->getOptions(); |
| 311 |
$recognizedPostTypes = $this->buildPostTypeSqlList($options); |
| 312 |
if ($recognizedPostTypes === '') { |
| 313 |
return null; |
| 314 |
} |
| 315 |
|
| 316 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/getIDsNeededForPermalinkCache.sql"); |
| 317 |
$query = $this->f->str_replace('{recognizedPostTypes}', $recognizedPostTypes, $query); |
| 318 |
|
| 319 |
$results = $this->queryAndGetResults($query); |
| 320 |
|
| 321 |
/** @var array<int, array<string, mixed>>|null $rows */ |
| 322 |
$rows = $results['rows']; |
| 323 |
return $rows; |
| 324 |
} |
| 325 |
|
| 326 |
/** |
| 327 |
* @param int|string $id |
| 328 |
* @return string|null |
| 329 |
*/ |
| 330 |
function getPermalinkFromCache($id) { |
| 331 |
// Sanitize id to prevent SQL injection |
| 332 |
$id = absint($id); |
| 333 |
$query = "select url from {wp_abj404_permalink_cache} where id = " . $id; |
| 334 |
$results = $this->queryAndGetResults($query); |
| 335 |
|
| 336 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 337 |
if (empty($rows)) { |
| 338 |
return null; |
| 339 |
} |
| 340 |
|
| 341 |
$row1 = is_array($rows[0] ?? null) ? $rows[0] : array(); |
| 342 |
return isset($row1['url']) && is_string($row1['url']) ? $row1['url'] : null; |
| 343 |
} |
| 344 |
|
| 345 |
/** |
| 346 |
* Batch-fetch permalinks for multiple IDs from the permalink cache. |
| 347 |
* |
| 348 |
* @param array<int, int> $ids |
| 349 |
* @return array<int, object> Rows with id and url columns |
| 350 |
*/ |
| 351 |
function getPermalinksByIds(array $ids) { |
| 352 |
if (empty($ids)) { |
| 353 |
return array(); |
| 354 |
} |
| 355 |
$sanitized = array_map('absint', $ids); |
| 356 |
$placeholders = implode(',', $sanitized); |
| 357 |
$query = "select id, url from {wp_abj404_permalink_cache} where id in (" . $placeholders . ")"; |
| 358 |
$query = $this->doTableNameReplacements($query); |
| 359 |
$results = $this->queryAndGetResults($query); |
| 360 |
return is_array($results['rows']) ? $results['rows'] : array(); |
| 361 |
} |
| 362 |
|
| 363 |
/** |
| 364 |
* @param int|string $id |
| 365 |
* @return array<string, mixed>|null |
| 366 |
*/ |
| 367 |
function getPermalinkEtcFromCache($id) { |
| 368 |
// Sanitize id to prevent SQL injection |
| 369 |
$id = absint($id); |
| 370 |
$query = "select id, url, meta, url_length, post_parent from {wp_abj404_permalink_cache} where id = " . $id; |
| 371 |
$results = $this->queryAndGetResults($query); |
| 372 |
|
| 373 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 374 |
if (empty($rows)) { |
| 375 |
return null; |
| 376 |
} |
| 377 |
|
| 378 |
return is_array($rows[0] ?? null) ? $rows[0] : null; |
| 379 |
} |
| 380 |
|
| 381 |
/** |
| 382 |
* Delete duplicate rows in the lookup table. Called from |
| 383 |
* correctIssuesBefore() during the upgrade flow, which runs *before* |
| 384 |
* runInitialCreateTables() — so on a fresh install (or after the |
| 385 |
* upgrade flow drops a stripped table for clean recreation) the lookup |
| 386 |
* table may not yet exist. Suppress errors and skip the table-repair |
| 387 |
* retry path: there's nothing to clean up if the table doesn't exist, |
| 388 |
* and we don't want this maintenance call to set the missing_table |
| 389 |
* admin notice transient that will then surface as a `.notice-error`. |
| 390 |
* |
| 391 |
* @return void |
| 392 |
*/ |
| 393 |
function correctDuplicateLookupValues(): void { |
| 394 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/correctLookupTableIssue.sql"); |
| 395 |
$this->queryAndGetResults($query, array( |
| 396 |
'log_errors' => false, |
| 397 |
'skip_repair' => true, |
| 398 |
)); |
| 399 |
} |
| 400 |
|
| 401 |
/** |
| 402 |
* @param string $requestedURLRaw |
| 403 |
* @param mixed $returnValue |
| 404 |
* @return void |
| 405 |
*/ |
| 406 |
function storeSpellingPermalinksToCache(string $requestedURLRaw, $returnValue): void { |
| 407 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/insertSpellingCache.sql"); |
| 408 |
|
| 409 |
// Sanitize invalid UTF-8 sequences before storing to database |
| 410 |
// This prevents "Could not perform query because it contains invalid data" errors |
| 411 |
// when URLs contain invalid UTF-8 byte sequences (e.g., %c1%1c from scanner probes) |
| 412 |
$cleanURL = $this->f->sanitizeInvalidUTF8($requestedURLRaw); |
| 413 |
|
| 414 |
$query = $this->f->str_replace('{url}', esc_sql($cleanURL), $query); |
| 415 |
$jsonEncoded = json_encode($returnValue); |
| 416 |
$query = $this->f->str_replace('{matchdata}', esc_sql(is_string($jsonEncoded) ? $jsonEncoded : ''), $query); |
| 417 |
|
| 418 |
$this->queryAndGetResults($query); |
| 419 |
} |
| 420 |
|
| 421 |
/** |
| 422 |
* @cache-write-audit: opt-out — spelling_cache is itself the cache; |
| 423 |
* SpellChecker recomputes lookups on demand from {wp_abj404_redirects} |
| 424 |
* and {wp_abj404_permalink_cache}, neither of which derives a transient |
| 425 |
* from spelling_cache rows. A grep for `spelling_cache` against |
| 426 |
* includes/ confirms no transient/option keys depend on it. No |
| 427 |
* dependent caches to invalidate. |
| 428 |
* |
| 429 |
* @return void |
| 430 |
*/ |
| 431 |
function deleteSpellingCache(): void { |
| 432 |
$query = "truncate table {wp_abj404_spelling_cache}"; |
| 433 |
|
| 434 |
$this->queryAndGetResults($query); |
| 435 |
} |
| 436 |
|
| 437 |
/** |
| 438 |
* Find redirects whose destination URL appears in the 404 log as a recent 404. |
| 439 |
* Only internal URL destinations can be detected this way (external 404s are not |
| 440 |
* logged by this plugin). Stores flagged redirect IDs in a transient for fast |
| 441 |
* lookup at redirect-processing time. |
| 442 |
* |
| 443 |
* Joins the pre-aggregated wp_abj404_logs_hits rollup (NOT raw logsv2) and |
| 444 |
* filters on the `failed_hits` column — the count of 404-only hits per |
| 445 |
* canonical URL, computed by the rollup builder via |
| 446 |
* SUM(CASE WHEN dest_url='' OR dest_url IS NULL THEN 1 ELSE 0 END). This |
| 447 |
* scales the cron's cost with URL cardinality (thousands), not raw hit |
| 448 |
* cardinality (millions). The previous implementation INNER JOINed |
| 449 |
* wp_abj404_logsv2 on a string column with a timestamp filter — same |
| 450 |
* O(N rows) shape that timed out the digest cron on busy sites |
| 451 |
* (audit finding G1; mirrors commit 9133848d for getHighImpactCapturedCount). |
| 452 |
* |
| 453 |
* Fallback when the rollup is missing or pre-dates the failed_hits column |
| 454 |
* (existing installs upgrading): schedule a rebuild and store an empty |
| 455 |
* list. Never falls back to scanning logsv2. |
| 456 |
* |
| 457 |
* h.requested_url is stored canonical (leading '/', no trailing '/') by the |
| 458 |
* rollup builder; r.final_dest is canonicalized at JOIN time so legacy |
| 459 |
* destinations with or without leading/trailing slashes match the same |
| 460 |
* indexed h.requested_url row. The CONCAT/TRIM is on r.final_dest (outer |
| 461 |
* side of the join), which has thousands of rows — small enough that the |
| 462 |
* per-row expression cost is dominated by the indexed h.requested_url |
| 463 |
* lookup. |
| 464 |
* |
| 465 |
* @return void |
| 466 |
*/ |
| 467 |
function flagDeadDestinationRedirects(): void { |
| 468 |
$cutoff = time() - 7 * 86400; |
| 469 |
$flaggedIds = array(); |
| 470 |
|
| 471 |
// Skip silently when the rollup is missing or hasn't been rebuilt |
| 472 |
// since the failed_hits column was added: schedule a rebuild and |
| 473 |
// store an empty list. A degraded cron cycle is acceptable; falling |
| 474 |
// back to scanning logsv2 is not. |
| 475 |
if (!$this->logsHitsTableExists() || !$this->logsHitsHasFailedHitsColumn()) { |
| 476 |
$this->scheduleHitsTableRebuild(); |
| 477 |
$this->storeDeadDestIdsTransient($flaggedIds); |
| 478 |
return; |
| 479 |
} |
| 480 |
|
| 481 |
// 30s timeout: the rollup-side JOIN should complete in milliseconds. |
| 482 |
// A blown timeout here signals rollup corruption / lock contention, |
| 483 |
// not "logsv2 is huge" — fast failure is the right behavior. |
| 484 |
$sql = "SELECT DISTINCT r.id |
| 485 |
FROM {wp_abj404_redirects} r |
| 486 |
INNER JOIN {wp_abj404_logs_hits} h |
| 487 |
ON BINARY h.requested_url = BINARY CONCAT('/', TRIM(BOTH '/' FROM r.final_dest)) |
| 488 |
WHERE h.last_used > %d |
| 489 |
AND h.failed_hits > 0 |
| 490 |
AND r.disabled = 0 |
| 491 |
AND r.final_dest != '' |
| 492 |
AND r.final_dest != '0'"; |
| 493 |
$sql = $this->doTableNameReplacements($sql); |
| 494 |
|
| 495 |
$result = $this->queryAndGetResults($sql, array( |
| 496 |
'query_params' => array($cutoff), |
| 497 |
'timeout' => 30, |
| 498 |
)); |
| 499 |
|
| 500 |
if (empty($result['timed_out']) && (!isset($result['last_error']) || $result['last_error'] == '')) { |
| 501 |
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 502 |
foreach ($rows as $row) { |
| 503 |
if (is_array($row)) { |
| 504 |
$value = $row['id'] ?? reset($row); |
| 505 |
} elseif (is_object($row)) { |
| 506 |
$value = $row->id ?? null; |
| 507 |
} else { |
| 508 |
$value = $row; |
| 509 |
} |
| 510 |
if ($value !== null && $value !== '') { |
| 511 |
$flaggedIds[] = (string)$value; |
| 512 |
} |
| 513 |
} |
| 514 |
} |
| 515 |
|
| 516 |
$this->storeDeadDestIdsTransient($flaggedIds); |
| 517 |
|
| 518 |
if (!empty($flaggedIds)) { |
| 519 |
$this->logger->infoMessage( |
| 520 |
__CLASS__ . '/' . __FUNCTION__ . ': Flagged ' . count($flaggedIds) . |
| 521 |
' redirect(s) with dead destinations: ' . implode(', ', $flaggedIds) |
| 522 |
); |
| 523 |
} |
| 524 |
} |
| 525 |
|
| 526 |
/** |
| 527 |
* Persist the dead-destination ID list. Extracted so the rollup-missing |
| 528 |
* fallback path stores the same empty-list shape as the success path — |
| 529 |
* stale data must never poison redirect handling. |
| 530 |
* |
| 531 |
* @param array<int, string> $flaggedIds |
| 532 |
* @return void |
| 533 |
*/ |
| 534 |
private function storeDeadDestIdsTransient(array $flaggedIds): void { |
| 535 |
if (function_exists('set_transient')) { |
| 536 |
$ttl = defined('HOUR_IN_SECONDS') ? 25 * (int) HOUR_IN_SECONDS : 90000; |
| 537 |
set_transient('abj404_dead_dest_ids', $flaggedIds, $ttl); |
| 538 |
} |
| 539 |
} |
| 540 |
|
| 541 |
/** |
| 542 |
* Detect whether the live wp_abj404_logs_hits table has the failed_hits |
| 543 |
* column. Existing installs that rebuilt the rollup before the column |
| 544 |
* was added will have the older 4-column schema; the next rollup pass |
| 545 |
* recreates the table with the new column included. Until then, the |
| 546 |
* cron must skip silently rather than emit a query that errors with |
| 547 |
* "Unknown column 'h.failed_hits'". |
| 548 |
* |
| 549 |
* Uses information_schema (single indexed lookup); the SHOW COLUMNS |
| 550 |
* fallback in queryAndGetResults handles hosts that restrict |
| 551 |
* information_schema access. |
| 552 |
* |
| 553 |
* @return bool |
| 554 |
*/ |
| 555 |
private function logsHitsHasFailedHitsColumn(): bool { |
| 556 |
$tableName = $this->doTableNameReplacements('{wp_abj404_logs_hits}'); |
| 557 |
$sql = "SELECT 1 FROM information_schema.columns " |
| 558 |
. "WHERE table_schema = DATABASE() " |
| 559 |
. "AND table_name = %s " |
| 560 |
. "AND column_name = 'failed_hits' LIMIT 1"; |
| 561 |
$result = $this->queryAndGetResults($sql, array( |
| 562 |
'query_params' => array($tableName), |
| 563 |
'log_errors' => false, |
| 564 |
)); |
| 565 |
if (!empty($result['last_error'])) { |
| 566 |
return false; |
| 567 |
} |
| 568 |
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 569 |
return !empty($rows); |
| 570 |
} |
| 571 |
|
| 572 |
/** |
| 573 |
* Move auto-created redirects to trash if they are older than the configured expiration. |
| 574 |
* |
| 575 |
* Uses the `timestamp` column (creation time) of the redirects table. Only affects |
| 576 |
* redirects with status = ABJ404_STATUS_AUTO that are not already disabled. The |
| 577 |
* threshold is controlled by the `auto_302_expiration_days` option (0 = disabled). |
| 578 |
* |
| 579 |
* @return int Number of redirects moved to trash |
| 580 |
*/ |
| 581 |
public function expireOldAutoRedirects(): int { |
| 582 |
$options = abj_service('plugin_logic')->getOptions(); |
| 583 |
$daysRaw = isset($options['auto_302_expiration_days']) ? $options['auto_302_expiration_days'] : 0; |
| 584 |
$days = is_numeric($daysRaw) ? (int)$daysRaw : 0; |
| 585 |
if ($days <= 0) { |
| 586 |
return 0; |
| 587 |
} |
| 588 |
|
| 589 |
$redirectsTable = $this->doTableNameReplacements('{wp_abj404_redirects}'); |
| 590 |
if (!$this->tableExists($redirectsTable)) { |
| 591 |
$this->logger->warn("expireOldAutoRedirects: redirects table missing, skipping."); |
| 592 |
return 0; |
| 593 |
} |
| 594 |
|
| 595 |
$cutoff = time() - ($days * 86400); |
| 596 |
|
| 597 |
// Route through queryAndGetResults() so this cron query inherits the |
| 598 |
// centralized timeout, retry, and corrupted-table recovery. The query |
| 599 |
// is small (redirects table only) but the table can grow on busy |
| 600 |
// sites and a long-held write lock could otherwise hang the cron. |
| 601 |
$sql = "SELECT id FROM `{$redirectsTable}` |
| 602 |
WHERE status = %d |
| 603 |
AND disabled = 0 |
| 604 |
AND `timestamp` > 0 |
| 605 |
AND `timestamp` < %d"; |
| 606 |
|
| 607 |
$result = $this->queryAndGetResults($sql, array( |
| 608 |
'query_params' => array(ABJ404_STATUS_AUTO, $cutoff), |
| 609 |
)); |
| 610 |
|
| 611 |
if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) { |
| 612 |
// queryAndGetResults() already logged the error/timeout; treat as no-op. |
| 613 |
return 0; |
| 614 |
} |
| 615 |
|
| 616 |
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 617 |
$ids = array(); |
| 618 |
foreach ($rows as $row) { |
| 619 |
if (is_array($row)) { |
| 620 |
$value = $row['id'] ?? reset($row); |
| 621 |
} elseif (is_object($row)) { |
| 622 |
$value = $row->id ?? null; |
| 623 |
} else { |
| 624 |
$value = $row; |
| 625 |
} |
| 626 |
if ($value !== null && $value !== '') { |
| 627 |
$ids[] = absint($value); |
| 628 |
} |
| 629 |
} |
| 630 |
|
| 631 |
if (empty($ids)) { |
| 632 |
return 0; |
| 633 |
} |
| 634 |
|
| 635 |
$moved = 0; |
| 636 |
foreach ($ids as $id) { |
| 637 |
$this->moveRedirectsToTrash($id, 1); |
| 638 |
$moved++; |
| 639 |
} |
| 640 |
|
| 641 |
$this->logger->infoMessage("expireOldAutoRedirects: moved {$moved} expired auto-redirect(s) to trash (threshold: {$days} days)."); |
| 642 |
return $moved; |
| 643 |
} |
| 644 |
|
| 645 |
/** |
| 646 |
* @param string $requestedURLRaw |
| 647 |
* @return mixed |
| 648 |
*/ |
| 649 |
function getSpellingPermalinksFromCache(string $requestedURLRaw) { |
| 650 |
// Sanitize invalid UTF-8 before SQL to prevent database errors |
| 651 |
$requestedURLRaw = $this->f->sanitizeInvalidUTF8($requestedURLRaw); |
| 652 |
$query = "select id, url, matchdata from {wp_abj404_spelling_cache} where url = '" . esc_sql($requestedURLRaw) . "'"; |
| 653 |
$results = $this->queryAndGetResults($query); |
| 654 |
|
| 655 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 656 |
|
| 657 |
if (empty($rows)) { |
| 658 |
return array(); |
| 659 |
} |
| 660 |
|
| 661 |
$row = is_array($rows[0] ?? null) ? $rows[0] : array(); |
| 662 |
$json = isset($row['matchdata']) && is_string($row['matchdata']) ? $row['matchdata'] : ''; |
| 663 |
$returnValue = json_decode($json, true); |
| 664 |
|
| 665 |
return $returnValue; |
| 666 |
} |
| 667 |
|
| 668 |
} |
| 669 |
|
