404-solution / includes / DataAccessTrait_QueryTimeouts.php

DataAccessTrait_QueryTimeouts.php in 404 Solution 4.1.19, at includes/DataAccessTrait_QueryTimeouts.php

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