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

DB conversion script fails to update MySQL Lessons index that has changed

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: RESOLVED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.9.0
    • Fix Version/s: 2.9.0
    • Component/s: Lessons
    • Environment:
      MySQL 5.5.x
    • Conversion Script Required:
      Yes
    • Previous Issue Keys:
      LSNBLDR-198

      Description

      At UVa, we have a version of Lessons 1.4.x working with our 2.8.1 Sakai. I found an issue in the 2.9.0 MySQL DB conversion script for Lesson Builder: the 'create index' statements assume each index does not already exist, but in our case, many of the indexes do exist, so their 290 creation shows 'Duplicate key name' ERRORs, which masks the fact that one of the indexes has been redefined.

      This index, in particular, is of concern:

      create index lesson_builder_log_index on lesson_builder_log(userId,itemId, studentPageId);

      This preexisting index originally was on 2 columns; now it is on 3 – it fails to create, so the former index is now incorrect for 2.9.0.

      To fix this, the index can be dropped and then recreated:

      ALTER TABLE lesson_builder_log DROP INDEX `lesson_builder_log_index`;
      create index lesson_builder_log_index on lesson_builder_log(userId,itemId, studentPageId);

      Note: here are the indexes that we already have in our 2.8.1 DB:
      lesson_builder_comments_itemid_author
      lesson_builder_student_pages_pageId
      lesson_builder_student_pages_itemId
      lesson_builder_log_index
      lesson_builder_log_index3
      lesson_builder_log_index2
      lesson_builder_groups_itemid
      lesson_builder_pages_pageid
      lesson_builder_pages_toolid
      lesson_builder_items_pageid
      lesson_builder_items_sakaiid

      So, these indexes are new for us with the 290 upgrade:
      lesson_builder_student_pages_index
      lesson_builder_comments_uuid
      lesson_builder_comments_author

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  hedrick Charles Hedrick
                  Reporter:
                  cmw6s Cynthia Gast
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Git Source Code