[TikiWiki-commits] [Git][tikiwiki/tiki][27.x] [BP][FIX] Duplicate key collision in sefurl migrations

ushindi bienvenu pushed to branch 27.x at Tiki Wiki CMS Groupware / Tiki


Commits:
dae6ba15 by ushindi bienvenu at 2026-05-23T23:08:25+00:00
[BP][FIX] Duplicate key collision in sefurl migrations
---
* [BP][FIX] Duplicate key collision in sefurl migrations
---
* [BP][FIX] Duplicate key collision in sefurl migrations
---
* [FIX] duplicate key collision in sefurl migrations
---
* [FIX] restore dl for file type and refactor sefurl migrations

* [FIX] duplicate key collision in sefurl migrations

See merge request tikiwiki/tiki!10069

See merge request tikiwiki/tiki!10281

See merge request tikiwiki/tiki!10282

See merge request tikiwiki/tiki!10360

- - - - -


10 changed files:

- db/tiki-secdb_27.6_mysql.sql
- db/tiki.sql
- doc/devtools/release.php
- installer/schema/20180921_php_upgrade_fix_from_older_tiki.php
- installer/schema/20180921_sql_upgrade_fix_from_older_tiki.php
- installer/schema/20180923_convert_collation_to_utf8mb4_tiki.php
- installer/schema/20260123_update_right_column_for_file_type_in_sefurl_tiki.sql
- + installer/schema/20260212_restore_right_dl_for_file_type_in_sefurl_tiki.sql
- installer/schema/20260212_update_regex_right_column_for_file_type_in_sefurl_tiki.sql
- + installer/schema/20260524_increase_filename_len_in_pk_tiki.sql


Changes:

=====================================
db/tiki-secdb_27.6_mysql.sql
=====================================
@@ -1,6 +1,6 @@
 start transaction;
 DELETE FROM `tiki_secdb`;
-ALTER TABLE `tiki_secdb` DROP PRIMARY KEY;
+ALTER TABLE `tiki_secdb` DROP PRIMARY KEY, ADD PRIMARY KEY (`filename`(186),`tiki_version`(5));
 
 INSERT INTO `tiki_secdb` (`filename`, `md5_value`, `tiki_version`) VALUES 
 ('./.phpstorm.meta.php', 'c94a2803140338162737442f39b534bf', '27.6'),


=====================================
db/tiki.sql
=====================================
@@ -2214,7 +2214,7 @@ CREATE TABLE tiki_secdb(
   `filename` varchar(250) NOT NULL,
   `tiki_version` varchar(60) NOT NULL,
   `severity` int(4) NOT NULL default '0',
-  PRIMARY KEY (`filename`(171),`tiki_version`(20)),
+  PRIMARY KEY (`filename`(186),`tiki_version`(5)),
   KEY `sdb_fn` (filename(191))
 ) ENGINE=MyISAM;
 
@@ -3437,7 +3437,7 @@ INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES(
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-view_blog_post.php\\?postId=(\\d+)', 'blogpost$1', 'blogpost', 'feature_blogs');
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-directory_browse.php\\?parent=(\\d+)', 'directory$1', 'directory', 'feature_directory');
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-view_faq.php\\?faqId=(\\d+)', 'faq$1', 'faq', 'feature_faqs');
-INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`, `order`) VALUES('tiki-download_file.php\\?fileId=(\\d+)', 'display$1', 'file', 'feature_file_galleries', 10);
+INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`, `order`) VALUES('tiki-download_file.php\\?fileId=(\\d+)', 'dl$1', 'file', 'feature_file_galleries', 10);
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-download_file.php\\?fileId=(\\d+)(?:&|&)thumbnail(?:=[^&]*)?(?=(?:&|&|$))', 'thumbnail$1', 'thumbnail', 'feature_file_galleries');
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-download_file.php\\?fileId=(\\d+)(?:&|&)display(?:=[^&]*)?(?=(?:&|&|$))', 'display$1', 'display', 'feature_file_galleries');
 INSERT INTO `tiki_sefurl_regex_out` (`left`, `right`, `type`, `feature`) VALUES('tiki-download_file.php\\?fileId=(\\d+)(?:&|&)preview(?:=[^&]*)?(?=(?:&|&|$))', 'preview$1', 'preview', 'feature_file_galleries');


=====================================
doc/devtools/release.php
=====================================
@@ -301,23 +301,10 @@ function updateSecdb($version)
         sort($queries);
         fwrite($fp, "start transaction;\n");
         fwrite($fp, "DELETE FROM `tiki_secdb`;\n");
-        // Do not recreate a PRIMARY KEY on (filename, tiki_version).
-        //
-        // Historically this table used:
-        //
-        //   PRIMARY KEY (`filename`(171), `tiki_version`(20))
-        //
-        // The key only indexed a prefix of `filename`, not the full path. This caused
-        // false duplicate-key errors when different long paths shared the same first
-        // 171 characters, especially in bundled vendor/test fixtures.
-        //
-        // Increasing the prefix length is not a reliable fix because older MySQL/MyISAM
-        // versions can hit index-size limits.
-        //
-        // Since tiki_secdb is fully generated from release files (not user data),
-        // enforcing uniqueness through a truncated filename prefix is more harmful
-        // than useful. A normal filename index remains sufficient for lookups.
-        fwrite($fp, "ALTER TABLE `tiki_secdb` DROP PRIMARY KEY;\n\n");
+        // This index was originally created with a size limit that would raise an error on some versions,
+        // notably on 18.0. Since this file is executed before any patch in installer/schema, the fix had to
+        // be done here. It's a quick operation because table is empty, so no harm in leaving this here forever.
+        fwrite($fp, "ALTER TABLE `tiki_secdb` DROP PRIMARY KEY, ADD PRIMARY KEY (`filename`(186),`tiki_version`(5));\n\n");
 
         $insertString = 'INSERT INTO `tiki_secdb` (`filename`, `md5_value`, `tiki_version`) VALUES ';
 


