404-solution / includes / database / upgrades / DatabaseUpgradeIndexes.php

DatabaseUpgradeIndexes.php in 404 Solution trunk, at includes/database/upgrades/DatabaseUpgradeIndexes.php

419 lines 20.4 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Index verification and repair for ABJ_404_Solution_DatabaseUpgradesEtc: for
9	* every create*Table.sql, bring the live table's indexes into agreement with
10	* the shipped definition -- adding what is missing, and rebuilding what exists
11	* under the right name with the wrong definition.
12	*
13	* Extracted from DatabaseUpgradesEtc.php in 4.1.12. The eager targeted
14	* column-add helpers it also carried for a while now live with the components
15	* that own those columns' backfills: canonical_url on
16	* {@see ABJ_404_Solution_DatabaseUpgradeCanonicalUrlBackfill}, the Step 3a
17	* denorm columns on {@see ABJ_404_Solution_DatabaseUpgradeRedirectsDenormBackfill}.
18	*/
19	class ABJ_404_Solution_DatabaseUpgradeIndexes extends ABJ_404_Solution_DatabaseUpgradeComponent {
20
21	/** Transient name prefix for the per-index rebuild-attempt marker. */
22	private const REBUILD_GUARD_PREFIX = 'abj404_index_rebuilt_';
23
24	/** How long a rebuild attempt suppresses a repeat of the same rebuild. */
25	private const REBUILD_GUARD_SECONDS = 30 * 24 * 60 * 60;
26
27	/** @return void */
28	function createIndexes() {
29	foreach ($this->upgrades()->bootstrapUpgrade()->discoverPermanentDDLFiles() as $ddlEntry) {
30	$tableName = $this->dbCore->doTableNameReplacements($ddlEntry['placeholder']);
31	$query = $this->dbCore->doTableNameReplacements($ddlEntry['ddlContent']);
32	$this->verifyIndexes($tableName, $query);
33	}
34	}
35
36	/**
37	* @param string $tableName
38	* @param string $createTableStatementGoal
39	* @return void
40	*/
41	function verifyIndexes($tableName, $createTableStatementGoal) {
42
43	// get the indexes.
44	// Pattern matches lines starting with "KEY" / "UNIQUE KEY" - handles composite indexes with commas inside parens
45	// Indexes: treat the CREATE TABLE SQL as source of truth, and treat the database as truth
46	// for what exists (SHOW INDEX). Avoid parsing SHOW CREATE TABLE output, which is vendor/format dependent.
47	$goalSpecsByName = ABJ_404_Solution_CreateTableIndexParser::fromCreateTableSql($createTableStatementGoal);
48	if (empty($goalSpecsByName)) {
49	return;
50	}
51
52	// An index is verified by its DEFINITION, never by its name alone. An
53	// index can exist under exactly the right name and still be the wrong
54	// index: MySQL and MariaDB silently strip a dropped column out of every
55	// index that named it and keep the rest, so a table that once ran a
56	// build whose DDL predated a column comes back with, say,
57	// idx_status_disabled_logshits_id defined as (status, disabled, id).
58	// A name-only check called that present and left the admin sort it was
59	// built for filesorting the whole table on every load, forever.
60	$definitions = new ABJ_404_Solution_TableIndexDefinitions($this->dbCore);
61	$liveDefinitions = $definitions->readLive($tableName);
62	if ($liveDefinitions === null) {
63	// Could not read the schema (missing table, denied permission, dead
64	// connection). Never treat that as "no indexes exist" -- that would
65	// issue DDL against a table we cannot even introspect.
66	$this->logger->debugMessage("Skipping the index check on {$tableName}: its index metadata could not be read.");
67	return;
68	}
69
70	// Read the columns next to the indexes, not between deciding and acting:
71	// both loops below need it, and a table whose schema cannot be read is
72	// not a table to issue ALTERs against for any reason.
73	$existingColumns = $this->readExistingColumnNames($tableName);
74	if ($existingColumns === null) {
75	$this->logger->debugMessage("Skipping the index check on {$tableName}: its column metadata could not be read.");
76	return;
77	}
78
79	$missingIndexNames = [];
80	$driftedIndexNames = [];
81	foreach ($goalSpecsByName as $indexName => $spec) {
82	$live = $liveDefinitions[strtolower((string)$indexName)] ?? null;
83	if (ABJ_404_Solution_IndexDefinitionComparator::signatureOfDdlSpec($spec) === null) {
84	// Our OWN SQL template did not parse into a column list this
85	// time -- a create*Table.sql truncated mid-write on a host that
86	// hit its disk quota, say. There is no goal to compare against
87	// and nothing safe to build: adding it would issue an ALTER
88	// carrying whatever fragment failed to parse, and rebuilding to
89	// it would drop a real index for one.
90	$this->logger->debugMessage("Skipping index {$indexName} on {$tableName}: its shipped "
91	. "definition could not be read out of the plugin's own SQL.");
92	} else if ($live === null) {
93	$missingIndexNames[] = $indexName;
94	} else if (!ABJ_404_Solution_TableIndexDefinitions::isDescribable($live)) {
95	// The index is present but the engine described it in a form we
96	// cannot compare (a functional index reports no Column_name; a
97	// driver that never reported Non_unique says nothing about its
98	// uniqueness). Neither missing nor drifted: creating it would
99	// collide with the name that already exists, and rebuilding it
100	// would rewrite the table on a difference we never established.
101	$this->logger->debugMessage("Leaving index {$indexName} on {$tableName} alone: "
102	. "the engine reports it in a form this version cannot describe.");
103	} else if (ABJ_404_Solution_IndexDefinitionComparator::isDriftedFromDdlSpec($live, $spec)) {
104	$driftedIndexNames[] = $indexName;
105	}
106	}
107	$missingIndexNames = $this->prioritizeMissingIndexNames($missingIndexNames);
108	$driftedIndexNames = $this->prioritizeMissingIndexNames($driftedIndexNames);
109
110	if (count($missingIndexNames) > 0) {
111	$this->logger->infoMessage($this->getUpgradeRuntimeId() . ": On {$tableName} I'm adding missing indexes: " . implode(', ', $missingIndexNames));
112	}
113
114	foreach ($missingIndexNames as $indexName) {
115	$spec = $goalSpecsByName[$indexName] ?? null;
116	if (empty($spec) \|\| !$this->indexColumnsAllExist($tableName, $spec, $existingColumns)) {
117	continue;
118	}
119	$this->deleteSpellingCacheBeforeUniqueIndex($tableName, $spec);
120	$this->indexWriter()->addIndex($tableName, $spec);
121	}
122
123	foreach ($driftedIndexNames as $indexName) {
124	$spec = $goalSpecsByName[$indexName] ?? null;
125	if (empty($spec) \|\| !$this->indexColumnsAllExist($tableName, $spec, $existingColumns)) {
126	continue;
127	}
128	$this->rebuildDriftedIndex($tableName, $spec,
129	$liveDefinitions[strtolower((string)$indexName)]);
130	}
131	}
132
133	/**
134	* Rebuild one index whose live definition no longer matches the DDL.
135	*
136	* DROP and ADD go in a single ALTER so the index is never absent between
137	* two statements, and so the engine makes one pass over the table
138	* instead of two. The online-DDL hints are tried first and fall back to
139	* a plain ALTER exactly as the missing-index add does.
140	*
141	* Two guards keep this from ever becoming a recurring cost on a large
142	* table:
143	*
144	* - the non-essential-write cooldown, so a host that is already out of
145	* disk or in read-only is not handed a table rewrite; and
146	* - a per-(table, index, goal) marker written BEFORE the attempt and
147	* cleared only once the engine confirms it now reports what we asked
148	* for. An engine that describes an index differently from the way we
149	* wrote it (or an ALTER that dies partway) therefore costs one
150	* rebuild per month, not one per upgrade tick. Changing the DDL
151	* changes the goal signature, which re-arms the repair.
152	*
153	* @param string $tableName
154	* @param array{name: string, columns: string, unique: bool} $spec
155	* @param array{columns: array<int, array{column: string, prefix: int\|null}>, unique: bool} $liveDefinition
156	* @return void
157	*/
158	private function rebuildDriftedIndex($tableName, array $spec, array $liveDefinition): void {
159	if ($this->dbCore->noticeState()->shouldSkipNonEssentialDbWrites()) {
160	$this->logger->debugMessage("Deferring the rebuild of {$spec['name']} on {$tableName} " .
161	"until the database write cooldown ends.");
162	return;
163	}
164	$goalSignature = ABJ_404_Solution_IndexDefinitionComparator::signatureOfDdlSpec($spec);
165	if ($goalSignature === null) {
166	// verifyIndexes() already refuses an unreadable goal, so this is
167	// the belt to that braces: a rebuild whose target definition
168	// nobody could read has no target, and there is no version of
169	// "drop the real index first" that is safe without one.
170	return;
171	}
172	$guardName = self::REBUILD_GUARD_PREFIX . md5(strtolower($tableName) . '\|' .
173	strtolower((string)$spec['name']) . '\|' . $goalSignature);
174	if (function_exists('get_transient') && get_transient($guardName) !== false) {
175	return;
176	}
177	if (function_exists('set_transient')) {
178	set_transient($guardName, 1, self::REBUILD_GUARD_SECONDS);
179	}
180
181	$this->logger->infoMessage($this->getUpgradeRuntimeId() . ": On {$tableName} I'm rebuilding " .
182	"{$spec['name']}: the table has (" .
183	ABJ_404_Solution_TableIndexDefinitions::describeColumns($liveDefinition) .
184	") but the schema defines " . trim((string)$spec['columns']) . ".");
185
186	$this->deleteSpellingCacheBeforeUniqueIndex($tableName, $spec);
187	$this->indexWriter()->addIndex($tableName, $spec, array('replace_existing' => true));
188
189	$after = (new ABJ_404_Solution_TableIndexDefinitions($this->dbCore))->readLive($tableName);
190	$rebuilt = is_array($after) ? ($after[strtolower((string)$spec['name'])] ?? null) : null;
191	if (is_array($rebuilt)
192	&& ABJ_404_Solution_IndexDefinitionComparator::signatureOfLiveDefinition($rebuilt) === $goalSignature) {
193	if (function_exists('delete_transient')) {
194	delete_transient($guardName);
195	}
196	return;
197	}
198	$this->logger->warn("After rebuilding {$spec['name']} on {$tableName} the server still describes it " .
199	"differently from " . trim((string)$spec['columns']) . ". Treating that as a difference in how this " .
200	"server reports indexes rather than as schema drift, and not rebuilding it again.");
201	}
202
203	/**
204	* The lowercased column names the table actually has, so an index that
205	* references a column this install does not carry can be skipped rather
206	* than attempted (schema-drift tolerance, defensive philosophy #1/#7).
207	*
208	* Returns null when the probe could not be answered, never an empty list.
209	* "This table has no columns" is not a thing a live table can report, so
210	* an empty answer only ever meant "the read failed" -- and read as a
211	* result it says every index column is present, which is the one
212	* conclusion that issues DDL against a table we cannot introspect. That
213	* is the same inference readLive() refuses two probes earlier, and it
214	* reached production as "Key column 'canonical_url' doesn't exist in
215	* table" on any host that denies the column read.
216	*
217	* @param string $tableName
218	* @return array<int, string>\|null
219	*/
220	private function readExistingColumnNames($tableName): ?array {
221	$existingColumns = [];
222	$quotedTableName = ABJ_404_Solution_TableIndexDefinitions::quoteIdentifier($tableName);
223	if ($quotedTableName === null) {
224	// Not a name we can safely put in a statement, so the probe is
225	// unanswerable rather than empty -- same contract as readLive().
226	return null;
227	}
228	$showColResult = $this->dbCore->queryAndGetResults("SHOW COLUMNS FROM " . $quotedTableName);
229	$lastError = isset($showColResult['last_error']) && is_scalar($showColResult['last_error'])
230	? (string)$showColResult['last_error'] : '';
231	if ($lastError !== '' \|\| !is_array($showColResult['rows'] ?? null)) {
232	return null;
233	}
234	$showColRows = $showColResult['rows'];
235	foreach ($showColRows as $colRow) {
236	if (!is_array($colRow)) { continue; }
237	foreach ($colRow as $key => $value) {
238	if (strtolower((string)$key) === 'field' && is_scalar($value)) {
239	$existingColumns[] = strtolower((string)$value);
240	break;
241	}
242	}
243	}
244	if (empty($existingColumns)) {
245	// A live table always has columns, so a successful read that names
246	// none is not an answer either -- the rows came back in a shape
247	// this version cannot read. Reporting it as "no columns" would
248	// warn once per index about columns that are probably all there.
249	return null;
250	}
251	return $existingColumns;
252	}
253
254	/**
255	* Whether every column an index spec names exists on the table. Applies
256	* to the rebuild path as much as the add path: dropping a drifted index
257	* whose replacement cannot be created would leave the table with neither.
258	*
259	* @param string $tableName
260	* @param array{name: string, columns: string, unique: bool} $spec
261	* @param array<int, string> $existingColumns
262	* @return bool
263	*/
264	private function indexColumnsAllExist($tableName, array $spec, array $existingColumns): bool {
265	$indexColNames = [];
266	foreach (ABJ_404_Solution_CreateTableIndexParser::ddlColumnList($spec['columns']) as $column) {
267	$indexColNames[] = $column['column'];
268	}
269	$missingCols = array_diff($indexColNames, $existingColumns);
270	if (empty($missingCols)) {
271	return true;
272	}
273	$this->logger->warn("Skipping index {$spec['name']} on {$tableName}: " .
274	"column(s) " . implode(', ', $missingCols) . " do not exist in the table.");
275	return false;
276	}
277
278	/**
279	* Adding (or rebuilding) the spelling cache's UNIQUE index fails if the
280	* table already holds duplicate rows, which is exactly the state a
281	* missing unique index allows. Emptying a cache costs nothing.
282	*
283	* @param string $tableName
284	* @param array{name: string, columns: string, unique: bool} $spec
285	* @return void
286	*/
287	private function deleteSpellingCacheBeforeUniqueIndex($tableName, array $spec): void {
288	$spellingCacheTableName = $this->dbCore->doTableNameReplacements('{wp_abj404_spelling_cache}');
289	if (strtolower($tableName) == $spellingCacheTableName && !empty($spec['unique'])) {
290	$this->contentRepo->deleteSpellingCache();
291	}
292	}
293
294	/**
295	* Put redirect admin-view performance indexes before lower-impact recovery
296	* indexes. Each index is still added by its own ALTER TABLE statement; this
297	* only controls which missing index is attempted first on weak hosts.
298	*
299	* @param array<int, string> $missingIndexNames
300	* @return array<int, string>
301	*/
302	private function prioritizeMissingIndexNames(array $missingIndexNames): array {
303	if (count($missingIndexNames) < 2) {
304	return $missingIndexNames;
305	}
306	$originalPosition = array();
307	foreach ($missingIndexNames as $i => $name) {
308	$originalPosition[$name] = $i;
309	}
310	$priority = array_flip(array(
311	'idx_dest_for_view_id',
312	'idx_status_disabled_timestamp_id',
313	'idx_status_disabled_url_sort_id',
314	'idx_disabled_url_sort_id',
315	'idx_status_disabled_dest_sort_id',
316	'idx_disabled_dest_sort_id',
317	'idx_status_disabled_logshits_id',
318	'idx_disabled_logshits_id',
319	'idx_status_disabled_last_used_id',
320	'idx_disabled_last_used_id',
321	'idx_status_disabled_score_id',
322	'idx_disabled_score_id',
323	'idx_status_disabled',
324	'idx_url_disabled_status',
325	'idx_canonical_url',
326	));
327	usort($missingIndexNames, function ($a, $b) use ($priority, $originalPosition) {
328	$pa = array_key_exists($a, $priority) ? $priority[$a] : 1000;
329	$pb = array_key_exists($b, $priority) ? $priority[$b] : 1000;
330	if ($pa === $pb) {
331	return ($originalPosition[$a] ?? 0) <=> ($originalPosition[$b] ?? 0);
332	}
333	return $pa <=> $pb;
334	});
335	return $missingIndexNames;
336	}
337
338	/**
339	* @param string $logsTable
340	* @param string\|null $createSqlOverride
341	* @return void
342	*/
343	public function ensureLogsCompositeIndex($logsTable, $createSqlOverride = null) {
344	$indexName = 'idx_requested_url_timestamp';
345	$createSql = is_string($createSqlOverride) ? $createSqlOverride : ABJ_404_Solution_FileSystemService::readFileContents(__DIR__ . "/../../sql/createLogTable.sql");
346	$specsByName = ABJ_404_Solution_CreateTableIndexParser::fromCreateTableSql($createSql);
347	$spec = $specsByName[$indexName] ?? null;
348	if (empty($spec)) {
349	$this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: index definition not found in createLogTable.sql");
350	return;
351	}
352	if (ABJ_404_Solution_IndexDefinitionComparator::signatureOfDdlSpec($spec) === null) {
353	$this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: its definition in " .
354	"createLogTable.sql did not parse into a column list.");
355	return;
356	}
357
358	// Same rule as verifyIndexes(): the NAME being present proves nothing.
359	// A logsv2 table whose requested_url column was ever dropped carries
360	// this composite narrowed to (timestamp) alone.
361	$liveDefinitions = (new ABJ_404_Solution_TableIndexDefinitions($this->dbCore))->readLive($logsTable);
362	if ($liveDefinitions === null) {
363	return;
364	}
365	$live = $liveDefinitions[strtolower($indexName)] ?? null;
366	if (is_array($live)
367	&& !ABJ_404_Solution_IndexDefinitionComparator::isDriftedFromDdlSpec($live, $spec)) {
368	// Present, and no difference from the DDL was established --
369	// either because it agrees, or because the engine described it in
370	// a form this version cannot compare. Both mean the same thing to
371	// a DROP INDEX + ADD INDEX on logsv2, which unlike the
372	// verifyIndexes() repair carries no once-per-month marker and
373	// would therefore re-run on every upgrade tick.
374	return;
375	}
376	// Preflight the columns on the emit path itself, the same gate
377	// verifyIndexes() applies to both of its loops. Established drift is
378	// not authorization to build: the comment above says this composite
379	// narrows when requested_url is dropped, and a table that lost the
380	// column presents exactly that way. Emitting anyway answers
381	// "Key column 'requested_url' doesn't exist in table" -- the line
382	// production reports carry -- and because the repair is a DROP and an
383	// ADD in one ALTER, an engine that applied it in halves would leave
384	// logsv2 with neither index. Probing here rather than beside the
385	// readLive() call keeps the read off the ticks that return early, and
386	// leaves no path to the builder that skips the check.
387	$existingColumns = $this->readExistingColumnNames($logsTable);
388	if ($existingColumns === null) {
389	$this->logger->debugMessage("Skipping {$indexName} on {$logsTable}: its column metadata could not be read.");
390	return;
391	}
392	if (!$this->indexColumnsAllExist($logsTable, $spec, $existingColumns)) {
393	return;
394	}
395
396	// try_online_first is off because this repair has always been issued
397	// plainly: it is a DROP and an ADD in one statement over a table that
398	// is multi-GB on busy sites, and which algorithm an engine picks for
399	// that is not something to change while fixing how its answer is read.
400	$this->indexWriter()->addIndex($logsTable, $spec, array(
401	'replace_existing' => is_array($live),
402	'try_online_first' => false,
403	));
404	}
405
406	/**
407	* The engine-facing half of index repair: what statement this server
408	* takes, and what its answer means. Built per call from the two
409	* collaborators this component already holds, the same way it builds
410	* ABJ_404_Solution_TableIndexDefinitions.
411	*
412	* @return ABJ_404_Solution_TableIndexWriter
413	*/
414	private function indexWriter(): ABJ_404_Solution_TableIndexWriter {
415	return new ABJ_404_Solution_TableIndexWriter($this->dbCore, $this->logger);
416	}
417
418	}
419