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

Nuno Fernandes nuno at ufp.edu.pt
Fri May 14 03:47:05 PDT 2010


Hi Alan,

>  If we are to move to RC then we cannot afford instabilities in the
> process such as upgrading hibernate and the required QA'ing.
>
Makes perfect sense, but perhaps this should be considered for 2.8.0.

> > Can I release a 2.1.2
>  If it resolves issues and does not introduce feature creep.
>
This consists only on the removal of "unique" as indicated by Anthony
(STAT-238).


> A very temporary solution which causes extra work for deployers. Has anyone
> suggestions?
>

One possible solution is to apply the (simple) patch from HHH-1012 to
Hibernate 3.2.5.qa source (the version we are using) and deploy it to maven
repo.

Nuno


>
>
> Alan
>
> Alan Berg
> Interim QA Director - The Sakai Foundation
>
> Senior Developer / Quality Assurance
> Group Education and Research Services
> Central Computer Services
> University of Amsterdam
>
> http://home.uva.nl/a.m.berg
>
>
>
>
>
> -----Original Message-----
> From: sakai-qa-bounces at collab.sakaiproject.org on behalf of Nuno Fernandes
> Sent: Fri 5/14/2010 12:28
> To: Anthony Whyte
> Cc: Sakai QA
> Subject: Re: [WG: Sakai QA] 2.7.0 conversion scripts: index
> variations(msgcntr, profile2, sitestats)
>
> Hi all!
>
> *Regarding the "Unexpected Index on:  SITE_ID on
> sst_preferences(SITE_ID)":*
> I have already removed this from the schema creation scripts bundled with
> SiteStats (see *STAT-238*) and merged into SiteStats-2.1.x (but didn't
> release a 2.1.2 yet - *can I do it?*). Also, took the chance to update the
> relevant 2.6.0->2.7.0 conversion scripts (see *SAK-18500*) > this needs to
> be merged into 2.7.x branch.
>
> *Now, our worst nightmare: indexes not being generated with auto.ddl=true
> (MySQL only):*
> Sadly, I was already aware of this but, somehow, I forgot. There are few
> references on sakai-dev since 2006 as this is a
> long-standing-super-annoying
> bug with Hibernate (so long it was detected on Hibernate 2.x (March 2005)
> and, in 3.x, it is not solved yet):
> http://opensource.atlassian.com/projects/hibernate/browse/HB-1458
>
> HOWEVER, there is a very similar issue back from October 2005 (and many
> consider it a duplicate), and this one was marked as fixed for
> [3.2.x<
> http://opensource.atlassian.com/projects/hibernate/browse/HHH/fixforversion/10920
> >
> , 3.3.x<
> http://opensource.atlassian.com/projects/hibernate/browse/HHH/fixforversion/10930
> >
> , 3.5.0-Beta-2<
> http://opensource.atlassian.com/projects/hibernate/browse/HHH/fixforversion/10950
> >
>
> ]:
> http://opensource.atlassian.com/projects/hibernate/browse/HHH-1012
>
> I'm not sure whether there is a final tag released from Hibernate 3.2.x
> with
> this fix incorporated, but I presume 3.5.0 and 3.5.1 have it. I have no
> idea
> how much would it cost to Sakai to upgrade the hibernate to a release with
> this fix, but it would be very, very valuable.
>
> As a side note, please note that if schema is generated by the Hibernate
> Maven plugin (*hibernate3-maven-plugin*), all indexes are properly
> generated, as seen here (MySQL case):
>
> https://source.sakaiproject.org/svn/sitestats/trunk/schema/db-tables/mysql/sitestats.sql
>
> One possible temporary solution is to reference this bug on the 2.7.0
> Release Documentation, and have a (possibly central) location with all
> indexes for Sakai 2.7.x/MySQL. Super-annoying, I know, but even worst if
> having a DB for a system like Sakai without any db index...
>
> Thoughts? Suggestions?
>
> Thank you,
> Nuno
>
>
> On Fri, May 14, 2010 at 2:15 AM, Anthony Whyte <arwhyte at umich.edu> wrote:
>
> > 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;
> >
>
>
>
> --
> Nuno Fernandes  .  { Analyst/Programmer }
>
> || web  . { http://www.ufp.pt  |  http://tinyurl.com/nfgrilo  |  follow_me
> @
> nfgrilo }
> || work . { Universidade Fernando Pessoa  |  Praça 9 de Abril, 349  |
>  4249-004 Porto }
>
>
>
>


-- 
Nuno Fernandes  .  { Analyst/Programmer }

|| web  . { http://www.ufp.pt  |  http://tinyurl.com/nfgrilo  |  follow_me @
nfgrilo }
|| work . { Universidade Fernando Pessoa  |  Praça 9 de Abril, 349  |
 4249-004 Porto }
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-qa/attachments/20100514/da84d1a6/attachment-0001.html 


More information about the sakai-qa mailing list