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

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

278 lines 12.3 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	class ABJ_404_Solution_DatabaseUpgradeTableRepair extends ABJ_404_Solution_DatabaseUpgradeComponent {
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	// Denorm Step 3e-D (i467): drop the transient staged view-build tables
53	// (view_build, view_done, view_deleteme) wholesale. The denorm chain
54	// moved the admin redirect-list read onto derived columns on
55	// abj404_redirects, so these tables (and the idx_pub_* per-sort indexes
56	// that only ever lived on view_build) are pure residue. Dropping the
57	// tables supersedes the old per-column cleanup that dropped the
58	// translated *_for_view label columns: there is no point altering
59	// columns on a table we drop in the same pass. Idempotent + cron-guarded
60	// inside the component; CronReachableDestructiveSqlLintTest Rule G proves
61	// the DROP is unreachable from cron.
62	$this->upgrades()->dropStagedViewTablesUpgrade()->dropStagedViewTables();
63
64	// t_260523_224315_207: drop the deprecated mutation watermark side
65	// table. Redirect changes now use direct rebuild invalidation instead
66	// of a separate mutation-signal subsystem. Idempotent DROP IF EXISTS so
67	// a fresh install (no legacy table) and a re-upgrade (already dropped)
68	// are both no-ops. See docs/design-lesson-watermark-overengineering.md.
69	$this->dropDeprecatedMutationWatermarkTable();
70	}
71
72	/**
73	* Drop the deprecated `wp_abj404_mutation_watermark` table. The table
74	* held a single-row counter that the pre-removal watermark primitive
75	* incremented on every mutation. Safe to call on every upgrade -- the
76	* statement is idempotent and the table cannot reappear because no
77	* production code creates it any more.
78	*
79	* @return void
80	*/
81	function dropDeprecatedMutationWatermarkTable() {
82	if (function_exists('wp_doing_cron') && wp_doing_cron()) { return; }
83	global $wpdb;
84	if (!is_object($wpdb) \|\| !method_exists($wpdb, 'query')) {
85	return;
86	}
87	$prefix = isset($wpdb->prefix) ? strtolower((string)$wpdb->prefix) : 'wp_';
88	$deprecatedWatermarkTableName = $prefix . 'abj404_mutation_watermark';
89	// @utf8-audit: opt-out - system-controlled table name composed from $wpdb->prefix plus the fixed-literal "abj404_mutation_watermark", cannot contain invalid UTF-8 bytes.
90	// DAO-bypass-approved: idempotent DROP TABLE IF EXISTS on a deprecated table; DAO error logging would surface a benign "table did not exist" line on every upgrade.
91	$wpdb->query("DROP TABLE IF EXISTS `" . esc_sql($deprecatedWatermarkTableName) . "`");
92
93	// Also drop the orphaned wp_options keys from the removed watermark /
94	// admin-mutation gate system. These options were used by the staged-build
95	// at-stage abort gate and the admin-mutation visibility gate, both of
96	// which were removed in favor of the 120s cache TTL + explicit
97	// invalidation on admin mutation.
98	if (function_exists('delete_option')) {
99	$orphanedOptions = array(
100	$prefix . 'abj404_view_done_mutation_invalidated_at',
101	$prefix . 'abj404_view_build_started_watermark',
102	$prefix . 'abj404_view_build_active_started_watermark',
103	$prefix . 'abj404_view_build_last_started_watermark',
104	$prefix . 'abj404_view_build_built_watermark',
105	);
106	foreach ($orphanedOptions as $optionName) {
107	delete_option($optionName);
108	}
109	}
110	}
111
112	/**
113	* For every permanent plugin table, check whether the table exists but is
114	* missing its primary `id` column — the signature of the 3.3.3 column-drop
115	* bug. When a stripped table is detected, ALTER it to add back the `id`
116	* AUTO_INCREMENT PRIMARY KEY in place; runInitialCreateTables() then runs
117	* verifyColumns to fill in any other missing columns.
118	*
119	* Why ALTER, not DROP: dropping the table during a daily cron path was the
120	* direct cause of the 4.1.6 → 4.1.7 incident, where ~93% of upgraded sites
121	* lost their `_logs_hits` table because a mis-named DDL file caused this
122	* method to mis-classify a runtime-rebuilt table as "stripped" and drop
123	* it. Even with the 4.1.8 positive-evidence guard, dropping during cron
124	* remains the wrong primitive: a future detection regression would again
125	* wipe live data. ALTER preserves whatever rows the table already holds,
126	* so the worst case of a mis-detection is a no-op rebuild of an index
127	* column the table already has — a recoverable warning, not data loss.
128	*
129	* Generalised in 3.3.5 from a view_cache-only fix to cover all plugin tables:
130	* the 3.3.3 bug only affected view_cache.sql, but any future DDL file shipped
131	* without parseable backtick column syntax would trigger the same data wipe on
132	* that table with no repair path.
133	*
134	* 4.1.8: Hardened to require POSITIVE evidence before repairing a table. The
135	* 4.1.7 release shipped a DDL file whose placeholder mis-classified
136	* `_logs_hits` (a runtime-rebuilt table with no `id` column) as permanent.
137	* The previous "drop if no `id` in live DDL" check then wiped the table on
138	* upgrade. The current check only fires when the file's DDL declares an
139	* `id` column AND the live table is missing it — absence of `id` in a file
140	* that never declared one is not evidence of stripping.
141	*
142	* 4.1.8: Also called from runInitialCreateTables() so that any caller of
143	* createDatabaseTables() — including non-upgrade callers like the daily
144	* insurance check — repairs stripped tables before CREATE TABLE IF NOT
145	* EXISTS turns the broken state into a permanent table that verifyColumns
146	* cannot fully repair. Idempotent: when the live DDL already declares
147	* `id`, every iteration short-circuits.
148	*
149	* @return void
150	*/
151	function repairStrippedViewCacheTable() {
152	foreach ($this->upgrades()->bootstrapUpgrade()->discoverPermanentDDLFiles() as $ddlEntry) {
153	$tableName = $this->dbCore->doTableNameReplacements($ddlEntry['placeholder']);
154
155	// Positive evidence required: the file's intended DDL must declare `id`.
156	// If the file never had an `id` column, absence in the live table is
157	// not "stripping" — it's the table's normal shape.
158	$intendedDdl = $ddlEntry['ddlContent'];
159	if (!$this->ddlDeclaresIdColumn($intendedDdl)) {
160	continue;
161	}
162
163	$liveDdl = $this->dbCore->tableNameResolver()->getCreateTableDDL($tableName);
164
165	// Table doesn't exist at all — nothing to repair (recovery handled elsewhere).
166	if (empty($liveDdl)) {
167	continue;
168	}
169
170	// Live table has the column the file declares — table is intact.
171	if ($this->ddlDeclaresIdColumn($liveDdl)) {
172	continue;
173	}
174
175	// File declares `id`, live table is missing it — stripped.
176	// ALTER (not DROP): preserve whatever rows the table holds so a
177	// false-positive detection cannot lose user data. Both MySQL 5.7+
178	// and MariaDB 10.x accept retro-adding an AUTO_INCREMENT PRIMARY
179	// KEY in this single-statement form; the prior comment claiming
180	// otherwise (rationale for the original DROP) was incorrect.
181	$this->logger->infoMessage("Repairing stripped plugin table " . $tableName .
182	" (missing id column — caused by DDL parsing bug). Adding id column via ALTER.");
183	$this->dbCore->queryAndGetResults(
184	"ALTER TABLE `" . $tableName . "` " .
185	"ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"
186	);
187	}
188	}
189
190	/**
191	* Returns true if the DDL declares a column literally named `id`.
192	*
193	* Parse the column region instead of scanning the whole statement. An
194	* index can itself be named `id`, but that is not evidence that an `id`
195	* column exists and must not suppress stripped-table repair.
196	*
197	* @param string $ddl
198	* @return bool
199	*/
200	private function ddlDeclaresIdColumn(string $ddl): bool {
201	return in_array(
202	'id',
203	ABJ_404_Solution_CreateTableColumnParser::columnNames($ddl),
204	true
205	);
206	}
207
208	/**
209	* Recover the {prefix}_abj404_logs_hits table if it is missing.
210	*
211	* The 4.1.6 → 4.1.7 upgrade dropped this table on ~93% of sites because a
212	* mis-named DDL file caused repairStrippedViewCacheTable() to treat it as
213	* a permanent table that had been "stripped" (see git log for 731fec2e and
214	* the 4.1.7 → 4.1.8 changelog). This method creates the table empty so
215	* that the scheduled rebuild (createRedirectsForViewHitsTable) can
216	* re-populate it. It is safe to run on any site — getCreateTableDDL()
217	* detects an existing table and we skip the create.
218	*
219	* Idempotent. Cheap. Safe to call on every upgrade.
220	*
221	* @return void
222	*/
223	private function recoverMissingLogsHitsTable(): void {
224	$tableName = $this->dbCore->doTableNameReplacements('{wp_abj404_logs_hits}');
225	if ($this->dbCore->tableNameResolver()->getCreateTableDDL($tableName) !== '') {
226	return;
227	}
228
229	$tempDdl = ABJ_404_Solution_FileSystemService::readFileContents(
230	__DIR__ . '/../../sql/createLogsHitsTempTable.sql');
231	if (!is_string($tempDdl) \|\| trim($tempDdl) === '') {
232	return;
233	}
234
235	// The temp DDL targets `{wp_abj404_logs_hits}_temp`. Strip the `_temp`
236	// suffix to recreate the final table at its real name.
237	$finalDdl = str_replace(
238	'{wp_abj404_logs_hits}_temp',
239	'{wp_abj404_logs_hits}',
240	$tempDdl);
241	$finalDdl = $this->upgrades()->bootstrapUpgrade()->applyPluginTableCharsetCollate($finalDdl);
242	$finalDdl = $this->dbCore->doTableNameReplacements($finalDdl);
243
244	$this->logger->infoMessage("Recreating missing " . $tableName .
245	" (lost during the 4.1.6→4.1.7 upgrade). The scheduled rebuild will repopulate it.");
246	$this->dbCore->queryAndGetResults($finalDdl);
247
248	// The missing-table notice (set when ALTER TABLE failed during the 4.1.7
249	// activation) is now stale — the table has been recovered. Clear it so
250	// the admin does not see an error notice on the next page load.
251	if (function_exists('delete_transient')) {
252	delete_transient('abj404_plugin_db_notice');
253	}
254	}
255
256	/**
257	* Drop spelling-cache rows whose match data was never populated. These
258	* are remnants from interrupted background workers; the cache fills in
259	* organically on the next 404, so it's safe to delete the empty rows.
260	*
261	* Called from correctIssuesBefore() and correctIssuesAfter() during
262	* the upgrade flow. The "before" call may run when the spelling_cache
263	* table doesn't exist (fresh install, or after stripped-table drop), so
264	* suppress errors and skip the table-repair retry: there's nothing to
265	* delete if the table doesn't exist, and we don't want this maintenance
266	* call to set the missing_table admin notice transient.
267	*
268	* @return void
269	*/
270	function correctMatchData() {
271	$this->dbCore->queryAndGetResults(
272	"delete from {wp_abj404_spelling_cache} where matchdata is null or matchdata = ''",
273	array('log_errors' => false, 'skip_repair' => true)
274	);
275	}
276
277	}
278