[WG: Sakai QA] 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

Anthony Whyte arwhyte at umich.edu
Thu May 13 18:15:14 PDT 2010


Gents--I've been using liquibase to diff between a MySQL 2.6.2->2.7.0 db upgraded with the 2.7.0 conversion script and an auto-generated 2.7.0 db.  There are a variety of indexes included in the conversion scripts that are absent when you generate a 2.7.0 db objects on a first startup (see below).

Please review the indexes relevant to your projects.  I should note that when I checked 2.7.x regarding the index

SST_EVENTS_DATE_IX unique  on sst_events(EVENT_DATE)

SiteStats 2.1.x EventStatImpl.hbm.xml's hibernate-mapping includes:

<property name="date" column="EVENT_DATE" type="date" not-null="true" index="SST_EVENTS_DATE_IX"/>

The same is true when I check 

MFR_EMAIL_USER_ID_I unique  on mfr_email_notification_t(USER_ID)

msgcntr-2.7.x EmailNotification.hbm.xml lists:

<property name="userId">
   <column name="USER_ID" length="255" not-null="true" index="MFR_EMAIL_USER_ID_I" />
</property>

I'm not sure (without digging deeper) why these particular indexes are not getting generated (or any of the others for that matter) when I run 2.7.x with an empty db instance for the first time.  Please see if you can confirm my findings (install and deploy 2.7.x against a new MySQL 5.1 sakai db).  If confirmed and you can fix it quickly, all the better as we are delaying the release of 2.7.x until we've resolved these db irregularities.

Assuming the indexes are indeed missing for those who start with a clean 2.7.x db we'll need to sort out what's preventing the indexes from being generated.

Cheers,

Anth

arwhyte-macbookpro-2010:liquibase-1.9.5 arwhyte$ java -jar ./liquibase-1.9.5.jar --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://127.0.0.1:3306/sakai262to270convert --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai270autogen --baseUsername=sakaiuser --basePassword=sushi
Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai270autogen
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai262to270convert

Missing Indexes (auto-generated 2.7.x db): 
   MFR_EMAIL_CONTEXT_ID_I unique  on mfr_email_notification_t(CONTEXT_ID)
   MFR_EMAIL_USER_ID_I unique  on mfr_email_notification_t(USER_ID)
   PROFILE_FRIENDS_FRIEND_UUID_I unique  on profile_friends_t(FRIEND_UUID)
   PROFILE_FRIENDS_USER_UUID_I unique  on profile_friends_t(USER_UUID)
   PROFILE_IMAGES_IS_CURRENT_I unique  on profile_images_t(IS_CURRENT)
   PROFILE_IMAGES_USER_UUID_I unique  on profile_images_t(USER_UUID)
   SAKAI_PERSON_META_PROPERTY_I unique  on sakai_person_meta_t(PROPERTY)
   SAKAI_PERSON_META_USER_UUID_I unique  on sakai_person_meta_t(USER_UUID)
   SST_EVENTS_DATE_IX unique  on sst_events(EVENT_DATE)
   SST_EVENTS_EVENT_ID_IX unique  on sst_events(EVENT_ID)
   SST_EVENTS_SITEEVENTUSER_ID_IX unique  on sst_events(SITE_ID, EVENT_ID, USER_ID)
   SST_EVENTS_SITE_ID_IX unique  on sst_events(SITE_ID)
   SST_EVENTS_USER_ID_IX unique  on sst_events(USER_ID)
   SST_PREFERENCES_SITE_ID_IX unique  on sst_preferences(SITE_ID)
   SST_REPORTS_SITE_ID_IX unique  on sst_reports(SITE_ID)
   SST_RESOURCES_DATE_IX unique  on sst_resources(RESOURCE_DATE)
   SST_RESOURCES_RES_ACT_IDX unique  on sst_resources(RESOURCE_ACTION)
   SST_RESOURCES_SITE_ID_IX unique  on sst_resources(SITE_ID)
   SST_RESOURCES_USER_ID_IX unique  on sst_resources(USER_ID)
   SST_SITEACTIVITY_DATE_IX unique  on sst_siteactivity(ACTIVITY_DATE)
   SST_SITEACTIVITY_EVENT_ID_IX unique  on sst_siteactivity(EVENT_ID)
   SST_SITEACTIVITY_SITE_ID_IX unique  on sst_siteactivity(SITE_ID)
   SST_SITEVISITS_DATE_IX unique  on sst_sitevisits(VISITS_DATE)
   SST_SITEVISITS_SITE_ID_IX unique  on sst_sitevisits(SITE_ID)

Unexpected Indexes: 
   SITE_ID on sst_preferences(SITE_ID)

Nuno--I believe the above unexpected index involves the CREATE TABLE statement that defines SITE_ID as NOT NULL unique followed by an index statement on the same field.  I assume "unique" needs to be removed from 2.1.x and a new release of sitestats 2.1.x generated.  My modified conversion script (not checked in yet) removed unique from the statement in the conversion scripts and the 2.6.2->2.7.0 upgraded db includes the key.  The auto-generated 2.7.x db does not.

EXAMPLE

2.6.2->2.7.0 conv script updated db (dump of the structure)

CREATE TABLE `sst_events` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(99) COLLATE utf8_bin NOT NULL,
  `SITE_ID` varchar(99) COLLATE utf8_bin NOT NULL,
  `EVENT_ID` varchar(32) COLLATE utf8_bin NOT NULL,
  `EVENT_DATE` date NOT NULL,
  `EVENT_COUNT` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `SST_EVENTS_SITE_ID_IX` (`SITE_ID`),
  KEY `SST_EVENTS_USER_ID_IX` (`USER_ID`),
  KEY `SST_EVENTS_EVENT_ID_IX` (`EVENT_ID`),
  KEY `SST_EVENTS_DATE_IX` (`EVENT_DATE`),
  KEY `SST_EVENTS_SITEEVENTUSER_ID_IX` (`SITE_ID`,`EVENT_ID`,`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


2.7.0 auto-gen db (dump of the structure)

CREATE TABLE `sst_events` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(99) COLLATE utf8_bin NOT NULL,
  `SITE_ID` varchar(99) COLLATE utf8_bin NOT NULL,
  `EVENT_ID` varchar(32) COLLATE utf8_bin NOT NULL,
  `EVENT_DATE` date NOT NULL,
  `EVENT_COUNT` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-qa/attachments/20100513/50bed395/attachment-0001.html 


More information about the sakai-qa mailing list