| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
require_once __DIR__ . '/FeedbackTransportLog.php'; |
| 8 |
|
| 9 |
/** |
| 10 |
* Read-only probes of THIS PLUGIN's own storage shape for the feedback |
| 11 |
* payload's `environment_extras` field: how large each plugin table is, how |
| 12 |
* healthy its indexes are, and what collation its JOIN-hot URL columns carry. |
| 13 |
* |
| 14 |
* One subject: the plugin's schema as the server currently reports it, read |
| 15 |
* through `information_schema` and `SHOW INDEX`. Reads about the SERVER's own |
| 16 |
* configuration live in ABJ_404_Solution_MysqlServerStateProbe; reads about |
| 17 |
* the rollup's freshness live in ABJ_404_Solution_RollupFreshnessProbe. |
| 18 |
* |
| 19 |
* Every probe here iterates a candidate table list and isolates each table in |
| 20 |
* its own try/catch, so a missing table (rebuild race, repair pending, an |
| 21 |
* install that never got that table) degrades to a missing map entry rather |
| 22 |
* than blanking the whole probe. A probe throws only when EVERY attempt |
| 23 |
* failed, which is the "we learned nothing" case the caller's recordProbe() |
| 24 |
* wrapper should mark as `<probe>_error`. |
| 25 |
* |
| 26 |
* The DAO-bypass markers here are specifically scoped to read-only metadata |
| 27 |
* probes: these statements read table/column/index METADATA, never plugin row |
| 28 |
* data, so DatabaseCore's repair-and-retry recovery does not apply (see |
| 29 |
* docs/adr/dataaccess-refactor.md). |
| 30 |
*/ |
| 31 |
class ABJ_404_Solution_PluginSchemaMetadataProbe { |
| 32 |
|
| 33 |
/** |
| 34 |
* Size of plugin-owned tables beyond logsv2 (which has its own typed |
| 35 |
* column). The redirects-tab perf bug is bounded by the |
| 36 |
* redirects/logs_hits volume, not logsv2, so shipping both lets the |
| 37 |
* report rank reports by the right axis. |
| 38 |
* |
| 39 |
* Per-table shape: {data_length: int, index_length: int, data_free: int, |
| 40 |
* bytes: int}. `data_free` is the fragmentation indicator (bytes |
| 41 |
* allocated to the file but not in use); a fragmentation ratio of |
| 42 |
* data_free / (data_length + index_length) over ~0.3 explains the |
| 43 |
* "tables are 200 MB but only 50 MB of data" long-tail slowness. |
| 44 |
* `bytes` is the legacy combined data+index size kept for backward |
| 45 |
* compatibility with consumers that pre-dated the data_free split. |
| 46 |
* |
| 47 |
* @return array<string, array<string, int>> |
| 48 |
*/ |
| 49 |
public function collectPluginTableSizes(): array { |
| 50 |
global $wpdb; |
| 51 |
if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'get_results') || !method_exists($wpdb, 'get_row')) { |
| 52 |
throw new \RuntimeException('wpdb unavailable for plugin_tables_bytes probe'); |
| 53 |
} |
| 54 |
$prefix = (isset($wpdb->prefix) && is_string($wpdb->prefix)) ? $wpdb->prefix : 'wp_'; |
| 55 |
$candidates = array( |
| 56 |
'redirects' => $prefix . 'abj404_redirects', |
| 57 |
'logs_hits' => $prefix . 'abj404_logs_hits', |
| 58 |
'logs_hits_preagg' => $prefix . 'abj404_logs_hits_preagg', |
| 59 |
'permalink_cache' => $prefix . 'abj404_permalink_cache', |
| 60 |
'spelling_cache' => $prefix . 'abj404_spelling_cache', |
| 61 |
'lookup' => $prefix . 'abj404_lookup', |
| 62 |
); |
| 63 |
$prevSuppress = method_exists($wpdb, 'suppress_errors') ? $wpdb->suppress_errors(true) : false; |
| 64 |
$out = array(); |
| 65 |
$errors = 0; |
| 66 |
$attempted = 0; |
| 67 |
foreach ($candidates as $key => $table) { |
| 68 |
$attempted++; |
| 69 |
try { |
| 70 |
if (!method_exists($wpdb, 'prepare')) { continue; } |
| 71 |
// DAO-bypass-approved: information_schema metadata probe placeholder bind; no plugin-table writes. |
| 72 |
$prepared = $wpdb->prepare( |
| 73 |
'SELECT data_length, index_length, data_free ' |
| 74 |
. 'FROM information_schema.TABLES ' |
| 75 |
. 'WHERE table_schema = DATABASE() AND table_name = %s', |
| 76 |
$table |
| 77 |
); |
| 78 |
if ($prepared === null) { continue; } |
| 79 |
// DAO-bypass-approved: information_schema probe; no plugin tables touched, no error class repair would apply. |
| 80 |
$row = $wpdb->get_row($prepared, ARRAY_A); |
| 81 |
if (!is_array($row)) { continue; } |
| 82 |
$dl = 0; $il = 0; $df = 0; |
| 83 |
foreach ($row as $col => $val) { |
| 84 |
if (!is_scalar($val) || !is_numeric($val)) { continue; } |
| 85 |
$clow = strtolower((string)$col); |
| 86 |
if ($clow === 'data_length') { $dl = (int)$val; } |
| 87 |
if ($clow === 'index_length') { $il = (int)$val; } |
| 88 |
if ($clow === 'data_free') { $df = (int)$val; } |
| 89 |
} |
| 90 |
$out[$key] = array( |
| 91 |
'data_length' => $dl, |
| 92 |
'index_length' => $il, |
| 93 |
'data_free' => $df, |
| 94 |
'bytes' => $dl + $il, |
| 95 |
); |
| 96 |
} catch (\Throwable $e) { |
| 97 |
// allow-silent-catch: per-table probe is best-effort; a missing-table or permissions error must not abort the whole map. Aggregated failure is rethrown after the loop when ALL attempts failed (see $errors check below) so the recordProbe wrapper can write the marker key. |
| 98 |
ABJ_404_Solution_FeedbackTransportLog::log('warn', 'collectPluginTableSizes probe failed for ' . $table . ': ' . $e->getMessage()); |
| 99 |
$errors++; |
| 100 |
} |
| 101 |
} |
| 102 |
if (method_exists($wpdb, 'suppress_errors')) { |
| 103 |
$wpdb->suppress_errors($prevSuppress); |
| 104 |
} |
| 105 |
if ($errors === $attempted && empty($out)) { |
| 106 |
throw new \RuntimeException('plugin_tables_bytes: all tables failed SQL probe'); |
| 107 |
} |
| 108 |
return $out; |
| 109 |
} |
| 110 |
|
| 111 |
/** |
| 112 |
* Per-index cardinality for the canonical indexes on the JOIN-hot |
| 113 |
* plugin tables. Output shape: |
| 114 |
* { redirects: {idx_url_disabled_status: int, idx_canonical_url: int, ...}, |
| 115 |
* logs_hits: {requested_url: int, ...}, |
| 116 |
* logs_hits_preagg: {...} } |
| 117 |
* |
| 118 |
* Each table is probed in its own SHOW INDEX statement, isolated in |
| 119 |
* try/catch so a missing table (rebuild race, repair pending) does |
| 120 |
* not blank the whole map. Only the Cardinality value is captured; |
| 121 |
* the rest of the SHOW INDEX columns are not emitted. |
| 122 |
* |
| 123 |
* @return array<string, array<string, int>> |
| 124 |
*/ |
| 125 |
public function probeIndexCardinality(): array { |
| 126 |
global $wpdb; |
| 127 |
if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'get_results')) { |
| 128 |
throw new \RuntimeException('wpdb unavailable for index_cardinality probe'); |
| 129 |
} |
| 130 |
$prefix = (isset($wpdb->prefix) && is_string($wpdb->prefix)) ? $wpdb->prefix : 'wp_'; |
| 131 |
$candidates = array( |
| 132 |
'redirects' => $prefix . 'abj404_redirects', |
| 133 |
'logs_hits' => $prefix . 'abj404_logs_hits', |
| 134 |
'logs_hits_preagg' => $prefix . 'abj404_logs_hits_preagg', |
| 135 |
'logsv2' => $prefix . 'abj404_logsv2', |
| 136 |
); |
| 137 |
$prevSuppress = method_exists($wpdb, 'suppress_errors') ? $wpdb->suppress_errors(true) : false; |
| 138 |
$out = array(); |
| 139 |
$errors = 0; |
| 140 |
$attempted = 0; |
| 141 |
foreach ($candidates as $key => $table) { |
| 142 |
$attempted++; |
| 143 |
try { |
| 144 |
$prepared = 'SHOW INDEX FROM `' . $table . '`'; |
| 145 |
if (method_exists($wpdb, 'prepare')) { |
| 146 |
// DAO-bypass-approved: SHOW INDEX metadata probe placeholder bind; no plugin-table writes possible. |
| 147 |
$prepared = $wpdb->prepare($prepared); |
| 148 |
} |
| 149 |
if ($prepared === null || $prepared === '') { |
| 150 |
continue; |
| 151 |
} |
| 152 |
// DAO-bypass-approved: SHOW INDEX is read-only metadata; no plugin-table writes possible. |
| 153 |
$rows = $wpdb->get_results($prepared, ARRAY_A); |
| 154 |
if (!is_array($rows)) { |
| 155 |
continue; |
| 156 |
} |
| 157 |
$byIndex = array(); |
| 158 |
foreach ($rows as $row) { |
| 159 |
if (!is_array($row)) { continue; } |
| 160 |
$idxName = ''; |
| 161 |
$card = null; |
| 162 |
foreach ($row as $col => $val) { |
| 163 |
$clow = strtolower((string)$col); |
| 164 |
if ($clow === 'key_name' && is_scalar($val)) { $idxName = (string)$val; } |
| 165 |
if ($clow === 'cardinality' && is_scalar($val) && is_numeric($val)) { $card = (int)$val; } |
| 166 |
} |
| 167 |
if ($idxName === '' || $card === null) { continue; } |
| 168 |
if (!isset($byIndex[$idxName]) || $card > $byIndex[$idxName]) { |
| 169 |
$byIndex[$idxName] = $card; |
| 170 |
} |
| 171 |
} |
| 172 |
if (!empty($byIndex)) { |
| 173 |
$out[$key] = $byIndex; |
| 174 |
} |
| 175 |
} catch (\Throwable $e) { |
| 176 |
// allow-silent-catch: per-table probe is best-effort; a missing-table or permissions error must not abort the whole map. Aggregated failure is rethrown after the loop when ALL attempts failed (see $errors check below) so the recordProbe wrapper can write the marker key. |
| 177 |
ABJ_404_Solution_FeedbackTransportLog::log('warn', 'probeIndexCardinality failed for ' . $table . ': ' . $e->getMessage()); |
| 178 |
$errors++; |
| 179 |
} |
| 180 |
} |
| 181 |
if (method_exists($wpdb, 'suppress_errors')) { |
| 182 |
$wpdb->suppress_errors($prevSuppress); |
| 183 |
} |
| 184 |
if ($errors === $attempted && empty($out)) { |
| 185 |
throw new \RuntimeException('index_cardinality: all tables failed SHOW INDEX probe'); |
| 186 |
} |
| 187 |
return $out; |
| 188 |
} |
| 189 |
|
| 190 |
/** |
| 191 |
* DB-level + per-column collation for the JOIN-hot URL columns on |
| 192 |
* `abj404_redirects` and `abj404_logs_hits`. Collation drift between |
| 193 |
* the two columns disables the index seek silently: MySQL falls back |
| 194 |
* to a full-scan ON the un-joined column. Capturing both lets the |
| 195 |
* server side classify "fast on staging, slow on prod" reports by |
| 196 |
* the cause that is invisible from the SHOW CREATE TABLE output. |
| 197 |
* |
| 198 |
* Shape: |
| 199 |
* { db_charset: string, db_collate: string, |
| 200 |
* columns: { '{prefix}abj404_redirects.url': string, |
| 201 |
* '{prefix}abj404_redirects.canonical_url': string, |
| 202 |
* '{prefix}abj404_logs_hits.requested_url': string } } |
| 203 |
* |
| 204 |
* @return array<string, mixed> |
| 205 |
*/ |
| 206 |
public function probeDbCollation(): array { |
| 207 |
$out = array( |
| 208 |
'db_charset' => defined('DB_CHARSET') && is_string(DB_CHARSET) ? DB_CHARSET : '', |
| 209 |
'db_collate' => defined('DB_COLLATE') && is_string(DB_COLLATE) ? DB_COLLATE : '', |
| 210 |
'columns' => array(), |
| 211 |
); |
| 212 |
global $wpdb; |
| 213 |
if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'get_results') || !method_exists($wpdb, 'get_var')) { |
| 214 |
throw new \RuntimeException('wpdb unavailable for db_collation probe'); |
| 215 |
} |
| 216 |
$prefix = (isset($wpdb->prefix) && is_string($wpdb->prefix)) ? $wpdb->prefix : 'wp_'; |
| 217 |
$targets = array( |
| 218 |
$prefix . 'abj404_redirects' => array('url', 'canonical_url'), |
| 219 |
$prefix . 'abj404_logs_hits' => array('requested_url'), |
| 220 |
$prefix . 'abj404_logsv2' => array('url'), |
| 221 |
); |
| 222 |
$prevSuppress = method_exists($wpdb, 'suppress_errors') ? $wpdb->suppress_errors(true) : false; |
| 223 |
$errors = 0; |
| 224 |
$attempted = 0; |
| 225 |
foreach ($targets as $table => $cols) { |
| 226 |
foreach ($cols as $col) { |
| 227 |
$attempted++; |
| 228 |
try { |
| 229 |
if (!method_exists($wpdb, 'prepare')) { continue; } |
| 230 |
// DAO-bypass-approved: information_schema collation probe placeholder bind; no plugin-table writes. |
| 231 |
$prepared = $wpdb->prepare( |
| 232 |
'SELECT COLLATION_NAME ' |
| 233 |
. 'FROM information_schema.COLUMNS ' |
| 234 |
. 'WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s', |
| 235 |
$table, |
| 236 |
$col |
| 237 |
); |
| 238 |
if ($prepared === null) { continue; } |
| 239 |
// DAO-bypass-approved: information_schema metadata probe; no plugin-table writes. |
| 240 |
$v = $wpdb->get_var($prepared); |
| 241 |
if (is_scalar($v) && (string)$v !== '') { |
| 242 |
$out['columns'][$table . '.' . $col] = (string)$v; |
| 243 |
} |
| 244 |
} catch (\Throwable $e) { |
| 245 |
// allow-silent-catch: per-column probe is best-effort; missing-table / permissions errors must not abort the whole map. Aggregated failure is rethrown after the loop when ALL attempts failed (see $errors check below) so the recordProbe wrapper can write the marker key. |
| 246 |
ABJ_404_Solution_FeedbackTransportLog::log('warn', 'probeDbCollation probe failed for ' . $table . '.' . $col . ': ' . $e->getMessage()); |
| 247 |
$errors++; |
| 248 |
} |
| 249 |
} |
| 250 |
} |
| 251 |
if (method_exists($wpdb, 'suppress_errors')) { |
| 252 |
$wpdb->suppress_errors($prevSuppress); |
| 253 |
} |
| 254 |
if ($errors === $attempted && empty($out['columns'])) { |
| 255 |
throw new \RuntimeException('db_collation: all per-column SQL probes failed'); |
| 256 |
} |
| 257 |
return $out; |
| 258 |
} |
| 259 |
} |
| 260 |
|
