Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-5742

SAKAI_PERSON_T indexes not created correctly on mysql

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.2.1, 2.2.2, 2.2.3, 2.3.0, 2.3.1, 2.3.2, 2.4.0, 2.4.1, 2.5.0, 2.5.2, 2.5.3, 2.5.4, 2.5.5, 2.6.0
    • Fix Version/s: 2.6.3, 2.7.1
    • Labels:
      None
    • Environment:
      Mysql
    • Conversion Script Required:
      Yes
    • CLE Team Issue:
      Yes

      Description

      From the mysql db on qa1-za, 2.2.1_001, we see:

      mysql> show indexes from SAKAI_PERSON_T;

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

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

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

      SAKAI_PERSON_T 0 PRIMARY 1 ID A 0 NULL NULL   BTREE  
      SAKAI_PERSON_T 0 UUID 1 UUID A 0 NULL NULL   BTREE  
      SAKAI_PERSON_T 0 AGENT_UUID 1 AGENT_UUID A 0 NULL NULL   BTREE  
      SAKAI_PERSON_T 0 AGENT_UUID 2 TYPE_UUID A 0 NULL NULL   BTREE  

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

      However, profile/common-composite-component/src/sql/mysql/sakai_common.sql lists these indexes:

      create index SAKAI_PERSON_TYPE_UUID_I on SAKAI_PERSON_T (TYPE_UUID)
      create index SAKAI_PERSON_SURNAME_I on SAKAI_PERSON_T (SURNAME)
      create index SAKAI_PERSON_ferpaEnabled_I on SAKAI_PERSON_T (ferpaEnabled)
      create index SAKAI_PERSON_AGENT_UUID_I on SAKAI_PERSON_T (AGENT_UUID)
      create index SAKAI_PERSON_GIVEN_NAME_I on SAKAI_PERSON_T (GIVEN_NAME)
      create index SAKAI_PERSON_UID_I on SAKAI_PERSON_T (UID_C)

      and the Hibernate mapping file includes details of an index for ferpaEnabled (to give one example).

      profile/common-composite-component-data/src/java/org/sakaiproject/component/common/edu/person/SakaiPersonImpl.hbm.xml

      <property name="ferpaEnabled">
      <column name="ferpaEnabled"
      index="SAKAI_PERSON_ferpaEnabled_I" />
      </property>

      This is a known issue in Hibernate, where it does not create column indexes correctly. Workarounds must be used to get these indexes created (c/f Gradebook for example).

      The lack of the correct indexes on mysql is most probably contributing to poor performance for tools which use SAKAI_PERSON_T (including Profile and Roster).

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  steve.swinsburg Steve Swinsburg
                  Reporter:
                  smarquard Stephen Marquard
                  Votes:
                  1 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration