WordPress.org
Get WordPress

Plugin Directory

Changeset 3423944


Ignore:
Timestamp:
12/19/2025 07:14:56 PM (3 months ago)
Author:
aaron13100
Message:
  • FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured ALTER TABLE ... ADD INDEX ... statements.
  • Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
Location:
404-solution
Files:
10 edited
1 copied

Legend:

Unmodified
Added
Removed

  • 404-solution/tags/3.1.7/404-solution.php

    r3423238 r3423944  
    88    Author URI:  https://www.ajexperience.com/404-solution/
    99
    10         Version: 3.1.6
     10        Version: 3.1.7
    1111    Requires at least: 5.0
    1212    Requires PHP: 7.4

  • 404-solution/tags/3.1.7/CHANGELOG.md

    r3423238 r3423944  
    11# Changelog #
     2
     3## Version 3.1.7 (Dec 19, 2025) ##
     4* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     5* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
    26
    37## Version 3.1.6 (Dec 18, 2025) ##

  • 404-solution/tags/3.1.7/README.md

    r3423238 r3423944  
    8181## Changelog ##
    8282
     83## Version 3.1.7 (Dec 19, 2025) ##
     84* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     85* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
     86
    8387## Version 3.1.6 (Dec 18, 2025) ##
    8488* FIX: Redirects table pagination/search no longer fails on some MariaDB versions with a SQL syntax error while updating the table.

  • 404-solution/tags/3.1.7/includes/DatabaseUpgradesEtc.php

    r3423122 r3423944  
    597597            // get the indexes.
    598598            // Pattern matches lines starting with "KEY" / "UNIQUE KEY" - handles composite indexes with commas inside parens
    599             $existingTableMatches = null;
    600             $goalTableMatches = null;
    601             preg_match_all('/^\s*((?:unique\s+)?key\s+.+?)\s*,?\s*$/im', $existingTableSQL, $existingTableMatches);
    602             preg_match_all('/^\s*((?:unique\s+)?key\s+.+?)\s*,?\s*$/im', $createTableStatementGoal, $goalTableMatches);
    603            
    604             $extractIndexName = function($indexDDL) {
    605                 $matches = null;
    606                 // Matches: KEY `name` (...) or UNIQUE KEY `name` (...)
    607                 preg_match('/\bkey\b\s+`([^`]+)`/i', $indexDDL, $matches);
    608                 return $matches[1] ?? null;
    609             };
    610            
    611             $goalTableMatchesColumnDDL = $goalTableMatches[1] ?? [];
    612             $existingTableMatchesColumnDDL = $existingTableMatches[1] ?? [];
    613            
    614             $goalIndexesByName = [];
    615             foreach ($goalTableMatchesColumnDDL as $indexDDL) {
    616                 $indexName = $extractIndexName($indexDDL);
    617                 if (!empty($indexName)) {
    618                     $goalIndexesByName[$indexName] = $indexDDL;
     599            // Indexes: treat the CREATE TABLE SQL as source of truth, and treat the database as truth
     600            // for what exists (SHOW INDEX). Avoid parsing SHOW CREATE TABLE output, which is vendor/format dependent.
     601            $goalSpecsByName = $this->parseIndexSpecsFromCreateTableSql($createTableStatementGoal);
     602
     603            $missingIndexNames = [];
     604            foreach (array_keys($goalSpecsByName) as $indexName) {
     605                if (!$this->indexExists($tableName, $indexName)) {
     606                    $missingIndexNames[] = $indexName;
    619607                }
    620608            }
    621            
    622             $existingIndexNames = [];
    623             foreach ($existingTableMatchesColumnDDL as $indexDDL) {
    624                 $indexName = $extractIndexName($indexDDL);
    625                 if (!empty($indexName)) {
    626                     $existingIndexNames[$indexName] = true;
    627                 }
    628             }
    629            
    630             // Compare by index name to avoid false positives from harmless formatting differences (e.g., "USING BTREE").
    631             $missingIndexNames = array_diff(array_keys($goalIndexesByName), array_keys($existingIndexNames));
    632            
    633             // say why we're doing what we're doing.
     609
    634610            if (count($missingIndexNames) > 0) {
    635611                $this->logger->infoMessage(self::$uniqID . ": On {$tableName} I'm adding missing indexes: " . implode(', ', $missingIndexNames));
    636612            }
    637            
     613
    638614            foreach ($missingIndexNames as $indexName) {
    639                 $indexDDL = $goalIndexesByName[$indexName] ?? null;
    640                 if (empty($indexDDL)) {
     615                $spec = $goalSpecsByName[$indexName] ?? null;
     616                if (empty($spec)) {
    641617                    continue;
    642618                }
    643                 // Skip if the index is already present (idempotent / concurrent creation).
    644                 if ($this->indexExists($tableName, $indexName)) {
    645                     continue;
    646                 }
    647                
    648                 // If we're adding a unique key then remove the duplicates.
    649                 // This was causing issues for some people.
     619
    650620                $spellingCacheTableName = $this->dao->doTableNameReplacements('{wp_abj404_spelling_cache}');
    651                 if (strtolower($tableName) == $spellingCacheTableName) {
     621                if (strtolower($tableName) == $spellingCacheTableName && !empty($spec['unique'])) {
    652622                    $this->dao->deleteSpellingCache();
    653623                }
    654                
    655                 // Create the index.
    656                 $addStatement = $this->buildAddIndexStatement($tableName, $indexDDL);
     624
     625                $addStatement = $this->buildAddIndexStatementFromParts($tableName, $spec['name'], $spec['columns'], $spec['unique']);
    657626                $this->dao->queryAndGetResults($addStatement);
    658627                $this->logger->infoMessage("I added an index: " . $addStatement);
    … …  
    679648            // Normalize "KEY `name` (...)" / "UNIQUE KEY `name` (...)" into a form usable with "IF NOT EXISTS".
    680649            // MariaDB supports: ADD [UNIQUE] INDEX IF NOT EXISTS `name` (...)
    681             if ($supportsIfNotExists) {
    682                 $matches = [];
    683                 if (preg_match('/^(unique\\s+)?key\\s+`([^`]+)`\\s*(\\(.+\\))\\s*$/i', $indexDDL, $matches)) {
    684                     $unique = !empty($matches[1]);
    685                     $name = $matches[2];
    686                     $cols = $matches[3];
    687                     $type = $unique ? 'unique index' : 'index';
    688                     return "alter table " . $tableName . " add " . $type . " if not exists `" . $name . "` " . $cols;
     650                if ($supportsIfNotExists) {
     651                    $matches = [];
     652                    if (preg_match('/^(unique\\s+)?key\\s+`([^`]+)`\\s*(\\(.+\\))\\s*$/i', $indexDDL, $matches)) {
     653                        $unique = !empty($matches[1]);
     654                        $name = $matches[2];
     655                        $cols = $matches[3];
     656                        $type = $unique ? 'unique index' : 'index';
     657                        return "alter table " . $tableName . " add " . $type . " if not exists `" . $name . "` " . $cols;
     658                    }
     659                    if (preg_match('/^`([^`]+)`\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
     660                        $name = $matches[1];
     661                        $cols = $matches[2];
     662                        return "alter table " . $tableName . " add index if not exists `" . $name . "` " . $cols;
     663                    }
     664                }
     665
     666                // If we were given a bare index DDL like "`name` (...)", make it valid for MySQL too.
     667                if (preg_match('/^`[^`]+`\\s*\\(.+\\)\\s*$/', $indexDDL)) {
     668                    return "alter table " . $tableName . " add index " . $indexDDL;
     669                }
     670
     671                // If we were given a bare index DDL like "name (...)", make it valid too.
     672                if (preg_match('/^([A-Za-z0-9_]+)\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
     673                    $name = $matches[1];
     674                    $cols = $matches[2];
     675                    return "alter table " . $tableName . " add index `" . $name . "` " . $cols;
     676                }
     677
     678                // Fallback: use the DDL as-is (already contains KEY/UNIQUE KEY).
     679                return "alter table " . $tableName . " add " . $indexDDL;
     680            }
     681
     682        /**
     683         * Parse an index DDL line from our CREATE TABLE SQL into a structured spec.
     684         *
     685         * Accepts forms like:
     686         * - KEY `name` (`col`(190), `other`)
     687         * - UNIQUE KEY `name` (`col`)
     688         * - KEY `name` (`col`) USING BTREE
     689         *
     690         * Returns null if the line doesn't look like a KEY/UNIQUE KEY definition.
     691         *
     692         * @param string $indexDDL
     693         * @return array|null {name:string, columns:string, unique:bool}
     694         */
     695        private function parseIndexDDLToSpec($indexDDL) {
     696            $indexDDL = trim($indexDDL);
     697            $matches = [];
     698            if (!preg_match('/^(unique\\s+)?key\\s+`?([^`\\s]+)`?\\s*(\\(.+\\))\\s*(?:using\\s+\\w+)?\\s*$/i', $indexDDL, $matches)) {
     699                return null;
     700            }
     701
     702            return [
     703                'name' => $matches[2],
     704                'columns' => $matches[3],
     705                'unique' => !empty($matches[1]),
     706            ];
     707        }
     708
     709        /**
     710         * Extract index specs from a CREATE TABLE statement (plugin SQL templates).
     711         *
     712         * @param string $createTableSql
     713         * @return array<string, array{name:string, columns:string, unique:bool}> keyed by index name
     714         */
     715        private function parseIndexSpecsFromCreateTableSql($createTableSql) {
     716            if (!is_string($createTableSql) || $createTableSql === '') {
     717                return [];
     718            }
     719
     720            $matches = [];
     721            preg_match_all('/^\\s*(?:unique\\s+)?key\\s+.+?\\s*$/im', $createTableSql, $matches);
     722            $lines = $matches[0] ?? [];
     723
     724            $specsByName = [];
     725            foreach ($lines as $line) {
     726                $spec = $this->parseIndexDDLToSpec($line);
     727                if (empty($spec) || empty($spec['name'])) {
     728                    continue;
    689729                }
    690                 if (preg_match('/^`([^`]+)`\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
    691                     $name = $matches[1];
    692                     $cols = $matches[2];
    693                     return "alter table " . $tableName . " add index if not exists `" . $name . "` " . $cols;
    694                 }
     730                $specsByName[$spec['name']] = $spec;
    695731            }
    696732
    697             // Fallback: use the DDL as-is (already contains KEY/UNIQUE KEY).
    698             return "alter table " . $tableName . " add " . $indexDDL;
     733            return $specsByName;
    699734        }
    700735
    701     private function ensureLogsCompositeIndex($logsTable) {
    702         $indexName = 'idx_requested_url_timestamp';
    703         $indexDDL = "`{$indexName}` (`requested_url`(190), `timestamp`)";
    704         if ($this->indexExists($logsTable, $indexName)) {
    705             return;
    706         }
    707         $query = $this->buildAddIndexStatement($logsTable, $indexDDL);
    708         $results = $this->dao->queryAndGetResults($query);
     736        /**
     737         * Build a valid ALTER TABLE ... ADD INDEX statement from structured parts.
     738         *
     739         * @param string $tableName
     740         * @param string $indexName
     741         * @param string $columnsSql Must include surrounding parentheses, e.g. "(`a`, `b`(190))"
     742         * @param bool $unique
     743         * @return string
     744         */
     745        private function buildAddIndexStatementFromParts($tableName, $indexName, $columnsSql, $unique) {
     746            global $wpdb;
     747            $serverVersion = method_exists($wpdb, 'db_version') ? $wpdb->db_version() : '';
     748            $serverInfo = property_exists($wpdb, 'db_server_info') ? $wpdb->db_server_info : '';
     749
     750            $isMaria = stripos($serverInfo, 'mariadb') !== false || stripos($serverVersion, 'maria') !== false;
     751            $supportsIfNotExists = $isMaria && version_compare(preg_replace('/[^\d\.]/', '', $serverVersion), '10.5', '>=');
     752
     753            $indexType = $unique ? 'unique index' : 'index';
     754            $ifNotExists = $supportsIfNotExists ? ' if not exists' : '';
     755
     756            return "alter table " . $tableName . " add " . $indexType . $ifNotExists . " `" . $indexName . "` " . trim($columnsSql);
     757        }
     758
     759        private function ensureLogsCompositeIndex($logsTable, $createSqlOverride = null) {
     760            $indexName = 'idx_requested_url_timestamp';
     761            $createSql = is_string($createSqlOverride) ? $createSqlOverride : ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLogTable.sql");
     762            $specsByName = $this->parseIndexSpecsFromCreateTableSql($createSql);
     763            $spec = $specsByName[$indexName] ?? null;
     764            if (empty($spec)) {
     765                $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: index definition not found in createLogTable.sql");
     766                return;
     767            }
     768
     769            if ($this->indexExists($logsTable, $indexName)) {
     770                return;
     771            }
     772            $query = $this->buildAddIndexStatementFromParts($logsTable, $spec['name'], $spec['columns'], $spec['unique']);
     773            $results = $this->dao->queryAndGetResults($query);
    709774        if (!empty($results['last_error'])) {
    710             $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: " . $results['last_error']);
     775            $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: " . $results['last_error'] . " (query: {$query})");
    711776        } else {
    712777            $this->logger->infoMessage("Added {$indexName} to {$logsTable} using query: {$query}");

  • 404-solution/tags/3.1.7/readme.txt

    r3423238 r3423944  
    66Requires PHP: 7.4
    77Tested up to: 6.9
    8 Stable tag: 3.1.6
     8Stable tag: 3.1.7
    99License: GPL-3.0-or-later
    1010License URI: https://www.gnu.org/licenses/gpl-3.0.html
    … …  
    237237
    238238== Changelog ==
     239
     240= Version 3.1.7 (Dec 19, 2025) =
     241* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     242* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
    239243
    240244= Version 3.1.6 (Dec 18, 2025) =

  • 404-solution/trunk/404-solution.php

    r3423238 r3423944  
    88    Author URI:  https://www.ajexperience.com/404-solution/
    99
    10         Version: 3.1.6
     10        Version: 3.1.7
    1111    Requires at least: 5.0
    1212    Requires PHP: 7.4

  • 404-solution/trunk/CHANGELOG.md

    r3423238 r3423944  
    11# Changelog #
     2
     3## Version 3.1.7 (Dec 19, 2025) ##
     4* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     5* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
    26
    37## Version 3.1.6 (Dec 18, 2025) ##

  • 404-solution/trunk/README.md

    r3423238 r3423944  
    8181## Changelog ##
    8282
     83## Version 3.1.7 (Dec 19, 2025) ##
     84* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     85* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
     86
    8387## Version 3.1.6 (Dec 18, 2025) ##
    8488* FIX: Redirects table pagination/search no longer fails on some MariaDB versions with a SQL syntax error while updating the table.

  • 404-solution/trunk/includes/DatabaseUpgradesEtc.php

    r3423122 r3423944  
    597597            // get the indexes.
    598598            // Pattern matches lines starting with "KEY" / "UNIQUE KEY" - handles composite indexes with commas inside parens
    599             $existingTableMatches = null;
    600             $goalTableMatches = null;
    601             preg_match_all('/^\s*((?:unique\s+)?key\s+.+?)\s*,?\s*$/im', $existingTableSQL, $existingTableMatches);
    602             preg_match_all('/^\s*((?:unique\s+)?key\s+.+?)\s*,?\s*$/im', $createTableStatementGoal, $goalTableMatches);
    603            
    604             $extractIndexName = function($indexDDL) {
    605                 $matches = null;
    606                 // Matches: KEY `name` (...) or UNIQUE KEY `name` (...)
    607                 preg_match('/\bkey\b\s+`([^`]+)`/i', $indexDDL, $matches);
    608                 return $matches[1] ?? null;
    609             };
    610            
    611             $goalTableMatchesColumnDDL = $goalTableMatches[1] ?? [];
    612             $existingTableMatchesColumnDDL = $existingTableMatches[1] ?? [];
    613            
    614             $goalIndexesByName = [];
    615             foreach ($goalTableMatchesColumnDDL as $indexDDL) {
    616                 $indexName = $extractIndexName($indexDDL);
    617                 if (!empty($indexName)) {
    618                     $goalIndexesByName[$indexName] = $indexDDL;
     599            // Indexes: treat the CREATE TABLE SQL as source of truth, and treat the database as truth
     600            // for what exists (SHOW INDEX). Avoid parsing SHOW CREATE TABLE output, which is vendor/format dependent.
     601            $goalSpecsByName = $this->parseIndexSpecsFromCreateTableSql($createTableStatementGoal);
     602
     603            $missingIndexNames = [];
     604            foreach (array_keys($goalSpecsByName) as $indexName) {
     605                if (!$this->indexExists($tableName, $indexName)) {
     606                    $missingIndexNames[] = $indexName;
    619607                }
    620608            }
    621            
    622             $existingIndexNames = [];
    623             foreach ($existingTableMatchesColumnDDL as $indexDDL) {
    624                 $indexName = $extractIndexName($indexDDL);
    625                 if (!empty($indexName)) {
    626                     $existingIndexNames[$indexName] = true;
    627                 }
    628             }
    629            
    630             // Compare by index name to avoid false positives from harmless formatting differences (e.g., "USING BTREE").
    631             $missingIndexNames = array_diff(array_keys($goalIndexesByName), array_keys($existingIndexNames));
    632            
    633             // say why we're doing what we're doing.
     609
    634610            if (count($missingIndexNames) > 0) {
    635611                $this->logger->infoMessage(self::$uniqID . ": On {$tableName} I'm adding missing indexes: " . implode(', ', $missingIndexNames));
    636612            }
    637            
     613
    638614            foreach ($missingIndexNames as $indexName) {
    639                 $indexDDL = $goalIndexesByName[$indexName] ?? null;
    640                 if (empty($indexDDL)) {
     615                $spec = $goalSpecsByName[$indexName] ?? null;
     616                if (empty($spec)) {
    641617                    continue;
    642618                }
    643                 // Skip if the index is already present (idempotent / concurrent creation).
    644                 if ($this->indexExists($tableName, $indexName)) {
    645                     continue;
    646                 }
    647                
    648                 // If we're adding a unique key then remove the duplicates.
    649                 // This was causing issues for some people.
     619
    650620                $spellingCacheTableName = $this->dao->doTableNameReplacements('{wp_abj404_spelling_cache}');
    651                 if (strtolower($tableName) == $spellingCacheTableName) {
     621                if (strtolower($tableName) == $spellingCacheTableName && !empty($spec['unique'])) {
    652622                    $this->dao->deleteSpellingCache();
    653623                }
    654                
    655                 // Create the index.
    656                 $addStatement = $this->buildAddIndexStatement($tableName, $indexDDL);
     624
     625                $addStatement = $this->buildAddIndexStatementFromParts($tableName, $spec['name'], $spec['columns'], $spec['unique']);
    657626                $this->dao->queryAndGetResults($addStatement);
    658627                $this->logger->infoMessage("I added an index: " . $addStatement);
    … …  
    679648            // Normalize "KEY `name` (...)" / "UNIQUE KEY `name` (...)" into a form usable with "IF NOT EXISTS".
    680649            // MariaDB supports: ADD [UNIQUE] INDEX IF NOT EXISTS `name` (...)
    681             if ($supportsIfNotExists) {
    682                 $matches = [];
    683                 if (preg_match('/^(unique\\s+)?key\\s+`([^`]+)`\\s*(\\(.+\\))\\s*$/i', $indexDDL, $matches)) {
    684                     $unique = !empty($matches[1]);
    685                     $name = $matches[2];
    686                     $cols = $matches[3];
    687                     $type = $unique ? 'unique index' : 'index';
    688                     return "alter table " . $tableName . " add " . $type . " if not exists `" . $name . "` " . $cols;
     650                if ($supportsIfNotExists) {
     651                    $matches = [];
     652                    if (preg_match('/^(unique\\s+)?key\\s+`([^`]+)`\\s*(\\(.+\\))\\s*$/i', $indexDDL, $matches)) {
     653                        $unique = !empty($matches[1]);
     654                        $name = $matches[2];
     655                        $cols = $matches[3];
     656                        $type = $unique ? 'unique index' : 'index';
     657                        return "alter table " . $tableName . " add " . $type . " if not exists `" . $name . "` " . $cols;
     658                    }
     659                    if (preg_match('/^`([^`]+)`\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
     660                        $name = $matches[1];
     661                        $cols = $matches[2];
     662                        return "alter table " . $tableName . " add index if not exists `" . $name . "` " . $cols;
     663                    }
     664                }
     665
     666                // If we were given a bare index DDL like "`name` (...)", make it valid for MySQL too.
     667                if (preg_match('/^`[^`]+`\\s*\\(.+\\)\\s*$/', $indexDDL)) {
     668                    return "alter table " . $tableName . " add index " . $indexDDL;
     669                }
     670
     671                // If we were given a bare index DDL like "name (...)", make it valid too.
     672                if (preg_match('/^([A-Za-z0-9_]+)\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
     673                    $name = $matches[1];
     674                    $cols = $matches[2];
     675                    return "alter table " . $tableName . " add index `" . $name . "` " . $cols;
     676                }
     677
     678                // Fallback: use the DDL as-is (already contains KEY/UNIQUE KEY).
     679                return "alter table " . $tableName . " add " . $indexDDL;
     680            }
     681
     682        /**
     683         * Parse an index DDL line from our CREATE TABLE SQL into a structured spec.
     684         *
     685         * Accepts forms like:
     686         * - KEY `name` (`col`(190), `other`)
     687         * - UNIQUE KEY `name` (`col`)
     688         * - KEY `name` (`col`) USING BTREE
     689         *
     690         * Returns null if the line doesn't look like a KEY/UNIQUE KEY definition.
     691         *
     692         * @param string $indexDDL
     693         * @return array|null {name:string, columns:string, unique:bool}
     694         */
     695        private function parseIndexDDLToSpec($indexDDL) {
     696            $indexDDL = trim($indexDDL);
     697            $matches = [];
     698            if (!preg_match('/^(unique\\s+)?key\\s+`?([^`\\s]+)`?\\s*(\\(.+\\))\\s*(?:using\\s+\\w+)?\\s*$/i', $indexDDL, $matches)) {
     699                return null;
     700            }
     701
     702            return [
     703                'name' => $matches[2],
     704                'columns' => $matches[3],
     705                'unique' => !empty($matches[1]),
     706            ];
     707        }
     708
     709        /**
     710         * Extract index specs from a CREATE TABLE statement (plugin SQL templates).
     711         *
     712         * @param string $createTableSql
     713         * @return array<string, array{name:string, columns:string, unique:bool}> keyed by index name
     714         */
     715        private function parseIndexSpecsFromCreateTableSql($createTableSql) {
     716            if (!is_string($createTableSql) || $createTableSql === '') {
     717                return [];
     718            }
     719
     720            $matches = [];
     721            preg_match_all('/^\\s*(?:unique\\s+)?key\\s+.+?\\s*$/im', $createTableSql, $matches);
     722            $lines = $matches[0] ?? [];
     723
     724            $specsByName = [];
     725            foreach ($lines as $line) {
     726                $spec = $this->parseIndexDDLToSpec($line);
     727                if (empty($spec) || empty($spec['name'])) {
     728                    continue;
    689729                }
    690                 if (preg_match('/^`([^`]+)`\\s*(\\(.+\\))\\s*$/', $indexDDL, $matches)) {
    691                     $name = $matches[1];
    692                     $cols = $matches[2];
    693                     return "alter table " . $tableName . " add index if not exists `" . $name . "` " . $cols;
    694                 }
     730                $specsByName[$spec['name']] = $spec;
    695731            }
    696732
    697             // Fallback: use the DDL as-is (already contains KEY/UNIQUE KEY).
    698             return "alter table " . $tableName . " add " . $indexDDL;
     733            return $specsByName;
    699734        }
    700735
    701     private function ensureLogsCompositeIndex($logsTable) {
    702         $indexName = 'idx_requested_url_timestamp';
    703         $indexDDL = "`{$indexName}` (`requested_url`(190), `timestamp`)";
    704         if ($this->indexExists($logsTable, $indexName)) {
    705             return;
    706         }
    707         $query = $this->buildAddIndexStatement($logsTable, $indexDDL);
    708         $results = $this->dao->queryAndGetResults($query);
     736        /**
     737         * Build a valid ALTER TABLE ... ADD INDEX statement from structured parts.
     738         *
     739         * @param string $tableName
     740         * @param string $indexName
     741         * @param string $columnsSql Must include surrounding parentheses, e.g. "(`a`, `b`(190))"
     742         * @param bool $unique
     743         * @return string
     744         */
     745        private function buildAddIndexStatementFromParts($tableName, $indexName, $columnsSql, $unique) {
     746            global $wpdb;
     747            $serverVersion = method_exists($wpdb, 'db_version') ? $wpdb->db_version() : '';
     748            $serverInfo = property_exists($wpdb, 'db_server_info') ? $wpdb->db_server_info : '';
     749
     750            $isMaria = stripos($serverInfo, 'mariadb') !== false || stripos($serverVersion, 'maria') !== false;
     751            $supportsIfNotExists = $isMaria && version_compare(preg_replace('/[^\d\.]/', '', $serverVersion), '10.5', '>=');
     752
     753            $indexType = $unique ? 'unique index' : 'index';
     754            $ifNotExists = $supportsIfNotExists ? ' if not exists' : '';
     755
     756            return "alter table " . $tableName . " add " . $indexType . $ifNotExists . " `" . $indexName . "` " . trim($columnsSql);
     757        }
     758
     759        private function ensureLogsCompositeIndex($logsTable, $createSqlOverride = null) {
     760            $indexName = 'idx_requested_url_timestamp';
     761            $createSql = is_string($createSqlOverride) ? $createSqlOverride : ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLogTable.sql");
     762            $specsByName = $this->parseIndexSpecsFromCreateTableSql($createSql);
     763            $spec = $specsByName[$indexName] ?? null;
     764            if (empty($spec)) {
     765                $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: index definition not found in createLogTable.sql");
     766                return;
     767            }
     768
     769            if ($this->indexExists($logsTable, $indexName)) {
     770                return;
     771            }
     772            $query = $this->buildAddIndexStatementFromParts($logsTable, $spec['name'], $spec['columns'], $spec['unique']);
     773            $results = $this->dao->queryAndGetResults($query);
    709774        if (!empty($results['last_error'])) {
    710             $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: " . $results['last_error']);
     775            $this->logger->errorMessage("Failed to add {$indexName} to {$logsTable}: " . $results['last_error'] . " (query: {$query})");
    711776        } else {
    712777            $this->logger->infoMessage("Added {$indexName} to {$logsTable} using query: {$query}");

  • 404-solution/trunk/readme.txt

    r3423238 r3423944  
    66Requires PHP: 7.4
    77Tested up to: 6.9
    8 Stable tag: 3.1.6
     8Stable tag: 3.1.7
    99License: GPL-3.0-or-later
    1010License URI: https://www.gnu.org/licenses/gpl-3.0.html
    … …  
    237237
    238238== Changelog ==
     239
     240= Version 3.1.7 (Dec 19, 2025) =
     241* FIX: Prevent invalid SQL during missing-index creation by parsing index definitions from the plugin SQL templates and emitting structured `ALTER TABLE ... ADD INDEX ...` statements.
     242* Test: Add regression coverage for parsing log table composite index definitions and for missing index definitions in SQL templates.
    239243
    240244= Version 3.1.6 (Dec 18, 2025) =
Note: See TracChangeset for help on using the changeset viewer.