404-solution / includes / DatabaseQueryTimeoutManager.php

DatabaseQueryTimeoutManager.php in 404 Solution 4.2.0, at includes/DatabaseQueryTimeoutManager.php

330 lines 14.0 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Engine-aware per-query timeout helpers.
9	*
10	* Centralizes the SQL-level timeout mechanism so every query routed through
11	* queryAndGetResults() inherits a fail-fast deadline before the host's silent
12	* connection-drop kicks in:
13	*
14	* - MySQL 5.7.8+ pure SELECT: MAX_EXECUTION_TIME(ms) optimizer hint.
15	* - MySQL non-SELECT: no SQL-level mechanism (left unchanged).
16	* - MariaDB 10.1+ any DML/DDL: SET STATEMENT max_statement_time=N FOR ...
17	*
18	* Also provides query-shape probes used by the routing layer in
19	* queryAndGetResults() to decide between $wpdb->get_results() vs
20	* $wpdb->query() based on whether a wrapped query produces rows.
21	*
22	* Composed into ABJ_404_Solution_DataAccess. No state of its own; uses the
23	* global $wpdb to detect engine version. Pure functions otherwise.
24	*/
25	class ABJ_404_Solution_DatabaseQueryTimeoutManager {
26
27	/** @var ABJ_404_Solution_DatabaseCore */
28	private $core;
29
30	/** @var ABJ_404_Solution_Logging */
31	private $logger;
32
33	/**
34	* @param ABJ_404_Solution_DatabaseCore $core
35	* @param ABJ_404_Solution_Logging $logger
36	*/
37	public function __construct(ABJ_404_Solution_DatabaseCore $core, $logger) {
38	$this->core = $core;
39	$this->logger = $logger;
40	}
41
42	/**
43	* Forward DatabaseCore infrastructure calls that remain owned by the core.
44	*
45	* @param string $name
46	* @param array<int, mixed> $arguments
47	* @return mixed
48	*/
49	public function __call(string $name, array $arguments) {
50	return $this->core->$name(...$arguments);
51	}
52
53	/**
54	* @param string $query
55	* @return bool
56	*/
57	public function queryStartsWithSelect(string $query): bool {
58	// SQL loaded from .sql files is wrapped in leading comments.
59	// Treat "/* ... */ SELECT ..." as a SELECT query for timeout purposes.
60	return preg_match('/^\s(?:\/\[\s\S]?\\/\s)SELECT\s/i', $query) === 1;
61	}
62
63	/**
64	* Returns true if the query produces a result set (rows), so it should
65	* be sent through $wpdb->get_results(). Returns false for INSERT, UPDATE,
66	* DELETE, REPLACE, DDL, SET, etc. Those should go through $wpdb->query().
67	*
68	* Sees past leading SQL comments and any `SET STATEMENT max_statement_time=N FOR `
69	* timeout wrapper. The wrapper is critical because applyQueryTimeout() prepends
70	* it on MariaDB, which would otherwise mask the underlying statement type.
71	*
72	* Misclassification triggered the 4.1.7 spell-check `mysqli_num_fields(true)`
73	* TypeError on PHP 8.1+ MariaDB sites. See DataAccessNonSelectRoutingTest.
74	*
75	* @param string $query
76	* @return bool
77	*/
78	public function queryProducesResultRows(string $query): bool {
79	$stripped = (string)preg_replace('/^\s(?:\/\[\s\S]?\\/\s*)+/', '', $query);
80	$stripped = (string)preg_replace(
81	'/^\sSET\s+STATEMENT\s+\w+\s=\s*\d+\s+FOR\s+/i',
82	'',
83	$stripped,
84	1
85	);
86	// Strip nested leading comments inside the SET STATEMENT wrapper too.
87	$stripped = (string)preg_replace('/^\s(?:\/\[\s\S]?\\/\s*)+/', '', $stripped);
88	return preg_match('/^\s*(SELECT\|SHOW\|EXPLAIN\|DESCRIBE\|DESC)\s/i', $stripped) === 1;
89	}
90
91	/**
92	* Apply a DB-level timeout to any query type.
93	*
94	* Dispatches to the appropriate engine-specific mechanism:
95	* - Pure SELECT: MySQL optimizer hint or MariaDB SET STATEMENT
96	* - INSERT...SELECT (or any non-leading SELECT): MariaDB SET STATEMENT
97	* or MySQL hint injected into the embedded SELECT
98	* - Other DML/DDL: MariaDB SET STATEMENT (MySQL has no mechanism for
99	* non-SELECT timeouts; these queries are typically fast)
100	*
101	* Skips queries that already carry a timeout hint to prevent double-wrapping
102	* (e.g. callers that used to apply timeouts manually before this was centralized).
103	*
104	* @param string $query Any SQL query
105	* @param int $timeoutSeconds Maximum execution time in seconds
106	* @return string The query with timeout applied (or unchanged if no mechanism)
107	*/
108	public function applyQueryTimeout(string $query, int $timeoutSeconds): string {
109	// Skip if a timeout hint is already present (prevents double-wrapping).
110	if (preg_match('/MAX_EXECUTION_TIME\|max_statement_time/i', $query)) {
111	return $query;
112	}
113
114	if ($this->queryStartsWithSelect($query)) {
115	return $this->applySelectTimeout($query, $timeoutSeconds);
116	}
117	if (preg_match('/SELECT\s/i', $query)) {
118	// INSERT...SELECT, CREATE TABLE...SELECT, etc.
119	return $this->applyNonLeadingSelectTimeout($query, $timeoutSeconds);
120	}
121	// Plain INSERT, UPDATE, DELETE, DDL: only MariaDB has a timeout mechanism.
122	return $this->applyStatementTimeout($query, $timeoutSeconds);
123	}
124
125	/**
126	* Detect the DB engine. Returns true for MariaDB, false for MySQL/unknown.
127	* @return bool
128	*/
129	public function isMariaDB(): bool {
130	global $wpdb;
131	if (!isset($wpdb) \|\| !is_object($wpdb)) {
132	return false;
133	}
134	try {
135	if (isset($wpdb->dbh) && function_exists('mysqli_get_server_info') && $wpdb->dbh instanceof \mysqli) {
136	$dbVersion = mysqli_get_server_info($wpdb->dbh);
137	} else {
138	/** @var wpdb $wpdb */
139	$dbVersion = $wpdb->db_version() ?? '';
140	}
141	} catch (\Throwable $e) { // allow-silent-catch: test doubles / early-boot wpdb may lack db_version(); defaulting to MySQL (no MariaDB timeout syntax) is safe
142	$dbVersion = '';
143	}
144	return stripos($dbVersion, 'mariadb') !== false;
145	}
146
147	/**
148	* Apply timeout to a pure SELECT query.
149	*
150	* MySQL 5.7.8+: MAX_EXECUTION_TIME(ms) optimizer hint.
151	* MariaDB 10.1+: SET STATEMENT max_statement_time=N FOR ...
152	*
153	* @param string $query A SELECT query
154	* @param int $timeoutSeconds Maximum execution time in seconds
155	* @return string The query with timeout hint applied
156	*/
157	public function applySelectTimeout(string $query, int $timeoutSeconds): string {
158	if ($this->isMariaDB() && !ABJ_404_Solution_DatabaseRuntimeState::isSetStatementWrapperUnsupported()) {
159	return "SET STATEMENT max_statement_time=" . $timeoutSeconds . " FOR " . $query;
160	}
161	// MySQL hint also works for the MariaDB-with-disabled-wrapper case:
162	// MariaDB silently ignores unrecognized optimizer hints (parses as a
163	// comment), so the SELECT runs without a per-statement deadline.
164	$timeoutMs = $timeoutSeconds * 1000;
165	$timedQuery = preg_replace(
166	'/^(\s(?:\/\[\s\S]?\\/\s)SELECT\s)/i',
167	'$1/+ MAX_EXECUTION_TIME(' . $timeoutMs . ') / ',
168	$query
169	);
170	return ($timedQuery !== null) ? $timedQuery : $query;
171	}
172
173	/**
174	* Apply timeout to a query containing a non-leading SELECT (INSERT...SELECT, etc.).
175	*
176	* MariaDB 10.1+: SET STATEMENT max_statement_time=N FOR ... (wraps entire statement).
177	* MySQL 5.7.8+: MAX_EXECUTION_TIME(ms) hint injected into the first SELECT keyword.
178	*
179	* @param string $query An INSERT...SELECT or similar query
180	* @param int $timeoutSeconds Maximum execution time in seconds
181	* @return string The query with timeout applied
182	*/
183	public function applyNonLeadingSelectTimeout(string $query, int $timeoutSeconds): string {
184	if ($this->isMariaDB() && !ABJ_404_Solution_DatabaseRuntimeState::isSetStatementWrapperUnsupported()) {
185	return "SET STATEMENT max_statement_time=" . $timeoutSeconds . " FOR " . $query;
186	}
187	$timeoutMs = $timeoutSeconds * 1000;
188	$timedQuery = preg_replace(
189	'/(SELECT\s)/i',
190	'SELECT /+ MAX_EXECUTION_TIME(' . $timeoutMs . ') / ',
191	$query,
192	1
193	);
194	return ($timedQuery !== null) ? $timedQuery : $query;
195	}
196
197	/**
198	* Apply timeout to a non-SELECT statement (INSERT, UPDATE, DELETE, DDL).
199	*
200	* MariaDB 10.1+: SET STATEMENT max_statement_time=N FOR ... works on all DML.
201	* MySQL: has no SQL-level timeout mechanism for non-SELECT queries.
202	*
203	* @param string $query Any non-SELECT query
204	* @param int $timeoutSeconds Maximum execution time in seconds
205	* @return string The query with timeout applied (unchanged on MySQL)
206	*/
207	public function applyStatementTimeout(string $query, int $timeoutSeconds): string {
208	if ($this->isMariaDB() && !ABJ_404_Solution_DatabaseRuntimeState::isSetStatementWrapperUnsupported()) {
209	return "SET STATEMENT max_statement_time=" . $timeoutSeconds . " FOR " . $query;
210	}
211	// MySQL has no timeout mechanism for non-SELECT queries. MariaDB hosts
212	// that have rejected SET STATEMENT (privilege denied or syntax not
213	// understood) earlier in this request fall through here too: the
214	// staged build's per-tick budget enforcement degrades to the cron
215	// tick's own wall-clock deadline rather than per-statement.
216	return $query;
217	}
218
219	/**
220	* @deprecated Use the 'timeout' option on queryAndGetResults() instead.
221	* Kept for backward compatibility with any external callers.
222	*
223	* @param string $insertSelectQuery The INSERT INTO ... SELECT ... query
224	* @param int $timeoutSeconds Maximum execution time in seconds
225	* @return string The query with timeout applied
226	*/
227	function applyTimeoutToInsertSelect(string $insertSelectQuery, int $timeoutSeconds): string {
228	return $this->applyNonLeadingSelectTimeout($insertSelectQuery, $timeoutSeconds);
229	}
230
231	/**
232	* True when $query begins with the timeout wrapper this trait emits:
233	* `SET STATEMENT max_statement_time=N FOR ...`. Used by the wrapper
234	* fallback path to confirm the failed query was wrapped before stripping.
235	*
236	* @param string $query
237	* @return bool
238	*/
239	public function queryHasSetStatementWrapper(string $query): bool {
240	return preg_match(
241	'/^\sSET\s+STATEMENT\s+max_statement_time\s=\s*\d+\s+FOR\s+/i',
242	$query
243	) === 1;
244	}
245
246	/**
247	* Strip the leading `SET STATEMENT max_statement_time=N FOR ` wrapper.
248	* Returns the unwrapped statement, or the input unchanged if no wrapper
249	* is present.
250	*
251	* @param string $query
252	* @return string
253	*/
254	public function stripSetStatementWrapper(string $query): string {
255	$stripped = preg_replace(
256	'/^\sSET\s+STATEMENT\s+max_statement_time\s=\s*\d+\s+FOR\s+/i',
257	'',
258	$query,
259	1
260	);
261	return is_string($stripped) ? $stripped : $query;
262	}
263
264	/**
265	* Re-execute a query without the `SET STATEMENT max_statement_time=N FOR `
266	* wrapper after the server rejected the wrapper itself (privilege denied
267	* or syntax not understood). Caches the result in
268	* ABJ_404_Solution_DatabaseCore::$setStatementWrapperUnsupported so every
269	* subsequent timeout-wrapped query in this request skips the wrapper too.
270	*
271	* Result harvest mirrors the other recovery paths
272	* (recoverFromCollationMismatchAndRetry, attemptMissingTableRepairAndRetry):
273	* write into $result by reference so the caller's downstream branches see
274	* the retry's outcome instead of the original error.
275	*
276	* $query is also passed by reference and mutated to the unwrapped form
277	* on success. Downstream retry paths in queryAndGetResults() (transient
278	* reconnect, deadlock retry, etc.) re-execute $query, so leaving the
279	* wrapper in place would re-trigger the same rejection on every retry.
280	*
281	* @param string $query Passed by reference. Mutated to the unwrapped form.
282	* @param array<string, mixed> $result Passed by reference; updated with retry rows / error.
283	* @param 'OBJECT'\|'OBJECT_K'\|'ARRAY_A'\|'ARRAY_N' $resultType wpdb output type for get_results().
284	* @return void
285	*/
286	public function retryWithoutSetStatementWrapper(
287	string &$query,
288	array &$result,
289	string $resultType
290	): void {
291	if (!$this->queryHasSetStatementWrapper($query)) {
292	// Defensive: nothing to strip. Caller misclassified the error.
293	return;
294	}
295	$unwrapped = $this->stripSetStatementWrapper($query);
296	// Cache the negative result for the rest of the request so we don't
297	// wrap-then-fail on every subsequent query. Reset between requests.
298	ABJ_404_Solution_DatabaseRuntimeState::setSetStatementWrapperUnsupported(true);
299	$this->logger->infoMessage(
300	'SET STATEMENT timeout wrapper rejected by server; '
301	. 'retrying query without wrapper and caching unsupported flag '
302	. 'for the rest of this request.'
303	);
304
305	global $wpdb;
306	/** @var wpdb $wpdb */
307	$wpdb->flush();
308	// Mutate $query so downstream retry paths execute the unwrapped form.
309	$query = $unwrapped;
310	// Re-route classification past any leading comments and the (now-absent)
311	// wrapper. Using queryProducesResultRows on the unwrapped query keeps
312	// the routing correct for INSERT/UPDATE/DELETE/DDL.
313	// SET STATEMENT wrapper-rejection recovery is a DAO-internal primitive
314	// (parallel to recoverFromCollationMismatchAndRetry). It must call
315	// $wpdb directly: re-routing through queryAndGetResults() would
316	// re-enter the same SET STATEMENT detection path, deepening the call
317	// stack on every retry. Per-bypass approval markers are inline below.
318	$unwrappedProducesRows = $this->queryProducesResultRows($unwrapped);
319	if ($unwrappedProducesRows) {
320	// DAO-bypass-approved: SET STATEMENT wrapper-rejection retry primitive.
321	$result['rows'] = $wpdb->get_results($unwrapped, $resultType);
322	} else {
323	// DAO-bypass-approved: SET STATEMENT wrapper-rejection retry primitive.
324	$wpdb->query($unwrapped);
325	$result['rows'] = array();
326	}
327	$this->core->harvestWpdbResult($result);
328	}
329	}
330