404-solution / includes / feedback / MysqlServerStateProbe.php

MysqlServerStateProbe.php in 404 Solution trunk, at includes/feedback/MysqlServerStateProbe.php

308 lines 12.7 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	require_once __DIR__ . '/FeedbackTransportLog.php';
8
9	/**
10	* Read-only probes of the MySQL/MariaDB server's own state for the feedback
11	* payload's `environment_extras` field.
12	*
13	* One subject: the database SERVER. What it is configured to allow (global and
14	* session variables), what it is actually doing (status counters), and how
15	* saturated it is (processlist row count). No plugin table name appears in
16	* this class -- reads about this plugin's own schema live in
17	* ABJ_404_Solution_PluginSchemaMetadataProbe, and reads about the plugin's
18	* rollup freshness live in ABJ_404_Solution_RollupFreshnessProbe.
19	*
20	* Every probe throws on a genuine failure (no $wpdb, non-array response)
21	* rather than returning an empty map, so the caller's recordProbe() wrapper
22	* writes a `<probe>_error` marker key. "Probe failed" and "the server
23	* reported nothing" must stay distinguishable in the payload: an empty map
24	* that silently means "broken" is the exact defect that left
25	* `view_build_state` looking healthy-but-empty for seven weeks
26	* (t_260801_071502_922).
27	*
28	* The DAO-bypass markers here are specifically scoped to read-only @@GLOBAL /
29	* @@SESSION / status probes; no plugin-owned table is reachable from any
30	* statement in this class, so DatabaseCore's repair-and-retry recovery does
31	* not apply (see docs/adr/dataaccess-refactor.md).
32	*/
33	class ABJ_404_Solution_MysqlServerStateProbe {
34
35	/**
36	* Fixed set of MySQL global variables relevant to view-build / temp-table
37	* JOIN performance on Bruno-class hosts. One SHOW GLOBAL VARIABLES query,
38	* parameterized name list, suppressed errors so a perms-denied response
39	* degrades to an empty map rather than a payload error.
40	*
41	* @return array<string, mixed>
42	*/
43	public function collectMysqlGlobals(): array {
44	return $this->coerceNumericScalars($this->fetchVariableRows('SHOW GLOBAL VARIABLES', array(
45	'innodb_buffer_pool_size',
46	'innodb_log_file_size',
47	'innodb_flush_method',
48	'innodb_file_per_table',
49	'innodb_lock_wait_timeout',
50	'tmp_table_size',
51	'max_heap_table_size',
52	'key_buffer_size',
53	'max_allowed_packet',
54	'sort_buffer_size',
55	'join_buffer_size',
56	'max_connections',
57	'thread_cache_size',
58	'table_open_cache',
59	'wait_timeout',
60	'interactive_timeout',
61	'character_set_server',
62	'collation_server',
63	'optimizer_switch',
64	'sql_mode',
65	'long_query_time',
66	'slow_query_log',
67	'open_files_limit',
68	)));
69	}
70
71	/**
72	* Live SHOW SESSION VARIABLES read for the operational and DDL-safety
73	* variables that can degrade or break the redirects-hits rollup rebuild
74	* (LogsHitsRollupService::createRedirectsForViewHitsTable()) on this
75	* connection. Session scope, not global scope: some hosts override these
76	* per-connection (a pooler, a session_variables config, a wp-config.php
77	* SET SESSION shim), so the global values collectMysqlGlobals() reports
78	* can differ from what the rollup rebuild actually ran under.
79	*
80	* Formerly a get_option() read of a value the deleted staged view-build
81	* subsystem used to persist (abj404_view_build_session_env_probe); commit
82	* 73a55a70 removed that writer without updating this read, so the option
83	* stayed forever empty and `mysql_session_probe` shipped `[]` on every
84	* payload. Replaced with a live probe so there is no persisted name left
85	* to drift out of sync with its writer again.
86	*
87	* @return array<string, mixed>
88	*/
89	public function collectMysqlSessionVariables(): array {
90	return $this->coerceNumericScalars($this->fetchVariableRows('SHOW SESSION VARIABLES', array(
91	'innodb_lock_wait_timeout',
92	'tmp_table_size',
93	'max_heap_table_size',
94	'slow_query_log',
95	'long_query_time',
96	'innodb_buffer_pool_size',
97	'wait_timeout',
98	'interactive_timeout',
99	'innodb_flush_method',
100	'character_set_server',
101	'collation_server',
102	'sql_require_primary_key',
103	'innodb_file_per_table',
104	'thread_stack',
105	'open_files_limit',
106	'innodb_online_alter_log_max_size',
107	)));
108	}
109
110	/**
111	* SHOW GLOBAL STATUS counterpart to collectMysqlGlobals(). The variables
112	* tell us what the server is CONFIGURED to allow; the status counters tell
113	* us what is actually HAPPENING. Counters that have ticked up since boot
114	* are the strongest proximate-cause signal: lock-wait pile-ups, tmp-disk
115	* spills, aborted connects, slow queries.
116	*
117	* Non-numeric status values are dropped rather than emitted as strings:
118	* every allowlisted name below is a counter, so a non-numeric value means
119	* the server returned something unexpected for that row.
120	*
121	* @return array<string, int>
122	*/
123	public function probeMysqlStatus(): array {
124	$raw = $this->fetchVariableRows('SHOW GLOBAL STATUS', array(
125	'Innodb_buffer_pool_pages_dirty',
126	'Innodb_buffer_pool_pages_total',
127	'Innodb_row_lock_waits',
128	'Innodb_row_lock_time_avg',
129	'Innodb_deadlocks',
130	'Threads_running',
131	'Threads_connected',
132	'Aborted_connects',
133	'Aborted_clients',
134	'Created_tmp_disk_tables',
135	'Created_tmp_tables',
136	'Slow_queries',
137	'Table_locks_waited',
138	'Open_tables',
139	'Opened_tables',
140	'Uptime',
141	));
142	$out = array();
143	foreach ($raw as $name => $value) {
144	if (is_numeric($value)) {
145	$out[$name] = (int)$value;
146	}
147	}
148	return $out;
149	}
150
151	/**
152	* Row count from SHOW PROCESSLIST. Cheap on a shared host (returns the
153	* current request's view of connection saturation) and a strong leading
154	* indicator for "the rollup rebuild is waiting because there are 200 other
155	* queries in flight". Only the row count is emitted; user/host/info
156	* columns are dropped to avoid PII leakage from other tenants on the same
157	* MySQL instance.
158	*
159	* Throws when the probe genuinely cannot complete (no $wpdb, query failed)
160	* so the caller records a marker rather than a misleading zero.
161	*
162	* @return int
163	*/
164	public function probeActiveConnectionCount(): int {
165	global $wpdb;
166	if (!isset($wpdb) \|\| !is_object($wpdb) \|\| !method_exists($wpdb, 'get_results')) {
167	throw new \RuntimeException('wpdb unavailable');
168	}
169	$prevSuppress = method_exists($wpdb, 'suppress_errors') ? $wpdb->suppress_errors(true) : false;
170	$rows = null;
171	try {
172	// DAO-bypass-approved: read-only probe of @@PROCESSLIST; no plugin tables involved.
173	$rows = $wpdb->get_results('SHOW PROCESSLIST', ARRAY_A);
174	} catch (\Throwable $e) {
175	// allow-silent-catch: probe is best-effort; rethrow after restoring suppress so the outer tryInt records null
176	ABJ_404_Solution_FeedbackTransportLog::log('warn', 'probeActiveConnectionCount failed: ' . $e->getMessage());
177	$rows = null;
178	}
179	if (method_exists($wpdb, 'suppress_errors')) {
180	$wpdb->suppress_errors($prevSuppress);
181	}
182	if (!is_array($rows)) {
183	throw new \RuntimeException('processlist probe failed');
184	}
185	return count($rows);
186	}
187
188	/**
189	* The connection's own server version string, vendor suffix included
190	* ("8.0.45-azure", "10.6.18-MariaDB-log"), or '' when the connection
191	* cannot report one.
192	*
193	* Read off the live connection rather than through `SELECT VERSION()`:
194	* mysqli already holds this string, so the probe costs no round trip and
195	* cannot fail on a read-only replica or a saturated server. The SUFFIX is
196	* what makes it worth collecting here -- `db_version` in the typed columns
197	* keeps the number, and the vendor tag on the end is a hosting-platform
198	* marker (see FeedbackEnvironmentExtras_PlatformFingerprint's
199	* INFRASTRUCTURE_HOST_MARKERS).
200	*
201	* Returns '' rather than throwing: unlike the probes above this has no
202	* `<probe>_error` marker of its own, and a host whose driver hides the
203	* version is not a failure, it is one fewer marker.
204	*/
205	public function serverVersionString(): string {
206	global $wpdb;
207	if (!isset($wpdb) \|\| !is_object($wpdb)) {
208	return '';
209	}
210	if (isset($wpdb->db_server_info) && is_scalar($wpdb->db_server_info)) {
211	return (string)$wpdb->db_server_info;
212	}
213	if (!method_exists($wpdb, 'db_server_info')) {
214	return '';
215	}
216	try {
217	$info = $wpdb->db_server_info();
218	return is_scalar($info) ? (string)$info : '';
219	} catch (\Throwable $e) {
220	ABJ_404_Solution_FeedbackTransportLog::log(
221	'warn', 'serverVersionString probe failed: ' . $e->getMessage());
222	return '';
223	}
224	}
225
226	/**
227	* Run one `SHOW <scope> VARIABLES\|STATUS WHERE Variable_name IN (...)`
228	* statement and fold the returned rows into a lowercased name => raw
229	* string-value map.
230	*
231	* Shared by all three name/value probes above because MySQL's SHOW
232	* VARIABLES and SHOW STATUS wire shape is identical (a Variable_name
233	* column and a Value column, whose letter case varies by driver) and the
234	* suppress-errors / non-array-response handling has to be identical too.
235	* Value COERCION is deliberately not done here: the variables probes keep
236	* strings and floats, while the status probe wants ints only, so each
237	* caller applies its own policy to the raw map.
238	*
239	* @param string $statement Bare SHOW statement; also used verbatim in the
240	* failure messages so each probe's thrown text
241	* still names the statement that failed.
242	* @param array<int, string> $names Allowlisted variable names to bind.
243	* @return array<string, string>
244	*/
245	private function fetchVariableRows(string $statement, array $names): array {
246	global $wpdb;
247	if (!isset($wpdb) \|\| !is_object($wpdb) \|\| !method_exists($wpdb, 'get_results')) {
248	throw new \RuntimeException('wpdb unavailable for ' . $statement . ' probe');
249	}
250	$placeholders = implode(',', array_fill(0, count($names), '%s'));
251	$prevSuppress = method_exists($wpdb, 'suppress_errors') ? $wpdb->suppress_errors(true) : false;
252	try {
253	$prepared = $statement;
254	if (method_exists($wpdb, 'prepare')) {
255	// DAO-bypass-approved: server variable/status name list placeholder bind; no plugin-table writes possible.
256	$prepared = $wpdb->prepare($statement . " WHERE Variable_name IN ($placeholders)", $names);
257	}
258	// DAO-bypass-approved: read-only probe of server variables/status; no plugin tables involved.
259	$rows = $wpdb->get_results($prepared, ARRAY_A);
260	} finally {
261	if (method_exists($wpdb, 'suppress_errors')) {
262	$wpdb->suppress_errors($prevSuppress);
263	}
264	}
265
266	if (!is_array($rows)) {
267	throw new \RuntimeException($statement . ' returned non-array');
268	}
269	$out = array();
270	foreach ($rows as $row) {
271	if (!is_array($row)) { continue; }
272	$name = '';
273	$value = '';
274	foreach ($row as $k => $v) {
275	$klow = strtolower((string)$k);
276	if ($klow === 'variable_name' && is_scalar($v)) { $name = strtolower((string)$v); }
277	if ($klow === 'value' && is_scalar($v)) { $value = (string)$v; }
278	}
279	if ($name === '') { continue; }
280	$out[$name] = $value;
281	}
282	return $out;
283	}
284
285	/**
286	* Coerce numeric-looking variable values to int/float so the server-side
287	* JSON sort is meaningful (otherwise 9 sorts after 100 lexically) while
288	* leaving genuinely textual values (sql_mode, optimizer_switch,
289	* collation_server) as strings.
290	*
291	* @param array<string, string> $raw
292	* @return array<string, mixed>
293	*/
294	private function coerceNumericScalars(array $raw): array {
295	$out = array();
296	foreach ($raw as $name => $value) {
297	if (is_numeric($value) && strpos($value, '.') === false) {
298	$out[$name] = (int)$value;
299	} elseif (is_numeric($value)) {
300	$out[$name] = (float)$value;
301	} else {
302	$out[$name] = $value;
303	}
304	}
305	return $out;
306	}
307	}
308