Zarafa Collaboration Platform
  1. Zarafa Collaboration Platform
  2. ZCP-9130

The zarafa7-upgrade script creates the wrong primary index on the tproperties table

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Highest Highest
    • Resolution: Fixed
    • Affects Version/s: 7.0.3, 7.1.0
    • Fix Version/s: 7.1.0
    • Component/s: installer
    • Security Level: Public (Everyone)
    • Labels:
      None
    • Severity:
      Critical
    • Similar issues:
      ZCP-9138The zarafa7-upgrade script creates the wrong primary index on the tproperties table
      ZCP-11116MySQL wrongly optimizes tproperties table queries
      ZCP-5193Wrong primary index in the outgoingqueue table

      Description

      Server generated index (correct):

      PRIMARY KEY `ht` (`folderid`,`tag`,`hierarchyid`,`type`)

      Script generated index (wrong):

      PRIMARY KEY `ht` (`folderid`,`tag`,`type`,`hierarchyid`)

        Gliffy Diagrams

          Activity

          Hide
          Steve Hardy added a comment -

          online upgrade fix:

          [15:10] <Sharky--> begin;
          [15:10] <Sharky--> truncate table tproperties;
          [15:10] <Sharky--> alter table tproperties drop primary key, add primary key (folderid, tag,hierarchyid,type);
          [15:10] <Sharky--> INSERT IGNORE INTO deferredupdate (hierarchyid, folderid) SELECT c.id,c.parent FROM hierarchy AS c JOIN hierarchy AS p ON c.parent = p.id AND p.type=3;
          [15:10] <Sharky--> commit;
          [15:11] <Sharky--> zarafa-admin --purge-deferred
          
          Show
          Steve Hardy added a comment - online upgrade fix: [15:10] <Sharky--> begin; [15:10] <Sharky--> truncate table tproperties; [15:10] <Sharky--> alter table tproperties drop primary key, add primary key (folderid, tag,hierarchyid,type); [15:10] <Sharky--> INSERT IGNORE INTO deferredupdate (hierarchyid, folderid) SELECT c.id,c.parent FROM hierarchy AS c JOIN hierarchy AS p ON c.parent = p.id AND p.type=3; [15:10] <Sharky--> commit; [15:11] <Sharky--> zarafa-admin --purge-deferred
          Hide
          Robin van den Kerkhoff added a comment -

          tested on 7.1.0-31203 Works.
          table properties:
          mysql> show index from tproperties;
          -------------------------------------------------------------------------------------------------------------+

          Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

          -------------------------------------------------------------------------------------------------------------+

          tproperties 0 PRIMARY 1 folderid A 9625 NULL NULL   BTREE  
          tproperties 0 PRIMARY 2 tag A 9625 NULL NULL   BTREE  
          tproperties 0 PRIMARY 3 hierarchyid A 9625 NULL NULL   BTREE  
          tproperties 0 PRIMARY 4 type A 9625 NULL NULL   BTREE  
          tproperties 1 hi 1 hierarchyid A 9625 NULL NULL   BTREE  

          -------------------------------------------------------------------------------------------------------------+
          5 rows in set (0.00 sec)

          Show
          Robin van den Kerkhoff added a comment - tested on 7.1.0-31203 Works. table properties: mysql> show index from tproperties; ------------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- --------+ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment ------------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- --------+ tproperties 0 PRIMARY 1 folderid A 9625 NULL NULL   BTREE   tproperties 0 PRIMARY 2 tag A 9625 NULL NULL   BTREE   tproperties 0 PRIMARY 3 hierarchyid A 9625 NULL NULL   BTREE   tproperties 0 PRIMARY 4 type A 9625 NULL NULL   BTREE   tproperties 1 hi 1 hierarchyid A 9625 NULL NULL   BTREE   ------------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- --------+ 5 rows in set (0.00 sec)

            People

            • Assignee:
              Unassigned
              Reporter:
              Mark Swaanenburg
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: