404-solution / includes / DatabaseUpgradesEtcTrait_TableRepair.php

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

215 lines 9.4 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Pre/post-upgrade table-correctness work for ABJ_404_Solution_DatabaseUpgradesEtc.
9	*
10	* Originally inlined in DatabaseUpgradesEtc.php; extracted in 4.1.8 alongside
11	* the new repairStrippedViewCacheTable() hardening and the _logs_hits recovery
12	* path so the host class stays under its line budget (FileSizeLimitsTest).
13	*
14	* The methods are scoped to "make the schema match the file's intent": detect
15	* tables that were corrupted by past DDL parsing bugs (3.3.3, 4.1.7) and either
16	* drop them for clean recreation, or recreate them empty when the cache-style
17	* table can be rebuilt by a later cron tick.
18	*/
19	trait ABJ_404_Solution_DatabaseUpgradesEtc_TableRepairTrait {
20
21	/**
22	* Run before runInitialCreateTables() during an upgrade. Cleans up data
23	* issues that would block the create/verify pass, then drops any table
24	* whose live schema is positively known to have been stripped.
25	*
26	* @return void
27	*/
28	function correctIssuesBefore() {
29	$this->logsRepo->correctDuplicateLookupValues();
30
31	// 3.3.4+: Repair any plugin table that was stripped of all columns by a
32	// DDL parsing bug. The 3.3.3 bug only affected view_cache, but any future
33	// DDL file shipped without parseable column syntax could wipe any table.
34	// Dropped tables are pure caches or safely recreatable; runInitialCreateTables()
35	// will recreate them immediately after.
36	$this->repairStrippedViewCacheTable();
37
38	$this->correctMatchData();
39	}
40
41	/**
42	* Run after runInitialCreateTables() during an upgrade. Cleans up data
43	* issues that depend on the new schema, then recreates any cache table
44	* that prior bugs may have dropped without recreating.
45	*
46	* @return void
47	*/
48	function correctIssuesAfter() {
49	$this->correctMatchData();
50	$this->recoverMissingLogsHitsTable();
51	}
52
53	/**
54	* For every permanent plugin table, check whether the table exists but is
55	* missing its primary `id` column — the signature of the 3.3.3 column-drop
56	* bug. When a stripped table is detected, ALTER it to add back the `id`
57	* AUTO_INCREMENT PRIMARY KEY in place; runInitialCreateTables() then runs
58	* verifyColumns to fill in any other missing columns.
59	*
60	* Why ALTER, not DROP: dropping the table during a daily cron path was the
61	* direct cause of the 4.1.6 → 4.1.7 incident, where ~93% of upgraded sites
62	* lost their `_logs_hits` table because a mis-named DDL file caused this
63	* method to mis-classify a runtime-rebuilt table as "stripped" and drop
64	* it. Even with the 4.1.8 positive-evidence guard, dropping during cron
65	* remains the wrong primitive: a future detection regression would again
66	* wipe live data. ALTER preserves whatever rows the table already holds,
67	* so the worst case of a mis-detection is a no-op rebuild of an index
68	* column the table already has — a recoverable warning, not data loss.
69	*
70	* Generalised in 3.3.5 from a view_cache-only fix to cover all plugin tables:
71	* the 3.3.3 bug only affected view_cache.sql, but any future DDL file shipped
72	* without parseable backtick column syntax would trigger the same data wipe on
73	* that table with no repair path.
74	*
75	* 4.1.8: Hardened to require POSITIVE evidence before repairing a table. The
76	* 4.1.7 release shipped a DDL file whose placeholder mis-classified
77	* `_logs_hits` (a runtime-rebuilt table with no `id` column) as permanent.
78	* The previous "drop if no `id` in live DDL" check then wiped the table on
79	* upgrade. The current check only fires when the file's DDL declares an
80	* `id` column AND the live table is missing it — absence of `id` in a file
81	* that never declared one is not evidence of stripping.
82	*
83	* 4.1.8: Also called from runInitialCreateTables() so that any caller of
84	* createDatabaseTables() — including non-upgrade callers like the daily
85	* insurance check — repairs stripped tables before CREATE TABLE IF NOT
86	* EXISTS turns the broken state into a permanent table that verifyColumns
87	* cannot fully repair. Idempotent: when the live DDL already declares
88	* `id`, every iteration short-circuits.
89	*
90	* @return void
91	*/
92	function repairStrippedViewCacheTable() {
93	foreach ($this->discoverPermanentDDLFiles() as $ddlEntry) {
94	$tableName = $this->dbCore->doTableNameReplacements($ddlEntry['placeholder']);
95
96	// Positive evidence required: the file's intended DDL must declare `id`.
97	// If the file never had an `id` column, absence in the live table is
98	// not "stripping" — it's the table's normal shape.
99	$intendedDdl = $ddlEntry['ddlContent'];
100	if (!$this->ddlDeclaresIdColumn($intendedDdl)) {
101	continue;
102	}
103
104	$liveDdl = $this->dbCore->getCreateTableDDL($tableName);
105
106	// Table doesn't exist at all — nothing to repair (recovery handled elsewhere).
107	if (empty($liveDdl)) {
108	continue;
109	}
110
111	// Live table has the column the file declares — table is intact.
112	if ($this->ddlDeclaresIdColumn($liveDdl)) {
113	continue;
114	}
115
116	// File declares `id`, live table is missing it — stripped.
117	// ALTER (not DROP): preserve whatever rows the table holds so a
118	// false-positive detection cannot lose user data. Both MySQL 5.7+
119	// and MariaDB 10.x accept retro-adding an AUTO_INCREMENT PRIMARY
120	// KEY in this single-statement form; the prior comment claiming
121	// otherwise (rationale for the original DROP) was incorrect.
122	$this->logger->infoMessage("Repairing stripped plugin table " . $tableName .
123	" (missing id column — caused by DDL parsing bug). Adding id column via ALTER.");
124	$this->dbCore->queryAndGetResults(
125	"ALTER TABLE `" . $tableName . "` " .
126	"ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"
127	);
128	}
129	}
130
131	/**
132	* Returns true if the DDL declares a column literally named `id` (in
133	* backticks, the only style permitted in plugin DDL files since 3.3.5 —
134	* see DDLColumnParsingRobustnessTest::testEveryDdlFileUsesBacktickColumnStyle).
135	*
136	* Matching `\bid\b` against raw DDL is unsafe — it hits the `id` substring
137	* in `auto_increment`, `void`, and any column name containing those letters.
138	*
139	* @param string $ddl
140	* @return bool
141	*/
142	private function ddlDeclaresIdColumn(string $ddl): bool {
143	return stripos($ddl, '`id`') !== false;
144	}
145
146	/**
147	* Recover the {prefix}_abj404_logs_hits table if it is missing.
148	*
149	* The 4.1.6 → 4.1.7 upgrade dropped this table on ~93% of sites because a
150	* mis-named DDL file caused repairStrippedViewCacheTable() to treat it as
151	* a permanent table that had been "stripped" (see git log for 731fec2e and
152	* the 4.1.7 → 4.1.8 changelog). This method creates the table empty so
153	* that the scheduled rebuild (createRedirectsForViewHitsTable) can
154	* re-populate it. It is safe to run on any site — getCreateTableDDL()
155	* detects an existing table and we skip the create.
156	*
157	* Idempotent. Cheap. Safe to call on every upgrade.
158	*
159	* @return void
160	*/
161	private function recoverMissingLogsHitsTable(): void {
162	$tableName = $this->dbCore->doTableNameReplacements('{wp_abj404_logs_hits}');
163	if ($this->dbCore->getCreateTableDDL($tableName) !== '') {
164	return;
165	}
166
167	$tempDdl = ABJ_404_Solution_Functions::readFileContents(
168	__DIR__ . '/sql/createLogsHitsTempTable.sql');
169	if (!is_string($tempDdl) \|\| trim($tempDdl) === '') {
170	return;
171	}
172
173	// The temp DDL targets `{wp_abj404_logs_hits}_temp`. Strip the `_temp`
174	// suffix to recreate the final table at its real name.
175	$finalDdl = str_replace(
176	'{wp_abj404_logs_hits}_temp',
177	'{wp_abj404_logs_hits}',
178	$tempDdl);
179	$finalDdl = $this->applyPluginTableCharsetCollate($finalDdl);
180	$finalDdl = $this->dbCore->doTableNameReplacements($finalDdl);
181
182	$this->logger->infoMessage("Recreating missing " . $tableName .
183	" (lost during the 4.1.6→4.1.7 upgrade). The scheduled rebuild will repopulate it.");
184	$this->dbCore->queryAndGetResults($finalDdl);
185
186	// The missing-table notice (set when ALTER TABLE failed during the 4.1.7
187	// activation) is now stale — the table has been recovered. Clear it so
188	// the admin does not see an error notice on the next page load.
189	if (function_exists('delete_transient')) {
190	delete_transient('abj404_plugin_db_notice');
191	}
192	}
193
194	/**
195	* Drop spelling-cache rows whose match data was never populated. These
196	* are remnants from interrupted background workers; the cache fills in
197	* organically on the next 404, so it's safe to delete the empty rows.
198	*
199	* Called from correctIssuesBefore() and correctIssuesAfter() during
200	* the upgrade flow. The "before" call may run when the spelling_cache
201	* table doesn't exist (fresh install, or after stripped-table drop), so
202	* suppress errors and skip the table-repair retry: there's nothing to
203	* delete if the table doesn't exist, and we don't want this maintenance
204	* call to set the missing_table admin notice transient.
205	*
206	* @return void
207	*/
208	function correctMatchData() {
209	$this->dbCore->queryAndGetResults(
210	"delete from {wp_abj404_spelling_cache} where matchdata is null or matchdata = ''",
211	array('log_errors' => false, 'skip_repair' => true)
212	);
213	}
214	}
215