=====================================
installer/schema/20180921_php_upgrade_fix_from_older_tiki.php
=====================================
@@ -9,6 +9,10 @@
  */
 function upgrade_20180921_php_upgrade_fix_from_older_tiki($installer)
 {
+    // Fix upgrade from 9.x
+    $installer->query('ALTER TABLE `tiki_secdb` DROP PRIMARY KEY;'); // it might not be set
+    $installer->query('ALTER TABLE `tiki_secdb` ADD PRIMARY KEY (`filename`(215),`tiki_version`(40));');
+
     // Fix upgrade from 12.x
     $tablesToRename = [
         'metrics_assigned',


=====================================
installer/schema/20180921_sql_upgrade_fix_from_older_tiki.php
=====================================
@@ -14,6 +14,8 @@ function upgrade_20180921_sql_upgrade_fix_from_older_tiki($installer)
 ALTER TABLE `tiki_forums_reported` DROP PRIMARY KEY;
 ALTER TABLE `tiki_forums_reported` ADD PRIMARY KEY (`threadId`, `forumId`, `parentId`, `user`); -- Changed in 20121210_better_forum_reported_index_tiki.sql but never make it to tiki.sql
 DELETE FROM `users_permissions` WHERE `permName` = 'tiki_p_view_poll_choices';
+ALTER TABLE `tiki_secdb` DROP PRIMARY KEY;
+ALTER TABLE `tiki_secdb` ADD PRIMARY KEY (`filename`(215),`tiki_version`(40));
 UPDATE `tiki_modules` SET `position` = '' WHERE `position` IS NULL;
 ALTER TABLE `tiki_modules` CHANGE `position` `position` varchar(20) NOT NULL DEFAULT '';
 ALTER TABLE `tiki_profile_symbols` CHANGE `value` `value` varchar(160) NOT NULL;


=====================================
installer/schema/20180923_convert_collation_to_utf8mb4_tiki.php
=====================================
@@ -296,6 +296,8 @@ ALTER TABLE `tiki_semaphores` DROP PRIMARY KEY;
 ALTER TABLE `tiki_semaphores` ADD PRIMARY KEY (`semName`(191));
 ALTER TABLE `tiki_secdb` DROP INDEX `sdb_fn`;
 ALTER TABLE `tiki_secdb` ADD KEY `sdb_fn` (`filename`(191));
+ALTER TABLE `tiki_secdb` DROP PRIMARY KEY;
+ALTER TABLE `tiki_secdb` ADD PRIMARY KEY (`filename`(171),`tiki_version`(20));
 ALTER TABLE `tiki_score` DROP PRIMARY KEY;
 ALTER TABLE `tiki_score` ADD PRIMARY KEY (`event`(191));
 ALTER TABLE `tiki_rss_items` DROP INDEX `tiki_rss_items_item`;


=====================================
installer/schema/20260123_update_right_column_for_file_type_in_sefurl_tiki.sql
=====================================
@@ -1,6 +1,8 @@
 UPDATE `tiki_sefurl_regex_out`
 SET `left` = 'tiki-download_file.php\\?fileId=(\\d+)(?:&|&)display'
-WHERE `right` = 'display$1' AND `feature` = 'feature_file_galleries';
+WHERE `right` = 'display$1'
+  AND `feature` = 'feature_file_galleries'
+  AND `type` = 'display';
 
 
 UPDATE `tiki_sefurl_regex_out`


=====================================
installer/schema/20260212_restore_right_dl_for_file_type_in_sefurl_tiki.sql
=====================================
@@ -0,0 +1,5 @@
+UPDATE `tiki_sefurl_regex_out`
+SET `right` = 'dl$1'
+WHERE `left` = 'tiki-download_file.php\\?fileId=(\\d+)'
+  AND `feature` = 'feature_file_galleries'
+  AND `type` = 'file';


=====================================
installer/schema/20260212_update_regex_right_column_for_file_type_in_sefurl_tiki.sql
=====================================
@@ -1,7 +1,8 @@
 UPDATE `tiki_sefurl_regex_out`
 SET `left` = 'tiki-download_file.php\\?fileId=(\\d+)(?:&|&)display(?:=[^&]*)?(?=(?:&|&|$))'
-WHERE `right` = 'display$1' AND `feature` = 'feature_file_galleries';
-
+WHERE `right` = 'display$1'
+  AND `feature` = 'feature_file_galleries'
+  AND `type` = 'display';
 
 UPDATE `tiki_sefurl_regex_out`
 SET `left` = 'tiki-download_file.php\\?fileId=(\\d+)(?:&|&)thumbnail(?:=[^&]*)?(?=(?:&|&|$))'


=====================================
installer/schema/20260524_increase_filename_len_in_pk_tiki.sql
=====================================
@@ -0,0 +1,3 @@
+ALTER TABLE `tiki_secdb`
+DROP PRIMARY KEY,
+ADD PRIMARY KEY (`filename`(186), `tiki_version`(5));
\ No newline at end of file



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

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/dae6ba152fd977fab426c455678dcfda1e79c341
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