[TikiWiki-commits] [Git][tikiwiki/tiki][master] [NEW] Add syntax checker for SQL field format type mismatches

Victor Emanouilov pushed to branch master at Tiki Wiki CMS Groupware / Tiki


Commits:
3d8e24ee by Moïse Nturubika at 2026-05-12T15:44:04+00:00
[NEW] Add syntax checker for SQL field format type mismatches
---
* [FIX] Revert some index in ignore list

* [FIX] Remove redundant idx_slvn_commentDate index and update installer warning logic

* [FIX] Installer: ignore deprecated integer width warnings for historical patches

* [REF] Simplify MySQL warning handling and resolve recursion

* [REF] Installer: Implement patch-specific MySQL warning threshold and custom exception

* [FIX] Installer: Whitelist InnoDB rebuild alerts and remove the test patch

* [FIX] Installer: Skip common MySQL warnings from old patches

* [FIX] Installer: Document and filter legacy MySQL 8+ deprecation warnings

* [FIX] Installer: Ensure MySQL warning exceptions bubble up to CI logs

* [DB] [TEST] Add dummy patch to verify CI warning capture failure

* [ENH] CI: Implement MySQL warning detection during database upgrades

* FIX: address PHPCS linting errors in check_schema_field_format.php

* NEW: Add syntax checker for SQL field format type mismatches

See merge request tikiwiki/tiki!9660

- - - - -


6 changed files:

- .gitlab-ci.yml
- db/tiki.sql
- installer/Installer.php
- + installer/MySQLWarningException.php
- installer/schema/20250415_since_last_visit_new_indexes_tiki.sql
- + installer/schema/20260509_cleanup_duplicate_indexes_tiki.php


Changes:

=====================================
.gitlab-ci.yml
=====================================
@@ -440,6 +440,7 @@ composer-could-update-lock:
     - echo "GRANT ALL ON tikinew.* TO '${MYSQL_USER}';" | mysql -u root --password=$MYSQL_ROOT_PASSWORD -h mysql
     - '[ ! -d doc/devtools/dbdiff/cache ] && mkdir doc/devtools/dbdiff/cache'
     - '[ ! -f doc/devtools/dbdiff/cache/$DBFILE ] && curl -sS https://gitlab.com/tikiwiki/tikiwiki-ci-databases/raw/master/$DBFILE.gz -o doc/devtools/dbdiff/cache/$DBFILE.gz && gzip -d doc/devtools/dbdiff/cache/$DBFILE.gz'
+    - export TIKI_CATCH_UPDATE_WARNINGS=1
     - php -d display_errors=On doc/devtools/check_schema_upgrade.php -m $DBVER -e $ENGINE --db1=$MYSQL_USER:$MYSQL_PASSWORD@mysql:tikiold --db2=$MYSQL_USER:$MYSQL_PASSWORD@mysql:tikinew
   after_script:
     - echo "SHOW CREATE DATABASE tikiold" | mysql -u root --password=$MYSQL_ROOT_PASSWORD -h mysql


