| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
/** |
| 8 |
* Logs hits rollup rebuild path. Owns the lifecycle of the |
| 9 |
* `{wp_abj404_logs_hits}` table — staleness checks, rebuild orchestration, |
| 10 |
* and the two INSERT strategies (single-statement direct path for small |
| 11 |
* sites, chunked pre-aggregation for large sites). Split out of |
| 12 |
* DataAccessTrait_Logs in 4.1.10+ to keep the parent trait under the |
| 13 |
* file-size limit; the rebuild subsystem is a clear seam. |
| 14 |
* |
| 15 |
* Composed into ABJ_404_Solution_DataAccess alongside the other DAO traits. |
| 16 |
* Depends on host-class state and methods provided by the rest of DAO: |
| 17 |
* `queryAndGetResults`, `doTableNameReplacements`, `getMaxLogId`, |
| 18 |
* `getMinLogId`, `getStoredMaxLogId`, `getRuntimeFlag`, `setRuntimeFlag`, |
| 19 |
* `executeAsTransaction`, `shouldSkipNonEssentialDbWrites`, |
| 20 |
* `acquireHitsTableRebuildLock`, `releaseHitsTableRebuildLock`, |
| 21 |
* the `$logger` property, and HITS_TABLE_* constants on the host class. |
| 22 |
*/ |
| 23 |
trait ABJ_404_Solution_DataAccess_LogsHitsRebuildTrait { |
| 24 |
|
| 25 |
/** |
| 26 |
* Detect a stalled `logs_hits` rollup using the `max_log_id` age signal. |
| 27 |
* |
| 28 |
* Compares `MAX(logsv2.id)` against the watermark stored in the |
| 29 |
* `logs_hits` table comment by the most recent successful rebuild. A |
| 30 |
* persistent gap means rows have continued arriving in `logsv2` while |
| 31 |
* the cron-driven rebuild (`abj404_updateLogsHitsTableAction`) has not |
| 32 |
* advanced the rollup, which is almost always a broken or disabled |
| 33 |
* WordPress cron, since the rebuild itself is fast and bounded. |
| 34 |
* |
| 35 |
* State machine, recorded as runtime flags: |
| 36 |
* - gap absent: clear both the first-detected flag and any open |
| 37 |
* admin-notice transient (self-heal). |
| 38 |
* - gap present, no prior flag: stamp the first-detected timestamp |
| 39 |
* and wait. A single observation is not enough to claim cron is |
| 40 |
* broken, since a healthy rollup may be momentarily behind while |
| 41 |
* cron runs. |
| 42 |
* - gap present, flag age at or above threshold: set a deduplicated |
| 43 |
* 24h admin-notice transient that the existing notice renderer in |
| 44 |
* 404-solution.php surfaces on the plugin's own admin screen. |
| 45 |
* |
| 46 |
* Called from `maybeUpdateRedirectsForViewHitsTable()` so every admin |
| 47 |
* paint of the redirects page re-evaluates the signal; cleared on a |
| 48 |
* successful rebuild inside `createRedirectsForViewHitsTable()`. |
| 49 |
* |
| 50 |
* @return void |
| 51 |
*/ |
| 52 |
function recordLogsHitsRollupStalenessSignal(): void { |
| 53 |
$currentMaxLogId = $this->getMaxLogId(); |
| 54 |
$storedMaxLogId = $this->getStoredMaxLogId(); |
| 55 |
|
| 56 |
if ($currentMaxLogId <= $storedMaxLogId) { |
| 57 |
$this->clearLogsHitsRollupStaleSignal(); |
| 58 |
return; |
| 59 |
} |
| 60 |
|
| 61 |
$rawFirstStale = $this->getRuntimeFlag(self::HITS_TABLE_FIRST_STALE_DETECTED_FLAG); |
| 62 |
$firstStale = is_scalar($rawFirstStale) ? (int)$rawFirstStale : 0; |
| 63 |
if ($firstStale <= 0) { |
| 64 |
$this->setRuntimeFlag(self::HITS_TABLE_FIRST_STALE_DETECTED_FLAG, time(), 86400); |
| 65 |
return; |
| 66 |
} |
| 67 |
|
| 68 |
$age = time() - $firstStale; |
| 69 |
if ($age >= self::HITS_TABLE_STALE_NOTICE_THRESHOLD_SECONDS) { |
| 70 |
$this->setLogsHitsRollupStaleNotice($age); |
| 71 |
} |
| 72 |
} |
| 73 |
|
| 74 |
/** |
| 75 |
* Self-heal: clear the staleness detection flag and any open admin |
| 76 |
* notice. Called when the gap closes (rebuild caught up) or directly |
| 77 |
* from `createRedirectsForViewHitsTable()` after a successful rebuild. |
| 78 |
* |
| 79 |
* @return void |
| 80 |
*/ |
| 81 |
private function clearLogsHitsRollupStaleSignal(): void { |
| 82 |
if (function_exists('delete_transient')) { |
| 83 |
delete_transient(self::HITS_TABLE_FIRST_STALE_DETECTED_FLAG); |
| 84 |
delete_transient(self::HITS_TABLE_STALE_NOTICE_TRANSIENT); |
| 85 |
return; |
| 86 |
} |
| 87 |
if (function_exists('delete_option')) { |
| 88 |
delete_option(self::HITS_TABLE_FIRST_STALE_DETECTED_FLAG); |
| 89 |
delete_option(self::HITS_TABLE_STALE_NOTICE_TRANSIENT); |
| 90 |
} |
| 91 |
} |
| 92 |
|
| 93 |
/** |
| 94 |
* Set a deduplicated admin notice that the cron-driven rollup rebuild |
| 95 |
* has not been advancing. Same 24h dedup TTL as the sibling |
| 96 |
* view-build cron-stuck notices so the broken-cron notice family |
| 97 |
* shares a consistent lifecycle. |
| 98 |
* |
| 99 |
* @param int $ageSeconds How long the rollup has been behind. |
| 100 |
* @return void |
| 101 |
*/ |
| 102 |
private function setLogsHitsRollupStaleNotice(int $ageSeconds): void { |
| 103 |
if (!function_exists('set_transient')) { |
| 104 |
return; |
| 105 |
} |
| 106 |
$key = self::HITS_TABLE_STALE_NOTICE_TRANSIENT; |
| 107 |
if (function_exists('get_transient') && get_transient($key) !== false) { |
| 108 |
return; // dedup window still active |
| 109 |
} |
| 110 |
$hours = max(1, intval(floor($ageSeconds / 3600))); |
| 111 |
$template = $this->localizeOrDefaultViewBuildNotice( |
| 112 |
'The 404 Solution redirects-hits rollup has been behind MAX(logsv2.id) ' |
| 113 |
. 'for at least %d hour(s). The cron-driven rebuild event ' |
| 114 |
. '(abj404_updateLogsHitsTableAction) does not appear to be firing, so ' |
| 115 |
. 'the redirects list will show stale "hits" and "last hit" columns ' |
| 116 |
. 'until cron resumes. To resolve: if DISABLE_WP_CRON is set in ' |
| 117 |
. 'wp-config.php either remove it, or configure a system cron job ' |
| 118 |
. 'that requests wp-cron.php periodically. To force a rebuild right ' |
| 119 |
. 'now in your browser, open the 404 Solution Redirects page with ' |
| 120 |
. '?abj404_force_view_rebuild=1 appended to the URL.' |
| 121 |
); |
| 122 |
$payload = array( |
| 123 |
'type' => 'logs_hits_rollup_stale', |
| 124 |
'message' => sprintf($template, $hours), |
| 125 |
'timestamp' => time(), |
| 126 |
'error_string' => '', |
| 127 |
'age_hours' => $hours, |
| 128 |
); |
| 129 |
// allow-cache-empty: intentional notice payload; error_string is empty by definition for stale-rollup state. |
| 130 |
set_transient($key, $payload, 86400); |
| 131 |
} |
| 132 |
|
| 133 |
/** @return bool */ |
| 134 |
function hitsTableNeedsRebuild() { |
| 135 |
$storedMaxId = $this->getStoredMaxLogId(); |
| 136 |
$currentMaxId = $this->getMaxLogId(); |
| 137 |
|
| 138 |
// Check if log entries have changed |
| 139 |
if ($currentMaxId != $storedMaxId) { |
| 140 |
$this->logger->debugMessage(__FUNCTION__ . " rebuild=yes (max_id changed: stored=$storedMaxId, current=$currentMaxId)"); |
| 141 |
return true; |
| 142 |
} |
| 143 |
|
| 144 |
// Check if table is too old (staleness check) |
| 145 |
$lastUpdated = $this->getLogsHitsTableLastUpdated(); |
| 146 |
if ($lastUpdated !== null) { |
| 147 |
$age = time() - $lastUpdated; |
| 148 |
if ($age > self::HITS_TABLE_MAX_AGE_SECONDS) { |
| 149 |
$this->logger->debugMessage(__FUNCTION__ . " rebuild=yes (stale: age={$age}s > " . self::HITS_TABLE_MAX_AGE_SECONDS . "s)"); |
| 150 |
return true; |
| 151 |
} |
| 152 |
} |
| 153 |
|
| 154 |
$this->logger->debugMessage(__FUNCTION__ . " rebuild=no (max_id=$currentMaxId unchanged, not stale)"); |
| 155 |
return false; |
| 156 |
} |
| 157 |
|
| 158 |
/** |
| 159 |
* Get the last update time of the logs_hits table. |
| 160 |
* |
| 161 |
* Uses the MySQL table creation time from information_schema since |
| 162 |
* the table is dropped and recreated on each rebuild. |
| 163 |
* |
| 164 |
* @return int|null Unix timestamp of last update, or null if table doesn't exist |
| 165 |
*/ |
| 166 |
function getLogsHitsTableLastUpdated() { |
| 167 |
$rawRefreshedFlag = $this->getRuntimeFlag(self::HITS_TABLE_LAST_REFRESHED_FLAG); |
| 168 |
$runtimeRefreshedAt = is_scalar($rawRefreshedFlag) ? (int)$rawRefreshedFlag : 0; |
| 169 |
$runtimeRefreshedAt = $runtimeRefreshedAt > 0 ? $runtimeRefreshedAt : null; |
| 170 |
|
| 171 |
$query = "SELECT create_time FROM information_schema.tables WHERE table_name = '{wp_abj404_logs_hits}' AND table_schema = DATABASE()"; |
| 172 |
$query = $this->doTableNameReplacements($query); |
| 173 |
$results = $this->queryAndGetResults($query); |
| 174 |
|
| 175 |
if ($results['rows'] == null || empty($results['rows'])) { |
| 176 |
if (!empty($results['last_error'])) { |
| 177 |
$statusRow = $this->getLogsHitsTableStatusRow(); |
| 178 |
$dateValue = ''; |
| 179 |
if (is_array($statusRow)) { |
| 180 |
$dateValue = $statusRow['update_time'] ?? ($statusRow['create_time'] ?? ''); |
| 181 |
} |
| 182 |
if ($dateValue !== '') { |
| 183 |
$fallbackTimestamp = strtotime(is_string($dateValue) ? $dateValue : ''); |
| 184 |
if ($fallbackTimestamp !== false) { |
| 185 |
if ($runtimeRefreshedAt !== null && $runtimeRefreshedAt > $fallbackTimestamp) { |
| 186 |
return $runtimeRefreshedAt; |
| 187 |
} |
| 188 |
return $fallbackTimestamp; |
| 189 |
} |
| 190 |
} |
| 191 |
} |
| 192 |
return $runtimeRefreshedAt; |
| 193 |
} |
| 194 |
|
| 195 |
$hitsRows = is_array($results['rows']) ? $results['rows'] : array(); |
| 196 |
$row = is_array($hitsRows[0] ?? null) ? $hitsRows[0] : array(); |
| 197 |
$row = array_change_key_case($row); |
| 198 |
$createTime = $row['create_time'] ?? null; |
| 199 |
|
| 200 |
if ($createTime === null) { |
| 201 |
return $runtimeRefreshedAt; |
| 202 |
} |
| 203 |
|
| 204 |
// Convert MySQL datetime to Unix timestamp |
| 205 |
$schemaTimestamp = strtotime(is_string($createTime) ? $createTime : ''); |
| 206 |
if ($schemaTimestamp === false) { |
| 207 |
return $runtimeRefreshedAt; |
| 208 |
} |
| 209 |
if ($runtimeRefreshedAt !== null && $runtimeRefreshedAt > $schemaTimestamp) { |
| 210 |
return $runtimeRefreshedAt; |
| 211 |
} |
| 212 |
return $schemaTimestamp; |
| 213 |
} |
| 214 |
|
| 215 |
/** @return array<string, mixed> */ |
| 216 |
private function getLogsHitsTableStatusRow() { |
| 217 |
global $wpdb; |
| 218 |
if (!isset($wpdb) || !method_exists($wpdb, 'prepare')) { |
| 219 |
return array(); |
| 220 |
} |
| 221 |
$tableName = $this->doTableNameReplacements('{wp_abj404_logs_hits}'); |
| 222 |
/** @var wpdb $wpdb */ |
| 223 |
$query = $wpdb->prepare("SHOW TABLE STATUS LIKE %s", $tableName); |
| 224 |
if ($query === null) { |
| 225 |
return array(); |
| 226 |
} |
| 227 |
$results = $this->queryAndGetResults($query, array('log_errors' => false)); |
| 228 |
if (!is_array($results['rows']) || empty($results['rows']) || !is_array($results['rows'][0])) { |
| 229 |
return array(); |
| 230 |
} |
| 231 |
return array_change_key_case($results['rows'][0], CASE_LOWER); |
| 232 |
} |
| 233 |
|
| 234 |
/** |
| 235 |
* Get a human-readable "time ago" string for the hits table's last update. |
| 236 |
* |
| 237 |
* @return string e.g., "2 minutes ago", "1 hour ago", or empty string if unknown |
| 238 |
*/ |
| 239 |
function getLogsHitsTableLastUpdatedHuman() { |
| 240 |
$timestamp = $this->getLogsHitsTableLastUpdated(); |
| 241 |
|
| 242 |
if ($timestamp === null) { |
| 243 |
return ''; |
| 244 |
} |
| 245 |
|
| 246 |
$diff = time() - $timestamp; |
| 247 |
|
| 248 |
if ($diff < 60) { |
| 249 |
return __('Just now', '404-solution'); |
| 250 |
} elseif ($diff < 3600) { |
| 251 |
$minutes = (int)floor($diff / 60); |
| 252 |
return sprintf(_n('%d minute ago', '%d minutes ago', $minutes, '404-solution'), $minutes); |
| 253 |
} elseif ($diff < 86400) { |
| 254 |
$hours = (int)floor($diff / 3600); |
| 255 |
return sprintf(_n('%d hour ago', '%d hours ago', $hours, '404-solution'), $hours); |
| 256 |
} else { |
| 257 |
$days = (int)floor($diff / 86400); |
| 258 |
return sprintf(_n('%d day ago', '%d days ago', $days, '404-solution'), $days); |
| 259 |
} |
| 260 |
} |
| 261 |
|
| 262 |
/** @return bool */ |
| 263 |
function createRedirectsForViewHitsTable(): bool { |
| 264 |
$wasRefreshed = false; |
| 265 |
if ($this->shouldSkipNonEssentialDbWrites()) { |
| 266 |
$this->logger->debugMessage(__FUNCTION__ . " skipped due to temporary DB write cooldown."); |
| 267 |
$this->setRuntimeFlag(self::HITS_TABLE_LAST_DECISION_FLAG, 'paused', 86400); |
| 268 |
return false; |
| 269 |
} |
| 270 |
if (!$this->acquireHitsTableRebuildLock()) { |
| 271 |
$this->logger->debugMessage(__FUNCTION__ . " skipped because rebuild lock is already held."); |
| 272 |
$this->setRuntimeFlag(self::HITS_TABLE_LAST_DECISION_FLAG, 'running', 86400); |
| 273 |
return false; |
| 274 |
} |
| 275 |
$preAggTable = $this->doTableNameReplacements("{wp_abj404_logs_hits}_preagg"); |
| 276 |
try { |
| 277 |
|
| 278 |
$finalDestTable = $this->doTableNameReplacements("{wp_abj404_logs_hits}"); |
| 279 |
$tempDestTable = $this->doTableNameReplacements("{wp_abj404_logs_hits}_temp"); |
| 280 |
|
| 281 |
// create the temp output table |
| 282 |
$this->queryAndGetResults("drop table if exists " . $tempDestTable); |
| 283 |
$createTempTableQuery = ABJ_404_Solution_Functions::readFileContents(__DIR__ . |
| 284 |
"/sql/createLogsHitsTempTable.sql"); |
| 285 |
$createTempTableQuery = $this->doTableNameReplacements($createTempTableQuery); |
| 286 |
$this->queryAndGetResults($createTempTableQuery); |
| 287 |
// @cache-write-audit: opt-out — temp table internal to this rebuild |
| 288 |
// (`{wp_abj404_logs_hits}_temp`); no other code path reads it, so no |
| 289 |
// dependent caches exist to invalidate. The atomic swap to the live |
| 290 |
// `{wp_abj404_logs_hits}` table later in this function is the only |
| 291 |
// observable effect. |
| 292 |
$this->queryAndGetResults("truncate table " . $tempDestTable); |
| 293 |
|
| 294 |
// Capture a pre-insert snapshot watermark. |
| 295 |
// This keeps rebuild checks consistent with getMaxLogId() while avoiding |
| 296 |
// claiming coverage for rows that may arrive during/after the insert. |
| 297 |
$maxLogIdSnapshot = $this->getMaxLogId(); |
| 298 |
$minLogId = $this->getMinLogId(); |
| 299 |
$chunkSize = self::HITS_TABLE_PREAGG_CHUNK_SIZE; |
| 300 |
$idRange = $maxLogIdSnapshot - $minLogId; |
| 301 |
|
| 302 |
// Tiny-table fast path: only skip pre-aggregation for trivially small |
| 303 |
// tables. Above this threshold (HITS_TABLE_DIRECT_PATH_THRESHOLD) the |
| 304 |
// direct path's CONCAT/COALESCE-derived JOIN can hit the 60s ceiling |
| 305 |
// on shared hosts even at id ranges far below HITS_TABLE_PREAGG_CHUNK_SIZE |
| 306 |
// — log retention by timestamp lets MIN(id) climb monotonically, so |
| 307 |
// a site's id range converges to its live row count. |
| 308 |
if ($idRange <= self::HITS_TABLE_DIRECT_PATH_THRESHOLD) { |
| 309 |
$results = $this->hitsTableInsertDirect($tempDestTable); |
| 310 |
} else { |
| 311 |
$results = $this->hitsTableInsertChunked( |
| 312 |
$tempDestTable, $preAggTable, $minLogId, $maxLogIdSnapshot, $chunkSize |
| 313 |
); |
| 314 |
} |
| 315 |
|
| 316 |
// If the query timed out or errored, don't replace the existing table with empty data. |
| 317 |
if ($results === false || !empty($results['timed_out']) || !empty($results['last_error'])) { |
| 318 |
$this->queryAndGetResults("drop table if exists " . $tempDestTable); |
| 319 |
$this->logger->debugMessage(__FUNCTION__ . " INSERT timed out or errored; aborting rebuild."); |
| 320 |
$this->setRuntimeFlag(self::HITS_TABLE_LAST_DECISION_FLAG, 'paused', 86400); |
| 321 |
return false; |
| 322 |
} |
| 323 |
|
| 324 |
// Store elapsed time and max log ID in comment for invalidation check |
| 325 |
// Format: "elapsed_time|max_log_id" (e.g., "0.35|12345") |
| 326 |
$elapsedTime = $results['elapsed_time']; |
| 327 |
$comment = $elapsedTime . '|' . $maxLogIdSnapshot; |
| 328 |
// @utf8-audit: opt-out — $comment is internally composed from numeric |
| 329 |
// elapsed-time and integer max-log-id values; never user-controlled. |
| 330 |
// Escape comment and truncate to MySQL's 2048 char limit for table comments |
| 331 |
$comment = substr(esc_sql($comment), 0, 2048); |
| 332 |
$addComment = "ALTER TABLE " . $tempDestTable . " COMMENT '" . $comment . "'"; |
| 333 |
$this->queryAndGetResults($addComment); |
| 334 |
|
| 335 |
// drop the old hits table and rename the temp table to the hits table as a transaction |
| 336 |
$statements = array( |
| 337 |
"drop table if exists " . $finalDestTable, |
| 338 |
"rename table " . $tempDestTable . ' to ' . $finalDestTable |
| 339 |
); |
| 340 |
$this->executeAsTransaction($statements); |
| 341 |
$this->setRuntimeFlag(self::HITS_TABLE_LAST_REFRESHED_FLAG, time(), 86400); |
| 342 |
// Self-heal the broken-cron diagnostic: a successful rebuild means |
| 343 |
// the cron event fired and the rollup is current, so any earlier |
| 344 |
// "rollup is stale" admin notice (and the first-detected flag that |
| 345 |
// produced it) is obsolete and should not linger for the full 24h |
| 346 |
// dedup TTL. |
| 347 |
$this->clearLogsHitsRollupStaleSignal(); |
| 348 |
$wasRefreshed = true; |
| 349 |
|
| 350 |
$this->logger->debugMessage(__FUNCTION__ . " refreshed " . $finalDestTable . " in " . $elapsedTime . |
| 351 |
" seconds."); |
| 352 |
} catch (Throwable $e) { |
| 353 |
// Never break the admin request because a shutdown refresh fails. |
| 354 |
$this->logger->errorMessage(__FUNCTION__ . " failed: " . $e->getMessage(), $e instanceof \Exception ? $e : null); |
| 355 |
$this->setRuntimeFlag(self::HITS_TABLE_LAST_DECISION_FLAG, 'paused', 86400); |
| 356 |
} finally { |
| 357 |
$this->queryAndGetResults("drop table if exists " . $preAggTable); |
| 358 |
$this->releaseHitsTableRebuildLock(); |
| 359 |
} |
| 360 |
return $wasRefreshed; |
| 361 |
} |
| 362 |
|
| 363 |
/** |
| 364 |
* Small-table fast path: single INSERT...SELECT with a DB-level timeout. |
| 365 |
* |
| 366 |
* @param string $tempDestTable |
| 367 |
* @return array<string, mixed> |
| 368 |
*/ |
| 369 |
private function hitsTableInsertDirect(string $tempDestTable): array { |
| 370 |
$ttSelectQuery = ABJ_404_Solution_Functions::readFileContents(__DIR__ . |
| 371 |
"/sql/getRedirectsForViewTempTable.sql"); |
| 372 |
|
| 373 |
// When the logsv2.canonical_url backfill has confirmed zero NULL |
| 374 |
// rows, drop the logsv2-side COALESCE so the planner can pick the |
| 375 |
// smaller side as the driving table and use idx_canonical_url for |
| 376 |
// the JOIN probe (~17,000x cost reduction per the |
| 377 |
// redirects-temp-table-perf writeup §3 EXPLAIN evidence). |
| 378 |
// Redirects-side COALESCE stays — backfillRedirectsCanonicalUrl |
| 379 |
// doesn't flip an explicit "complete" flag and we treat the |
| 380 |
// CONCAT/TRIM(redirects.url) fallback as the eternal safety net. |
| 381 |
if ($this->isLogsv2CanonicalUrlBackfillComplete()) { |
| 382 |
$ttSelectQuery = $this->dropLogsv2CanonicalCoalesceWrap($ttSelectQuery); |
| 383 |
} |
| 384 |
|
| 385 |
$ttSelectQuery = $this->doTableNameReplacements($ttSelectQuery); |
| 386 |
|
| 387 |
$ttInsertQuery = "/* abj404:src=DataAccessTrait_LogsHitsRebuild::hitsTableInsertDirect */ " . |
| 388 |
"insert into " . $tempDestTable . " (requested_url, logsid, " . |
| 389 |
"last_used, logshits, failed_hits) \n " . $ttSelectQuery; |
| 390 |
return $this->queryAndGetResults($ttInsertQuery, array('log_too_slow' => false, 'timeout' => 60)); |
| 391 |
} |
| 392 |
|
| 393 |
/** |
| 394 |
* Returns true once backfillLogsv2CanonicalUrl() has confirmed zero |
| 395 |
* remaining NULL rows on logsv2.canonical_url. Cached at request scope |
| 396 |
* via the wp_options layer (autoload=false → wp_cache hits a |
| 397 |
* non-autoloaded option once per request, then short-circuits). |
| 398 |
*/ |
| 399 |
private function isLogsv2CanonicalUrlBackfillComplete(): bool { |
| 400 |
if (!function_exists('get_option')) { |
| 401 |
return false; |
| 402 |
} |
| 403 |
$optName = ABJ_404_Solution_DatabaseUpgradesEtc::LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION; |
| 404 |
return (bool)get_option($optName); |
| 405 |
} |
| 406 |
|
| 407 |
/** |
| 408 |
* Strip the logsv2-side COALESCE wrapper from |
| 409 |
* getRedirectsForViewTempTable.sql so the JOIN expression collapses |
| 410 |
* to a bare {wp_abj404_logsv2}.canonical_url reference. Leaves the |
| 411 |
* redirects-side COALESCE alone (eternal safety net). |
| 412 |
* |
| 413 |
* Used only after isLogsv2CanonicalUrlBackfillComplete() returns true, |
| 414 |
* so the bare reference is guaranteed-non-NULL. |
| 415 |
* |
| 416 |
* @param string $sql |
| 417 |
* @return string |
| 418 |
*/ |
| 419 |
private function dropLogsv2CanonicalCoalesceWrap(string $sql): string { |
| 420 |
// Matches the three occurrences in the SQL file (SELECT, ON, GROUP |
| 421 |
// BY) regardless of indentation on the continuation line. Anchored |
| 422 |
// to the {wp_abj404_logsv2} placeholder so we never accidentally |
| 423 |
// touch the redirects-side COALESCE on the same line of the JOIN. |
| 424 |
$pattern = '/COALESCE\(\{wp_abj404_logsv2\}\.canonical_url,\s*CONCAT\(\'\/\',\s*TRIM\(BOTH\s+\'\/\'\s+FROM\s+\{wp_abj404_logsv2\}\.requested_url\)\)\)/'; |
| 425 |
$replacement = '{wp_abj404_logsv2}.canonical_url'; |
| 426 |
$result = preg_replace($pattern, $replacement, $sql); |
| 427 |
return is_string($result) ? $result : $sql; |
| 428 |
} |
| 429 |
|
| 430 |
/** |
| 431 |
* Large-table path: two-phase chunked pre-aggregation. |
| 432 |
* |
| 433 |
* Phase 1: chunk through logsv2 by ID range, aggregating each chunk into a |
| 434 |
* pre-agg table (no join — uses PRIMARY KEY index, fast). |
| 435 |
* |
| 436 |
* Phase 2: join the small pre-agg table with redirects (same concat/trim |
| 437 |
* normalization) and re-aggregate across chunks into the final temp table. |
| 438 |
* |
| 439 |
* @param string $tempDestTable |
| 440 |
* @param string $preAggTable |
| 441 |
* @param int $minId |
| 442 |
* @param int $maxId |
| 443 |
* @param int $chunkSize |
| 444 |
* @return array<string, mixed>|false False on chunk error |
| 445 |
*/ |
| 446 |
private function hitsTableInsertChunked( |
| 447 |
string $tempDestTable, string $preAggTable, |
| 448 |
int $minId, int $maxId, int $chunkSize |
| 449 |
) { |
| 450 |
$logsv2Table = $this->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 451 |
$redirectsTable = $this->doTableNameReplacements("{wp_abj404_redirects}"); |
| 452 |
$startTime = microtime(true); |
| 453 |
|
| 454 |
// Create the pre-aggregation scratch table. |
| 455 |
$this->queryAndGetResults("drop table if exists " . $preAggTable); |
| 456 |
$createPreAggQuery = ABJ_404_Solution_Functions::readFileContents(__DIR__ . |
| 457 |
"/sql/createLogsHitsPreAggTempTable.sql"); |
| 458 |
$createPreAggQuery = $this->doTableNameReplacements($createPreAggQuery); |
| 459 |
$this->queryAndGetResults($createPreAggQuery); |
| 460 |
|
| 461 |
// Phase 1: chunk through logsv2 by ID range. |
| 462 |
// Each chunk aggregates by canonical requested_url so URL variants |
| 463 |
// like '/foo', 'foo', and '/foo/' collapse into a single pre-agg row. |
| 464 |
// Reads logsv2.canonical_url (added 4.1.x) when populated and falls |
| 465 |
// back to CONCAT('/', TRIM(...)) on legacy NULL rows — the COALESCE |
| 466 |
// form keeps reads correct regardless of backfill state. Once |
| 467 |
// backfillLogsv2CanonicalUrl() flips |
| 468 |
// LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION (zero NULL rows |
| 469 |
// observed), this collapses to a bare canonical_url reference and |
| 470 |
// the GROUP BY can use idx_canonical_url for a loose-index scan. |
| 471 |
// The same canonical key can still appear across chunks — Phase 2 |
| 472 |
// sums them. |
| 473 |
// failed_hits = count of 404-only hits per canonical URL (rows where |
| 474 |
// dest_url is empty/NULL). Lets flagDeadDestinationRedirects() avoid |
| 475 |
// scanning logsv2 in cron — see DataAccessTrait_Maintenance::flagDeadDestinationRedirects(). |
| 476 |
$logsv2CanonicalExpr = $this->isLogsv2CanonicalUrlBackfillComplete() |
| 477 |
? "canonical_url" |
| 478 |
: "COALESCE(canonical_url, CONCAT('/', TRIM(BOTH '/' FROM requested_url)))"; |
| 479 |
for ($start = $minId; $start <= $maxId; $start += $chunkSize) { |
| 480 |
$end = $start + $chunkSize; |
| 481 |
// Marker: this chunk INSERT generated 38 of 43 May 2026 error |
| 482 |
// emails when logs_hits was missing. Explicit marker keeps the |
| 483 |
// source identifier stable even if the trait method is renamed. |
| 484 |
$chunkQuery = "/* abj404:src=DataAccessTrait_LogsHitsRebuild::hitsTableInsertChunked#phase1Chunk */ " . |
| 485 |
"INSERT INTO " . $preAggTable . |
| 486 |
" (requested_url, logsid, last_used, logshits, failed_hits) " . |
| 487 |
"SELECT " . $logsv2CanonicalExpr . ", " . |
| 488 |
" MIN(id), MAX(timestamp), COUNT(*), " . |
| 489 |
" SUM(CASE WHEN dest_url = '' OR dest_url IS NULL THEN 1 ELSE 0 END) " . |
| 490 |
"FROM " . $logsv2Table . " " . |
| 491 |
"WHERE id >= %d AND id < %d " . |
| 492 |
"GROUP BY " . $logsv2CanonicalExpr; |
| 493 |
$chunkResult = $this->queryAndGetResults($chunkQuery, array( |
| 494 |
'log_too_slow' => false, |
| 495 |
'timeout' => 10, |
| 496 |
'query_params' => array($start, $end), |
| 497 |
)); |
| 498 |
if (!empty($chunkResult['timed_out']) || !empty($chunkResult['last_error'])) { |
| 499 |
$this->logger->debugMessage(__FUNCTION__ . |
| 500 |
" Phase 1 chunk failed at id range [{$start}, {$end}); aborting."); |
| 501 |
return false; |
| 502 |
} |
| 503 |
} |
| 504 |
|
| 505 |
// Phase 2: join the small pre-agg table with redirects and |
| 506 |
// re-aggregate across chunks into the final temp table. |
| 507 |
// a.requested_url is already canonical from Phase 1. Match against |
| 508 |
// the persisted r.canonical_url column (added 4.1.10) so the JOIN |
| 509 |
// is an indexed equality lookup; COALESCE fallback covers rows |
| 510 |
// where the chunked backfill hasn't reached yet. Final GROUP BY |
| 511 |
// collapses any remaining duplicate canonical rows that originated |
| 512 |
// from different ID-range chunks. |
| 513 |
$phase2Query = "/* abj404:src=DataAccessTrait_LogsHitsRebuild::hitsTableInsertChunked#phase2Aggregate */ " . |
| 514 |
"INSERT INTO " . $tempDestTable . |
| 515 |
" (requested_url, logsid, last_used, logshits, failed_hits) " . |
| 516 |
"SELECT a.requested_url, MIN(a.logsid), MAX(a.last_used), SUM(a.logshits), SUM(a.failed_hits) " . |
| 517 |
"FROM " . $preAggTable . " a " . |
| 518 |
"INNER JOIN " . $redirectsTable . " r " . |
| 519 |
"ON a.requested_url = COALESCE(r.canonical_url, CONCAT('/', TRIM(BOTH '/' FROM r.url))) " . |
| 520 |
"GROUP BY a.requested_url"; |
| 521 |
$results = $this->queryAndGetResults($phase2Query, array('log_too_slow' => false, 'timeout' => 60)); |
| 522 |
|
| 523 |
// Attach total elapsed time so the caller can store it in the table comment. |
| 524 |
$results['elapsed_time'] = round(microtime(true) - $startTime, 3); |
| 525 |
|
| 526 |
// Clean up pre-agg table (also done in the finally block as a safety net). |
| 527 |
$this->queryAndGetResults("drop table if exists " . $preAggTable); |
| 528 |
|
| 529 |
return $results; |
| 530 |
} |
| 531 |
} |
| 532 |
|
