| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
require_once __DIR__ . '/../DatabaseCollationHelper.php'; |
| 8 |
|
| 9 |
/** |
| 10 |
* Plugin-table collation/charset drift correction. |
| 11 |
* |
| 12 |
* Discovers the current charset+collation of every plugin table (and every character |
| 13 |
* column within), resolves the canonical utf8mb4 collation target for this site, and |
| 14 |
* ALTERs any table or column that has drifted off it. Drift sources include legacy |
| 15 |
* latin1 tables created before utf8mb4 support, hosting migrations that swap |
| 16 |
* collations under us, and partial dbDelta runs that leave per-column collations |
| 17 |
* inconsistent with the table default. |
| 18 |
* |
| 19 |
* Reachable from {@see ABJ_404_Solution_DatabaseUpgradeSelfHeal::verifyAndRepairCurrentSite()}, |
| 20 |
* the initial table-create boot path via the coordinator, and the daily cron via |
| 21 |
* {@see ABJ_404_Solution_DatabaseUpgradeDailyMaintenance::runDatabaseMaintenanceTasks()}. |
| 22 |
*/ |
| 23 |
class ABJ_404_Solution_DatabaseUpgradeCollationDrift extends ABJ_404_Solution_DatabaseUpgradeComponent { |
| 24 |
|
| 25 |
/** Retrieve the collation for a given table name. |
| 26 |
* @param string $tableName |
| 27 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if retrieval failed. |
| 28 |
*/ |
| 29 |
function getTableCollation($tableName) { |
| 30 |
// Try SHOW CREATE TABLE first |
| 31 |
$result = $this->getTableCollationFromShowCreate($tableName); |
| 32 |
|
| 33 |
if ($result !== null) { |
| 34 |
return $result; |
| 35 |
} |
| 36 |
|
| 37 |
// Fallback to information_schema query |
| 38 |
$result = $this->getTableCollationFromInformationSchema($tableName); |
| 39 |
|
| 40 |
if ($result !== null) { |
| 41 |
return $result; |
| 42 |
} |
| 43 |
|
| 44 |
$this->logger->warn("Could not retrieve collation for $tableName from SHOW CREATE TABLE or information_schema."); |
| 45 |
return null; |
| 46 |
} |
| 47 |
|
| 48 |
/** Parse collation/charset from SHOW CREATE TABLE output. |
| 49 |
* @param string $tableName |
| 50 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if parsing failed. |
| 51 |
*/ |
| 52 |
function getTableCollationFromShowCreate($tableName) { |
| 53 |
$query = "SHOW CREATE TABLE `$tableName`"; |
| 54 |
$results = $this->dbCore->queryAndGetResults($query); |
| 55 |
|
| 56 |
// Check for query errors or empty results |
| 57 |
$lastError = isset($results['last_error']) && is_scalar($results['last_error']) |
| 58 |
? (string)$results['last_error'] |
| 59 |
: ''; |
| 60 |
if ($lastError !== '') { |
| 61 |
$this->logger->debugMessage("SHOW CREATE TABLE failed for $tableName: " . $lastError); |
| 62 |
return null; |
| 63 |
} |
| 64 |
|
| 65 |
$rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; |
| 66 |
$firstRow = isset($rows[0]) && is_array($rows[0]) ? $rows[0] : null; |
| 67 |
if ($firstRow === null) { |
| 68 |
$this->logger->debugMessage("SHOW CREATE TABLE returned no data for $tableName."); |
| 69 |
return null; |
| 70 |
} |
| 71 |
|
| 72 |
// Use array_values to handle varying column name cases ('Create Table', 'CREATE TABLE', etc.) |
| 73 |
// SHOW CREATE TABLE returns: [table_name, create_statement] |
| 74 |
$row = array_values($firstRow); |
| 75 |
if (count($row) < 2 || empty($row[1])) { |
| 76 |
$this->logger->debugMessage("SHOW CREATE TABLE returned unexpected format for $tableName."); |
| 77 |
return null; |
| 78 |
} |
| 79 |
|
| 80 |
if (!is_string($row[1])) { |
| 81 |
$this->logger->debugMessage("SHOW CREATE TABLE returned non-string DDL for $tableName."); |
| 82 |
return null; |
| 83 |
} |
| 84 |
|
| 85 |
$createTableSQL = $row[1]; |
| 86 |
|
| 87 |
// The table default lives in the table-options section, after the |
| 88 |
// closing paren of the body -- never inside it. Column definitions come |
| 89 |
// FIRST in real engine output, and a column may carry its own |
| 90 |
// `CHARACTER SET x COLLATE y`, so a pattern run over the whole statement |
| 91 |
// returns the first COLUMN's charset and calls it the table's. That is |
| 92 |
// not a near-miss this caller can second-guess: correctCollations() |
| 93 |
// reads "utf8mb3" off a utf8mb4 table as drift and issues ALTER TABLE |
| 94 |
// ... CONVERT against a table that never drifted. The parser owns the |
| 95 |
// body/options boundary so no reader has to find it again. |
| 96 |
$tableDefault = ABJ_404_Solution_CreateTableOptionsParser::tableCharsetAndCollation($createTableSQL); |
| 97 |
if ($tableDefault === null) { |
| 98 |
$this->logger->debugMessage("SHOW CREATE TABLE output for $tableName has no readable " |
| 99 |
. "table-options section; falling back to information_schema."); |
| 100 |
return null; |
| 101 |
} |
| 102 |
|
| 103 |
$charset = $tableDefault['charset']; |
| 104 |
$collation = $tableDefault['collation']; |
| 105 |
|
| 106 |
// If we got charset but no explicit collation, derive default collation from charset |
| 107 |
if ($charset && !$collation) { |
| 108 |
$collation = $this->getDefaultCollationForCharset($charset); |
| 109 |
|
| 110 |
} else if ($collation && !$charset) { |
| 111 |
// The mirror case: a table-options section that states COLLATE and |
| 112 |
// leaves the charset implicit. A collation names its own charset, so |
| 113 |
// the pair is derivable -- through the single owner of that rule, not |
| 114 |
// a private explode() that could pair the two halves differently |
| 115 |
// from everywhere else (errno 1253 is what a mismatched pair costs). |
| 116 |
$pair = ABJ_404_Solution_DatabaseCollationHelper::charsetCollationPair($collation); |
| 117 |
$charset = $pair['charset']; |
| 118 |
} |
| 119 |
|
| 120 |
return ($collation && $charset) ? [$collation, $charset] : null; |
| 121 |
} |
| 122 |
|
| 123 |
/** Query information_schema for table collation (fallback method). |
| 124 |
* @param string $tableName |
| 125 |
* @return array{0: string, 1: string}|null Array of [collation, charset] or null if query failed. |
| 126 |
*/ |
| 127 |
function getTableCollationFromInformationSchema($tableName) { |
| 128 |
$queryResult = $this->dbCore->queryAndGetResults( |
| 129 |
"SELECT TABLE_COLLATION, " . |
| 130 |
"SUBSTRING_INDEX(TABLE_COLLATION, '_', 1) as TABLE_CHARSET " . |
| 131 |
"FROM information_schema.tables " . |
| 132 |
"WHERE TABLE_NAME = %s AND TABLE_SCHEMA = DATABASE()", |
| 133 |
['query_params' => [$tableName]] |
| 134 |
); |
| 135 |
|
| 136 |
$lastError = isset($queryResult['last_error']) && is_string($queryResult['last_error']) ? $queryResult['last_error'] : ''; |
| 137 |
if ($lastError !== '') { |
| 138 |
$this->logger->debugMessage("information_schema query failed for $tableName: " . $lastError); |
| 139 |
return null; |
| 140 |
} |
| 141 |
|
| 142 |
$results = isset($queryResult['rows']) && is_array($queryResult['rows']) ? $queryResult['rows'] : []; |
| 143 |
if (empty($results) || !is_array($results[0])) { |
| 144 |
$this->logger->debugMessage("Table $tableName not found in information_schema (may not exist)."); |
| 145 |
return null; |
| 146 |
} |
| 147 |
|
| 148 |
// Handle case-insensitive column names (some MySQL configs return uppercase) |
| 149 |
$row = array_change_key_case($results[0], CASE_UPPER); |
| 150 |
$collation = isset($row['TABLE_COLLATION']) && is_scalar($row['TABLE_COLLATION']) |
| 151 |
? (string)$row['TABLE_COLLATION'] |
| 152 |
: null; |
| 153 |
$charset = isset($row['TABLE_CHARSET']) && is_scalar($row['TABLE_CHARSET']) |
| 154 |
? (string)$row['TABLE_CHARSET'] |
| 155 |
: null; |
| 156 |
|
| 157 |
if (empty($collation)) { |
| 158 |
return null; |
| 159 |
} |
| 160 |
|
| 161 |
// Handle edge case where charset extraction might fail |
| 162 |
if (empty($charset)) { |
| 163 |
$charset = explode('_', $collation)[0]; |
| 164 |
} |
| 165 |
|
| 166 |
return [$collation, $charset]; |
| 167 |
} |
| 168 |
|
| 169 |
/** Get the default collation for a given charset. |
| 170 |
* @param string $charset |
| 171 |
* @return string|null Default collation or null if unknown. |
| 172 |
*/ |
| 173 |
function getDefaultCollationForCharset($charset) { |
| 174 |
// Common charset to default collation mappings |
| 175 |
$defaults = [ |
| 176 |
'utf8mb4' => 'utf8mb4_general_ci', |
| 177 |
'utf8' => 'utf8_general_ci', |
| 178 |
'utf8mb3' => 'utf8mb3_general_ci', |
| 179 |
'latin1' => 'latin1_swedish_ci', |
| 180 |
'ascii' => 'ascii_general_ci', |
| 181 |
]; |
| 182 |
|
| 183 |
$charsetLower = strtolower($charset); |
| 184 |
return $defaults[$charsetLower] ?? null; |
| 185 |
} |
| 186 |
|
| 187 |
/** |
| 188 |
* Keep collation identifiers SQL-safe. |
| 189 |
* |
| 190 |
* @param string $collation |
| 191 |
* @return string |
| 192 |
*/ |
| 193 |
private function sanitizeCollationIdentifier($collation) { |
| 194 |
if (!is_string($collation) || $collation === '') { |
| 195 |
return ''; |
| 196 |
} |
| 197 |
return preg_replace('/[^A-Za-z0-9_]/', '', $collation) ?? ''; |
| 198 |
} |
| 199 |
|
| 200 |
/** |
| 201 |
* Resolve the utf8mb4 collation target for plugin-table normalization. |
| 202 |
* |
| 203 |
* Priority: |
| 204 |
* 1) Active wpdb connection collation if utf8mb4 |
| 205 |
* 2) Most common existing utf8mb4 plugin-table collation |
| 206 |
* 3) Database default collation variable if utf8mb4 |
| 207 |
* 4) Safe fallback (utf8mb4_unicode_ci) |
| 208 |
* |
| 209 |
* @param array<int, string> $tableNames |
| 210 |
* @param array<string, array{0: string, 1: string}|null> $tableCollations Optional map: table => [collation, charset] |
| 211 |
* @return string |
| 212 |
*/ |
| 213 |
private function resolveTargetUtf8mb4Collation($tableNames, $tableCollations = []) { |
| 214 |
global $wpdb; |
| 215 |
|
| 216 |
if (!empty($wpdb->collate)) { |
| 217 |
$wpdbCollation = $this->sanitizeCollationIdentifier((string)$wpdb->collate); |
| 218 |
if (ABJ_404_Solution_DatabaseCollationHelper::isUtf8mb4Collation($wpdbCollation)) { |
| 219 |
return $wpdbCollation; |
| 220 |
} |
| 221 |
} |
| 222 |
|
| 223 |
$counts = []; |
| 224 |
foreach ($tableNames as $tableName) { |
| 225 |
$row = $tableCollations[$tableName] ?? $this->getTableCollation($tableName); |
| 226 |
if (!is_array($row)) { |
| 227 |
continue; |
| 228 |
} |
| 229 |
$collation = $this->sanitizeCollationIdentifier((string)$row[0]); |
| 230 |
$charset = strtolower((string)$row[1]); |
| 231 |
if ($charset === 'utf8mb4' && ABJ_404_Solution_DatabaseCollationHelper::isUtf8mb4Collation($collation)) { |
| 232 |
$counts[$collation] = ($counts[$collation] ?? 0) + 1; |
| 233 |
} |
| 234 |
} |
| 235 |
if (!empty($counts)) { |
| 236 |
arsort($counts); |
| 237 |
return array_key_first($counts); |
| 238 |
} |
| 239 |
|
| 240 |
$vars = $this->dbCore->queryAndGetResults("SHOW VARIABLES LIKE 'collation_database'"); |
| 241 |
$varRows = isset($vars['rows']) && is_array($vars['rows']) ? $vars['rows'] : []; |
| 242 |
if (!empty($varRows)) { |
| 243 |
$row = is_array($varRows[0]) ? $varRows[0] : []; |
| 244 |
$valueRaw = isset($row['Value']) ? $row['Value'] : (isset($row['value']) ? $row['value'] : ''); |
| 245 |
$value = $this->sanitizeCollationIdentifier(is_scalar($valueRaw) ? (string)$valueRaw : ''); |
| 246 |
if (ABJ_404_Solution_DatabaseCollationHelper::isUtf8mb4Collation($value)) { |
| 247 |
return $value; |
| 248 |
} |
| 249 |
} |
| 250 |
|
| 251 |
return 'utf8mb4_unicode_ci'; |
| 252 |
} |
| 253 |
|
| 254 |
/** |
| 255 |
* Ensure our tables use utf8mb4 (do not alter WordPress core tables). |
| 256 |
* @return void |
| 257 |
*/ |
| 258 |
function correctCollations() { |
| 259 |
// Discover all plugin tables dynamically so new tables are automatically included. |
| 260 |
// Use queryAndGetResults() so the SHOW TABLES call goes through the same DAO |
| 261 |
// layer as all other queries (enables testability via mock injection). |
| 262 |
// {wp_prefix} is resolved by doTableNameReplacements inside queryAndGetResults. |
| 263 |
$rawResult = $this->dbCore->queryAndGetResults("SHOW TABLES LIKE '{wp_prefix}abj404_%'"); |
| 264 |
/** @var array<int, string> $abjTableNames */ |
| 265 |
$abjTableNames = []; |
| 266 |
if (isset($rawResult['rows']) && is_array($rawResult['rows'])) { |
| 267 |
foreach ($rawResult['rows'] as $row) { |
| 268 |
$tableName = is_array($row) ? reset($row) : $row; |
| 269 |
if (is_scalar($tableName)) { |
| 270 |
$abjTableNames[] = (string)$tableName; |
| 271 |
} |
| 272 |
} |
| 273 |
} |
| 274 |
|
| 275 |
// Exclude the vestigial staged-build tables (view_build / view_done / |
| 276 |
// view_deleteme). They are no longer read or maintained (the denorm |
| 277 |
// columns on wp_abj404_redirects are the live source) and are slated |
| 278 |
// for removal in the final denorm step. ALTERing tables that nothing |
| 279 |
// reads and that are about to be dropped has no correctness value. |
| 280 |
// This matches the existing "transient tables are out of scope" |
| 281 |
// treatment in the permanent-DDL schema-diff sweep |
| 282 |
// (DatabaseUpgradeTableRepair). |
| 283 |
$abjTableNames = array_values(array_filter( |
| 284 |
$abjTableNames, |
| 285 |
static function ($t) { |
| 286 |
return preg_match('/abj404_view_(build|done|deleteme)$/i', (string)$t) !== 1; |
| 287 |
} |
| 288 |
)); |
| 289 |
|
| 290 |
/** @var array<string, array{0: string, 1: string}|null> $tableCollations */ |
| 291 |
$tableCollations = []; |
| 292 |
foreach ($abjTableNames as $tableName) { |
| 293 |
$collationResult = $this->getTableCollation($tableName); |
| 294 |
$tableCollations[$tableName] = ( |
| 295 |
is_array($collationResult) |
| 296 |
&& isset($collationResult[0], $collationResult[1]) |
| 297 |
&& is_scalar($collationResult[0]) |
| 298 |
&& is_scalar($collationResult[1]) |
| 299 |
) ? [(string)$collationResult[0], (string)$collationResult[1]] : null; |
| 300 |
} |
| 301 |
|
| 302 |
$targetCharset = 'utf8mb4'; |
| 303 |
$targetCollation = $this->resolveTargetUtf8mb4Collation($abjTableNames, $tableCollations); |
| 304 |
|
| 305 |
foreach ($abjTableNames as $tableName) { |
| 306 |
$abjTableData = $tableCollations[$tableName] ?? null; |
| 307 |
|
| 308 |
if ($abjTableData === null) { |
| 309 |
$this->logger->warn("Failed to retrieve collation for $tableName."); |
| 310 |
continue; // Skip this table if collation can't be determined |
| 311 |
} |
| 312 |
|
| 313 |
[$abjTableCollation, $abjTableCharset] = $abjTableData; |
| 314 |
|
| 315 |
$needsUpdate = !($abjTableCharset === $targetCharset && $abjTableCollation === $targetCollation); |
| 316 |
if (!$needsUpdate) { |
| 317 |
// Table default matches, but individual columns can still drift (e.g., some columns left as *_bin). |
| 318 |
$columnMismatch = $this->tableHasMismatchedCharacterColumnCollation($tableName, $targetCharset, $targetCollation); |
| 319 |
if ($columnMismatch === true) { |
| 320 |
$needsUpdate = true; |
| 321 |
$this->logger->infoMessage("Detected column-level collation mismatch on {$tableName}; normalizing to {$targetCharset}/{$targetCollation}"); |
| 322 |
} else if ($columnMismatch === null) { |
| 323 |
$this->logger->warn("Could not verify column collations for {$tableName}; skipping collation normalization."); |
| 324 |
continue; |
| 325 |
} |
| 326 |
} |
| 327 |
if (!$needsUpdate) { |
| 328 |
continue; |
| 329 |
} |
| 330 |
|
| 331 |
$this->logger->infoMessage("Updating charset/collation on {$tableName} from {$abjTableCharset}/{$abjTableCollation} to {$targetCharset}/{$targetCollation}"); |
| 332 |
|
| 333 |
$query = "ALTER TABLE {table_name} CONVERT TO CHARSET " . $targetCharset . |
| 334 |
" COLLATE " . $targetCollation; |
| 335 |
$query = str_replace('{table_name}', $tableName, $query); |
| 336 |
$results = $this->dbCore->queryAndGetResults($query, |
| 337 |
array('ignore_errors' => array("Index column size too large"))); |
| 338 |
|
| 339 |
$lastErr = isset($results['last_error']) && is_string($results['last_error']) ? $results['last_error'] : ''; |
| 340 |
if ($lastErr !== '' && |
| 341 |
strpos($lastErr, "Index column size too large") !== false) { |
| 342 |
|
| 343 |
$this->logger->warn("Charset/collation change for $tableName failed: Index column size too large. Deleting indexes and retrying..."); |
| 344 |
|
| 345 |
// delete indexes and try again. |
| 346 |
$this->upgrades()->schemaDiffUpgrade()->deleteIndexes($tableName); |
| 347 |
|
| 348 |
$retryResults = $this->dbCore->queryAndGetResults($query); |
| 349 |
$retryLastError = isset($retryResults['last_error']) && is_scalar($retryResults['last_error']) |
| 350 |
? (string)$retryResults['last_error'] |
| 351 |
: ''; |
| 352 |
if ($retryLastError !== '') { |
| 353 |
$this->logger->warn("Charset/collation retry for $tableName failed: " . $retryLastError); |
| 354 |
} else { |
| 355 |
$this->logger->infoMessage("Successfully changed charset/collation of $tableName after retry."); |
| 356 |
} |
| 357 |
|
| 358 |
} else if (empty($results['last_error'])) { |
| 359 |
$this->logger->infoMessage("Successfully changed charset/collation of $tableName to {$targetCharset}/{$targetCollation}"); |
| 360 |
} else { |
| 361 |
$resultLastError = isset($results['last_error']) && is_scalar($results['last_error']) |
| 362 |
? (string)$results['last_error'] |
| 363 |
: ''; |
| 364 |
$this->logger->warn("Charset/collation change for $tableName failed: " . $resultLastError); |
| 365 |
} |
| 366 |
} |
| 367 |
} |
| 368 |
|
| 369 |
/** |
| 370 |
* Detect character column collation drift on a table. |
| 371 |
* |
| 372 |
* Some environments can end up with per-column collations that differ from the table default |
| 373 |
* (e.g., `utf8mb4_bin` on one VARCHAR column while the table default is `utf8mb4_unicode_520_ci`). |
| 374 |
* This causes MySQL errors in string operations (REPLACE/LOWER) that mix collations. |
| 375 |
* |
| 376 |
* @param string $tableName Fully qualified table name (with prefix) |
| 377 |
* @param string $targetCharset Expected charset (e.g., utf8mb4) |
| 378 |
* @param string $targetCollation Expected collation (e.g., utf8mb4_unicode_ci) |
| 379 |
* @return bool|null True if mismatch found, false if all match, null if query failed |
| 380 |
*/ |
| 381 |
private function tableHasMismatchedCharacterColumnCollation($tableName, $targetCharset, $targetCollation) { |
| 382 |
$results = $this->dbCore->queryAndGetResults("SHOW FULL COLUMNS FROM " . $tableName); |
| 383 |
$lastError = isset($results['last_error']) && is_scalar($results['last_error']) |
| 384 |
? (string)$results['last_error'] |
| 385 |
: ''; |
| 386 |
if ($lastError !== '') { |
| 387 |
$this->logger->warn("Failed to read columns for {$tableName}: " . $lastError); |
| 388 |
return null; |
| 389 |
} |
| 390 |
/** @var array<int, array<string, mixed>> $rows */ |
| 391 |
$rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; |
| 392 |
if (empty($rows)) { |
| 393 |
return false; |
| 394 |
} |
| 395 |
|
| 396 |
$collationKey = null; |
| 397 |
$firstRow = $rows[0]; |
| 398 |
foreach (array_keys($firstRow) as $key) { |
| 399 |
if ($this->f->strtolower((string)$key) === 'collation') { |
| 400 |
$collationKey = $key; |
| 401 |
break; |
| 402 |
} |
| 403 |
} |
| 404 |
if ($collationKey === null) { |
| 405 |
$this->logger->warn("SHOW FULL COLUMNS returned no Collation column for {$tableName}"); |
| 406 |
return null; |
| 407 |
} |
| 408 |
|
| 409 |
foreach ($rows as $row) { |
| 410 |
if (!is_array($row)) { |
| 411 |
continue; |
| 412 |
} |
| 413 |
$rawColCollation = $row[$collationKey] ?? null; |
| 414 |
if ($rawColCollation === null || !is_string($rawColCollation) || trim($rawColCollation) === '') { |
| 415 |
continue; // Non-character columns |
| 416 |
} |
| 417 |
$colCollation = trim($rawColCollation); |
| 418 |
$colCharset = explode('_', $colCollation)[0] ?? ''; |
| 419 |
|
| 420 |
if ($colCharset !== $targetCharset || $colCollation !== $targetCollation) { |
| 421 |
return true; |
| 422 |
} |
| 423 |
} |
| 424 |
|
| 425 |
return false; |
| 426 |
} |
| 427 |
} |
| 428 |
|