=====================================
db/tiki.sql
=====================================
@@ -732,8 +732,7 @@ CREATE TABLE `tiki_comments` (
   KEY `tc_pi` (`parentId`),
   KEY `objectType` (object(160), `objectType`),
   KEY `commentDate` (`commentDate`),
-  KEY `threaded` (message_id(40), in_reply_to(40), `parentId`),
-  KEY `idx_slvn_commentDate` (`commentDate`)
+  KEY `threaded` (message_id(40), in_reply_to(40), `parentId`)
 ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
 
 DROP TABLE IF EXISTS `tiki_content`;
@@ -1406,7 +1405,6 @@ CREATE TABLE `tiki_link_cache` (
   PRIMARY KEY (`cacheId`),
   KEY `url` (url(191))
 ) ENGINE=MyISAM AUTO_INCREMENT=1 ;
-CREATE INDEX urlindex ON tiki_link_cache (url(191));
 
 DROP TABLE IF EXISTS `tiki_links`;
 CREATE TABLE `tiki_links` (


=====================================
installer/Installer.php
=====================================
@@ -27,6 +27,8 @@ class Installer extends TikiDb_Bridge implements SplSubject
     public $scripts = [];
     public $executed = [];
 
+    private string $currentPatchName = '';
+
     public $queries = [
         'currentStmt' => '',
         'currentFile' => '',
@@ -34,7 +36,8 @@ class Installer extends TikiDb_Bridge implements SplSubject
         'total' => 0,
         'files' => [], //path of the files executed
         'successful' => [],
-        'failed' => []
+        'failed' => [],
+        'warnings' => []
     ];
 
     public $useInnoDB = true;
@@ -140,6 +143,8 @@ class Installer extends TikiDb_Bridge implements SplSubject
         foreach (Patch::getPatches([Patch::NOT_APPLIED]) as $patchName => $patch) {
             try {
                 $this->installPatch($patchName);
+            } catch (MySQLWarningException $e) {
+                throw $e;
             } catch (Exception $e) {
                 if ($e->getCode() != 2) {
                     throw $e;
@@ -163,6 +168,7 @@ class Installer extends TikiDb_Bridge implements SplSubject
      */
     public function installPatch($patch, $force = false)
     {
+        $this->currentPatchName = (string) $patch;
         if (! $force && isset(Patch::$list[$patch]) && Patch::$list[$patch]->isApplied()) {
             throw new Exception('Patch already applied', 3);
         }
@@ -202,7 +208,8 @@ class Installer extends TikiDb_Bridge implements SplSubject
             } else {
                 try {
                     $status = $this->runFile($schema);
-                } catch (Exception $e) {
+                } catch (MySQLWarningException $e) {
+                    throw $e;
                 }
             }
 
@@ -222,6 +229,7 @@ class Installer extends TikiDb_Bridge implements SplSubject
         } else {
             Patch::$list[$patch]->record();
         }
+        $this->currentPatchName = '';
     }
 
     /**
@@ -349,12 +357,48 @@ class Installer extends TikiDb_Bridge implements SplSubject
         $error = '';
         $result = $this->queryError($query, $error, $values);
 
+        if (stripos(trim($query), 'SHOW WARNINGS') === 0) {
+            return $result;
+        }
+
+        $isCI = ! empty($_ENV['TIKI_CATCH_UPDATE_WARNINGS']) || getenv('TIKI_CATCH_UPDATE_WARNINGS');
+        $warnings = [];
+        $warnResult = self::get()->query('SHOW WARNINGS', reporterrors: \TikiDb::ERR_NONE);
+
+        if ($warnResult) {
+            while ($row = $warnResult->fetchRow()) {
+                if ($row['Level'] === 'Warning' || $row['Level'] === 'Error') {
+                    $code = (int) $row['Code'];
+                    // Historical patches are before 2026-04-17. Base schema (empty name) is also historical.
+                    $isHistorical = empty($this->currentPatchName) || substr($this->currentPatchName, 0, 8) < '20260417';
+
+                    if (($code === 1681 || $code === 124) && $isHistorical) {
+                        continue;
+                    }
+
+                    $warnings[] = $row['Level'] . ' ' . $row['Code'] . ': ' . $row['Message'];
+                }
+            }
+        }
+
         if ($result && empty($error)) {
+            if ($isCI && ! empty($warnings)) {
+                throw new MySQLWarningException("MySQL Warning(s) caught during upgrade in query:\n$query\nWarnings:\n" . implode("\n", $warnings));
+            }
+
+            if (! empty($warnings)) {
+                $this->queries['warnings'][] = ['query' => $query, 'warnings' => $warnings];
+            }
+
             if ($countQueries) {
                 $this->queries['successful'][] = $query;
             }
             return $result;
         } else {
+            if ($isCI && ! empty($warnings)) {
+                throw new MySQLWarningException("MySQL Error/Warning(s) caught during upgrade in query:\n$query\nDetails:\n" . implode("\n", $warnings));
+            }
+
             if ($countQueries) {
                 $this->queries['failed'][] = [$query, $error, substr(basename($patch), 0, -4)];
             }


=====================================
installer/MySQLWarningException.php
=====================================
@@ -0,0 +1,17 @@
+<?php
+
+// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project
+//
+// All Rights Reserved. See copyright.txt for details and a complete list of authors.
+// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
+namespace Tiki\Installer;
+
+use Exception;
+
+/**
+ * Exception thrown when MySQL warnings are caught during database upgrade and
+ * stricter checks are enabled (e.g. in CI environments).
+ */
+class MySQLWarningException extends Exception
+{
+}


=====================================
installer/schema/20250415_since_last_visit_new_indexes_tiki.sql
=====================================
@@ -1,4 +1,3 @@
-ALTER TABLE `tiki_comments` ADD INDEX `idx_slvn_commentDate` (`commentDate`);
 ALTER TABLE `tiki_articles` ADD INDEX `idx_slvn_created` (`created`);
 ALTER TABLE `tiki_calendars` ADD INDEX `idx_slvn_created` (`created`);
 ALTER TABLE `tiki_tracker_items` ADD INDEX `idx_slvn_created` (`created`);


=====================================
installer/schema/20260509_cleanup_duplicate_indexes_tiki.php
=====================================
@@ -0,0 +1,19 @@
+<?php
+
+// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project
+//
+// All Rights Reserved. See copyright.txt for details and a complete list of authors.
+// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
+
+function upgrade_20260509_cleanup_duplicate_indexes_tiki($installer)
+{
+    $indexExists = $installer->fetchAll("SHOW INDEX FROM `tiki_comments` WHERE Key_name = 'idx_slvn_commentDate'");
+    if (! empty($indexExists)) {
+        $installer->query("ALTER TABLE `tiki_comments` DROP INDEX `idx_slvn_commentDate`", [], -1, -1, false);
+    }
+
+    $indexExists = $installer->fetchAll("SHOW INDEX FROM `tiki_link_cache` WHERE Key_name = 'urlindex'");
+    if (! empty($indexExists)) {
+        $installer->query("ALTER TABLE `tiki_link_cache` DROP INDEX `urlindex`", [], -1, -1, false);
+    }
+}



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/3d8e24ee799ead75900e09b071b5c5c5d2b96132

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/3d8e24ee799ead75900e09b071b5c5c5d2b96132
You're receiving this email because of your account on gitlab.com. Manage all notifications: https://gitlab.com/-/profile/notifications | Help: https://gitlab.com/help

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs