[Building Sakai] Sakai 2.9.0 MySQL DB Conversion, Lessons - new index fails to be created if LB already exists

[Gast, Cynthia (cmw6s)]

Hello:
I created LSNBLDR-198 for this 2.9.0 DB conversion issue.

Charles, thanks for considering this.  UVa will go ahead and drop/recreate the Lessons index for our 2.9.0/1 upgrades, as needed.

Cynthia Gast @ UVa

________________________________
From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] on behalf of Gast, Cynthia (cmw6s) [cmw6s at eservices.virginia.edu]
Sent: Monday, January 28, 2013 12:46 PM
To: sakai-dev at collab.sakaiproject.org
Subject: [Building Sakai] Sakai 2.9.0 MySQL DB Conversion, Lessons - new index fails to be created if LB already exists

Hi again:

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 we have Lesson Builder installed with 2.8.1 and 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 index is now incorrect for 2.9.0.

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

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


Let me know if I should create a JIRA issue for this; I could not find one about this issue.
Thank-you,
Cynthia Gast @ UVa


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130130/3f03bb0a/attachment.html