[Building Sakai] Fwd: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

classic Classic list List threaded Threaded
11 messages Options
Anthony Whyte Anthony Whyte
Reply | Threaded
Open this post in threaded view
|

[Building Sakai] Fwd: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2 db ->2.7.0 db by applying the current 2.7.0 conversion script (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup (auto.ddl=true) I found (among other issues) that a number of hibernate-generated indexes were NOT being created by the auto.ddl = true process.

Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to address the problem.  However, changing an hbm jar dependency (as Nuno points out) at such a late date in the release process is risky although the patch changes only a single method (see thread below).

SiteStats is hit hard by this problem and it concerns me that new deployers may encounter performance issues given the large number of missing indexes.

To reiterate, this problem is limited to new deployments and not those upgrading from a previous release.  

We can

1) add the jar and update the hbm dependency in both trunk and the 2.7.x master pom (<sakai.hibernate.version>).
2) delay updating the jar until after the 2.7.0 release and work to address the problem for 2.7.1 (late summer release I reckon).  We can list the problem as a known issue in the 2.7.0 release notes and add the patched jar to the maven2 repo if deployers want to use it.

As a first step I recommend we try it out in trunk.  Whether we opt to use it with 2.7.x we should resolve on list.

Cheers,

Anth


Missing Indexes: 
    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)


Begin forwarded message:

From: Nuno Fernandes <[hidden email]>
Date: May 14, 2010 7:43:28 AM EDT
To: Steve Swinsburg <[hidden email]>
Cc: Anthony Whyte <[hidden email]>, Holladay Bryan <[hidden email]>, Sakai QA <[hidden email]>
Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

Hi,

I have downloaded 3.2.5.qa source and applied the patch (not the one attached on to HHH-1012, but a patch obtained from the commit):

I have just started up cafe 2.6.x with this patched hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
I will now try with a fresh 2.7.x install.

This patch only changes this method:
 "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
So it won't have any other implications...

Would it be risky to use this patched jar?
Or, we could deploy it to Sakai Maven Repo and reference in Install Docs how to use it (at own risk), by just changing <sakai.hibernate.version> in master/pom.xml.

Nuno


On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg <[hidden email]> wrote:
The indexes not being created with auto.ddl=true on MySQL is a real pain. Profile2 also has indexes which aren't created when auto.ddl=true on MySQL. I wasn't aware of a fix, so nice find. Have you tried it to see if it does fix the issue, the thread seems to have varying responses.

For users that are upgrading, then the indexes are fine (since they'll run the SQL manually), it's just new installs that have auto.ddl on, so perhaps we should include info in the install guide about it. The index only script is a good idea and is an approach I use for Profile2 as well. 

cheers,
Steve


On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:

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):

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.x3.3.x3.5.0-Beta-2]:

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):

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 <[hidden email]> 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 }


_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
David Horwitz David Horwitz
Reply | Threaded
Open this post in threaded view
|

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

+1 to doing this in trunk and analysing before making a call.

This is an old issue that has been known about since hibernate 2.


As a side node if we cut a modified hibernate jar we *must* change the version number - otherwise we are exposing all users to unknowingly use the modified jar.

D

On 05/14/2010 02:13 PM, Anthony Whyte wrote:
When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2 db ->2.7.0 db by applying the current 2.7.0 conversion script (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup (auto.ddl=true) I found (among other issues) that a number of hibernate-generated indexes were NOT being created by the auto.ddl = true process.

Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to address the problem.  However, changing an hbm jar dependency (as Nuno points out) at such a late date in the release process is risky although the patch changes only a single method (see thread below).

SiteStats is hit hard by this problem and it concerns me that new deployers may encounter performance issues given the large number of missing indexes.

To reiterate, this problem is limited to new deployments and not those upgrading from a previous release.  

We can

1) add the jar and update the hbm dependency in both trunk and the 2.7.x master pom (<sakai.hibernate.version>).
2) delay updating the jar until after the 2.7.0 release and work to address the problem for 2.7.1 (late summer release I reckon).  We can list the problem as a known issue in the 2.7.0 release notes and add the patched jar to the maven2 repo if deployers want to use it.

As a first step I recommend we try it out in trunk.  Whether we opt to use it with 2.7.x we should resolve on list.

Cheers,

Anth


Missing Indexes: 
    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)


Begin forwarded message:

  
From: Nuno Fernandes [hidden email]
Date: May 14, 2010 7:43:28 AM EDT
To: Steve Swinsburg [hidden email]
Cc: Anthony Whyte [hidden email], Holladay Bryan [hidden email], Sakai QA [hidden email]
Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

Hi,

I have downloaded 3.2.5.qa source and applied the patch (not the one attached on to HHH-1012, but a patch obtained from the commit):
http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868

I have just started up cafe 2.6.x with this patched hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
I will now try with a fresh 2.7.x install.

This patch only changes this method:
 "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
So it won't have any other implications...

Would it be risky to use this patched jar?
Or, we could deploy it to Sakai Maven Repo and reference in Install Docs how to use it (at own risk), by just changing <sakai.hibernate.version> in master/pom.xml.

Nuno


On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg [hidden email] wrote:
The indexes not being created with auto.ddl=true on MySQL is a real pain. Profile2 also has indexes which aren't created when auto.ddl=true on MySQL. I wasn't aware of a fix, so nice find. Have you tried it to see if it does fix the issue, the thread seems to have varying responses.

For users that are upgrading, then the indexes are fine (since they'll run the SQL manually), it's just new installs that have auto.ddl on, so perhaps we should include info in the install guide about it. The index only script is a good idea and is an approach I use for Profile2 as well. 

cheers,
Steve


On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:

    
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, 3.3.x, 3.5.0-Beta-2]:
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 [hidden email] 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 }
    

  
_______________________________________________ sakai-qa mailing list [hidden email] http://collab.sakaiproject.org/mailman/listinfo/sakai-qa TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
nfgrilo nfgrilo
Reply | Threaded
Open this post in threaded view
|

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

Hi all,

+1 to do it in trunk

Good & bad news:

The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai version.

The good news is that the bad news are also good news. Sakai won't start up because hibernate is now able to create the missing indexes and, it complains about some invalid indexes for MySQL. Here are the (truncated) startup messages:
  • Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
    • java.sql.SQLException: Specified key was too long; max key length is 1024 bytes
  • Unsuccessful schema statement: create index irwikihistory_ref on rwikihistory (referenced)
    • java.sql.SQLException: BLOB/TEXT column 'referenced' used in key specification without a key length
  • Unsuccessful schema statement: create index irwikiobject_ref on rwikiobject (referenced)
    • java.sql.SQLException: BLOB/TEXT column 'referenced' used in key specification without a key length
  • Sql.dbWrite(): error code: 1061 sql:  create index GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:  
    • java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
After this Sakai dies when Gradebook tries to insert/update data during startup (SqlService.dbWrite failure).
Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain about the same issues.

Also, it looks to me that updating on master/pom.xml is insufficient: I guess kernel/pom.xml also needs update which complicates a bit for an ordinary user to replace 3.2.7.ga with the custom hibernate jar.

I will open a JIRA for this. Should this be under Global or Kernel?

(for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to test this, just replace existing hibernate from shared/lib with this one)

Thanks,
Nuno



On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]> wrote:
+1 to doing this in trunk and analysing before making a call.

This is an old issue that has been known about since hibernate 2.


As a side node if we cut a modified hibernate jar we *must* change the version number - otherwise we are exposing all users to unknowingly use the modified jar.

D


On 05/14/2010 02:13 PM, Anthony Whyte wrote:
When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2 db ->2.7.0 db by applying the current 2.7.0 conversion script (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup (auto.ddl=true) I found (among other issues) that a number of hibernate-generated indexes were NOT being created by the auto.ddl = true process.

Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to address the problem.  However, changing an hbm jar dependency (as Nuno points out) at such a late date in the release process is risky although the patch changes only a single method (see thread below).

SiteStats is hit hard by this problem and it concerns me that new deployers may encounter performance issues given the large number of missing indexes.

To reiterate, this problem is limited to new deployments and not those upgrading from a previous release.  

We can

1) add the jar and update the hbm dependency in both trunk and the 2.7.x master pom (<sakai.hibernate.version>).
2) delay updating the jar until after the 2.7.0 release and work to address the problem for 2.7.1 (late summer release I reckon).  We can list the problem as a known issue in the 2.7.0 release notes and add the patched jar to the maven2 repo if deployers want to use it.

As a first step I recommend we try it out in trunk.  Whether we opt to use it with 2.7.x we should resolve on list.

Cheers,

Anth


Missing Indexes: 
    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)


Begin forwarded message:

  
From: Nuno Fernandes [hidden email]
Date: May 14, 2010 7:43:28 AM EDT
To: Steve Swinsburg [hidden email]
Cc: Anthony Whyte [hidden email], Holladay Bryan [hidden email], Sakai QA [hidden email]
Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

Hi,

I have downloaded 3.2.5.qa source and applied the patch (not the one attached on to HHH-1012, but a patch obtained from the commit):
http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868

I have just started up cafe 2.6.x with this patched hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
I will now try with a fresh 2.7.x install.

This patch only changes this method:
 "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
So it won't have any other implications...

Would it be risky to use this patched jar?
Or, we could deploy it to Sakai Maven Repo and reference in Install Docs how to use it (at own risk), by just changing <sakai.hibernate.version> in master/pom.xml.

Nuno


On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg [hidden email] wrote:
The indexes not being created with auto.ddl=true on MySQL is a real pain. Profile2 also has indexes which aren't created when auto.ddl=true on MySQL. I wasn't aware of a fix, so nice find. Have you tried it to see if it does fix the issue, the thread seems to have varying responses.

For users that are upgrading, then the indexes are fine (since they'll run the SQL manually), it's just new installs that have auto.ddl on, so perhaps we should include info in the install guide about it. The index only script is a good idea and is an approach I use for Profile2 as well. 

cheers,
Steve


On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:

    
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, 3.3.x, 3.5.0-Beta-2]:
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 [hidden email] 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 }
    
  
_______________________________________________ sakai-qa mailing list http://collab.sakaiproject.org/mailman/listinfo/sakai-qa TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"



--
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 }

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"

hibernate-3.2.7.ga-sakai.jar (3M) Download Attachment
nfgrilo nfgrilo
Reply | Threaded
Open this post in threaded view
|

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

Since the use of the modifed hibernate jar (with fix for creating indexes on MySQL on auto.ddl=true) also requires fixes on some sakai tools (see email below), does it make sense to think in upgrading to 3.5.2 instead (which should include HHH-1012, accordingly to its fix version)?

Nuno

On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
Hi all,

+1 to do it in trunk

Good & bad news:

The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai version.

The good news is that the bad news are also good news. Sakai won't start up because hibernate is now able to create the missing indexes and, it complains about some invalid indexes for MySQL. Here are the (truncated) startup messages:
  • Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
    • java.sql.SQLException: Specified key was too long; max key length is 1024 bytes
  • Unsuccessful schema statement: create index irwikihistory_ref on rwikihistory (referenced)
    • java.sql.SQLException: BLOB/TEXT column 'referenced' used in key specification without a key length
  • Unsuccessful schema statement: create index irwikiobject_ref on rwikiobject (referenced)
    • java.sql.SQLException: BLOB/TEXT column 'referenced' used in key specification without a key length
  • Sql.dbWrite(): error code: 1061 sql:  create index GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:  
    • java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
After this Sakai dies when Gradebook tries to insert/update data during startup (SqlService.dbWrite failure).
Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain about the same issues.

Also, it looks to me that updating on master/pom.xml is insufficient: I guess kernel/pom.xml also needs update which complicates a bit for an ordinary user to replace 3.2.7.ga with the custom hibernate jar.

I will open a JIRA for this. Should this be under Global or Kernel?

(for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to test this, just replace existing hibernate from shared/lib with this one)

Thanks,
Nuno



On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]> wrote:
+1 to doing this in trunk and analysing before making a call.

This is an old issue that has been known about since hibernate 2.


As a side node if we cut a modified hibernate jar we *must* change the version number - otherwise we are exposing all users to unknowingly use the modified jar.

D


On 05/14/2010 02:13 PM, Anthony Whyte wrote:
When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2 db ->2.7.0 db by applying the current 2.7.0 conversion script (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup (auto.ddl=true) I found (among other issues) that a number of hibernate-generated indexes were NOT being created by the auto.ddl = true process.

Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to address the problem.  However, changing an hbm jar dependency (as Nuno points out) at such a late date in the release process is risky although the patch changes only a single method (see thread below).

SiteStats is hit hard by this problem and it concerns me that new deployers may encounter performance issues given the large number of missing indexes.

To reiterate, this problem is limited to new deployments and not those upgrading from a previous release.  

We can

1) add the jar and update the hbm dependency in both trunk and the 2.7.x master pom (<sakai.hibernate.version>).
2) delay updating the jar until after the 2.7.0 release and work to address the problem for 2.7.1 (late summer release I reckon).  We can list the problem as a known issue in the 2.7.0 release notes and add the patched jar to the maven2 repo if deployers want to use it.

As a first step I recommend we try it out in trunk.  Whether we opt to use it with 2.7.x we should resolve on list.

Cheers,

Anth


Missing Indexes: 
    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)


Begin forwarded message:

  
From: Nuno Fernandes [hidden email]
Date: May 14, 2010 7:43:28 AM EDT
To: Steve Swinsburg [hidden email]
Cc: Anthony Whyte [hidden email], Holladay Bryan [hidden email], Sakai QA [hidden email]
Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)

Hi,

I have downloaded 3.2.5.qa source and applied the patch (not the one attached on to HHH-1012, but a patch obtained from the commit):
http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868

I have just started up cafe 2.6.x with this patched hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
I will now try with a fresh 2.7.x install.

This patch only changes this method:
 "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
So it won't have any other implications...

Would it be risky to use this patched jar?
Or, we could deploy it to Sakai Maven Repo and reference in Install Docs how to use it (at own risk), by just changing <sakai.hibernate.version> in master/pom.xml.

Nuno


On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg [hidden email] wrote:
The indexes not being created with auto.ddl=true on MySQL is a real pain. Profile2 also has indexes which aren't created when auto.ddl=true on MySQL. I wasn't aware of a fix, so nice find. Have you tried it to see if it does fix the issue, the thread seems to have varying responses.

For users that are upgrading, then the indexes are fine (since they'll run the SQL manually), it's just new installs that have auto.ddl on, so perhaps we should include info in the install guide about it. The index only script is a good idea and is an approach I use for Profile2 as well. 

cheers,
Steve


On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:

    
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, 3.3.x, 3.5.0-Beta-2]:
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 [hidden email] 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 }
    
  
_______________________________________________ sakai-qa mailing list http://collab.sakaiproject.org/mailman/listinfo/sakai-qa TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"



--
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 }

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
Aaron Zeckoski-3 Aaron Zeckoski-3
Reply | Threaded
Open this post in threaded view
|

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

If that avoids us maintaining our own copy then I think it is preferable.
-AZ


On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:

> Since the use of the modifed hibernate jar (with fix for creating indexes on
> MySQL on auto.ddl=true) also requires fixes on some sakai tools (see email
> below), does it make sense to think in upgrading to 3.5.2 instead (which
> should include HHH-1012, accordingly to its fix version)?
> Nuno
>
> On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
>>
>> Hi all,
>> +1 to do it in trunk
>> Good & bad news:
>> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
>> version.
>> The good news is that the bad news are also good news. Sakai won't start
>> up because hibernate is now able to create the missing indexes and, it
>> complains about some invalid indexes for MySQL. Here are the (truncated)
>> startup messages:
>>
>> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
>> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
>>
>> java.sql.SQLException: Specified key was too long; max key length is 1024
>> bytes
>>
>> Unsuccessful schema statement: create index irwikihistory_ref on
>> rwikihistory (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Unsuccessful schema statement: create index irwikiobject_ref on
>> rwikiobject (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Sql.dbWrite(): error code: 1061 sql:  create index
>> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
>>
>> java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
>>
>> After this Sakai dies when Gradebook tries to insert/update data during
>> startup (SqlService.dbWrite failure).
>> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain
>> about the same issues.
>> Also, it looks to me that updating on master/pom.xml is insufficient: I
>> guess kernel/pom.xml also needs update which complicates a bit for an
>> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
>> I will open a JIRA for this. Should this be under Global or Kernel?
>> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
>> test this, just replace existing hibernate from shared/lib with this one)
>> Thanks,
>> Nuno
>>
>>
>> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]>
>> wrote:
>>>
>>> +1 to doing this in trunk and analysing before making a call.
>>>
>>> This is an old issue that has been known about since hibernate 2.
>>>
>>>
>>> As a side node if we cut a modified hibernate jar we *must* change the
>>> version number - otherwise we are exposing all users to unknowingly use the
>>> modified jar.
>>>
>>> D
>>>
>>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
>>>
>>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2
>>> db ->2.7.0 db by applying the current 2.7.0 conversion script
>>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup
>>> (auto.ddl=true) I found (among other issues) that a number of
>>> hibernate-generated indexes were NOT being created by the auto.ddl = true
>>> process.
>>>
>>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
>>> address the problem.  However, changing an hbm jar dependency (as Nuno
>>> points out) at such a late date in the release process is risky although the
>>> patch changes only a single method (see thread below).
>>>
>>> SiteStats is hit hard by this problem and it concerns me that new
>>> deployers may encounter performance issues given the large number of missing
>>> indexes.
>>>
>>> To reiterate, this problem is limited to new deployments and not those
>>> upgrading from a previous release.
>>>
>>> We can
>>>
>>> 1) add the jar and update the hbm dependency in both trunk and the 2.7.x
>>> master pom (<sakai.hibernate.version>).
>>> 2) delay updating the jar until after the 2.7.0 release and work to
>>> address the problem for 2.7.1 (late summer release I reckon).  We can list
>>> the problem as a known issue in the 2.7.0 release notes and add the patched
>>> jar to the maven2 repo if deployers want to use it.
>>>
>>> As a first step I recommend we try it out in trunk.  Whether we opt to
>>> use it with 2.7.x we should resolve on list.
>>>
>>> Cheers,
>>>
>>> Anth
>>>
>>>
>>> Missing Indexes:
>>>     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)
>>>
>>>
>>> Begin forwarded message:
>>>
>>>
>>>
>>> From: Nuno Fernandes <[hidden email]>
>>> Date: May 14, 2010 7:43:28 AM EDT
>>> To: Steve Swinsburg <[hidden email]>
>>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
>>> <[hidden email]>, Sakai QA <[hidden email]>
>>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
>>> profile2, sitestats)
>>>
>>> Hi,
>>>
>>> I have downloaded 3.2.5.qa source and applied the patch (not the one
>>> attached on to HHH-1012, but a patch obtained from the commit):
>>>
>>> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
>>>
>>> I have just started up cafe 2.6.x with this patched
>>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
>>> I will now try with a fresh 2.7.x install.
>>>
>>> This patch only changes this method:
>>>  "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
>>> So it won't have any other implications...
>>>
>>> Would it be risky to use this patched jar?
>>> Or, we could deploy it to Sakai Maven Repo and reference in Install Docs
>>> how to use it (at own risk), by just changing <sakai.hibernate.version> in
>>> master/pom.xml.
>>>
>>> Nuno
>>>
>>>
>>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
>>> <[hidden email]> wrote:
>>> The indexes not being created with auto.ddl=true on MySQL is a real pain.
>>> Profile2 also has indexes which aren't created when auto.ddl=true on MySQL.
>>> I wasn't aware of a fix, so nice find. Have you tried it to see if it does
>>> fix the issue, the thread seems to have varying responses.
>>>
>>> For users that are upgrading, then the indexes are fine (since they'll
>>> run the SQL manually), it's just new installs that have auto.ddl on, so
>>> perhaps we should include info in the install guide about it. The index only
>>> script is a good idea and is an approach I use for Profile2 as well.
>>>
>>> cheers,
>>> Steve
>>>
>>>
>>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
>>>
>>>
>>>
>>> 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,
>>> 3.3.x, 3.5.0-Beta-2]:
>>> 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 <[hidden email]> 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 }
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sakai-qa mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of "unsubscribe"
>>>
>>> _______________________________________________
>>> sakai-dev mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of
>>> "unsubscribe"
>>
>>
>>
>> --
>> 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 }
>
> _______________________________________________
> sakai-qa mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>
> TO UNSUBSCRIBE: send email to [hidden email]
> with a subject of "unsubscribe"
>



--
Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
Berg, Alan Berg, Alan
Reply | Threaded
Open this post in threaded view
|

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

Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)
Sorry if I have replied twice to this email. I have had some trouble with the local email server.
 
. I do not believe we have the time or resources to fully test the disturbance caused by a new version of hibernate before 2.7 is released. I would suggest it is better to clearly document the conversion issues and place the scripts correctly and then am for a hibernate update for 2.7.1 
 
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


From: [hidden email] on behalf of Aaron Zeckoski
Sent: Mon 5/17/2010 12:38
To: Nuno Fernandes
Cc: sakai-qa; sakai-dev
Subject: Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)

If that avoids us maintaining our own copy then I think it is preferable.
-AZ


On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:


> Since the use of the modifed hibernate jar (with fix for creating indexes on
> MySQL on auto.ddl=true) also requires fixes on some sakai tools (see email
> below), does it make sense to think in upgrading to 3.5.2 instead (which
> should include HHH-1012, accordingly to its fix version)?
> Nuno
>
> On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
>>
>> Hi all,
>> +1 to do it in trunk
>> Good & bad news:
>> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
>> version.
>> The good news is that the bad news are also good news. Sakai won't start
>> up because hibernate is now able to create the missing indexes and, it
>> complains about some invalid indexes for MySQL. Here are the (truncated)
>> startup messages:
>>
>> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
>> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
>>
>> java.sql.SQLException: Specified key was too long; max key length is 1024
>> bytes
>>
>> Unsuccessful schema statement: create index irwikihistory_ref on
>> rwikihistory (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Unsuccessful schema statement: create index irwikiobject_ref on
>> rwikiobject (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Sql.dbWrite(): error code: 1061 sql:  create index
>> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
>>
>> java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
>>
>> After this Sakai dies when Gradebook tries to insert/update data during
>> startup (SqlService.dbWrite failure).
>> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain
>> about the same issues.
>> Also, it looks to me that updating on master/pom.xml is insufficient: I
>> guess kernel/pom.xml also needs update which complicates a bit for an
>> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
>> I will open a JIRA for this. Should this be under Global or Kernel?
>> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
>> test this, just replace existing hibernate from shared/lib with this one)
>> Thanks,
>> Nuno
>>
>>
>> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]>
>> wrote:
>>>
>>> +1 to doing this in trunk and analysing before making a call.
>>>
>>> This is an old issue that has been known about since hibernate 2.
>>>
>>>
>>> As a side node if we cut a modified hibernate jar we *must* change the
>>> version number - otherwise we are exposing all users to unknowingly use the
>>> modified jar.
>>>
>>> D
>>>
>>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
>>>
>>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2
>>> db ->2.7.0 db by applying the current 2.7.0 conversion script
>>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup
>>> (auto.ddl=true) I found (among other issues) that a number of
>>> hibernate-generated indexes were NOT being created by the auto.ddl = true
>>> process.
>>>
>>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
>>> address the problem.  However, changing an hbm jar dependency (as Nuno
>>> points out) at such a late date in the release process is risky although the
>>> patch changes only a single method (see thread below).
>>>
>>> SiteStats is hit hard by this problem and it concerns me that new
>>> deployers may encounter performance issues given the large number of missing
>>> indexes.
>>>
>>> To reiterate, this problem is limited to new deployments and not those
>>> upgrading from a previous release.
>>>
>>> We can
>>>
>>> 1) add the jar and update the hbm dependency in both trunk and the 2.7.x
>>> master pom (<sakai.hibernate.version>).
>>> 2) delay updating the jar until after the 2.7.0 release and work to
>>> address the problem for 2.7.1 (late summer release I reckon).  We can list
>>> the problem as a known issue in the 2.7.0 release notes and add the patched
>>> jar to the maven2 repo if deployers want to use it.
>>>
>>> As a first step I recommend we try it out in trunk.  Whether we opt to
>>> use it with 2.7.x we should resolve on list.
>>>
>>> Cheers,
>>>
>>> Anth
>>>
>>>
>>> Missing Indexes:
>>>     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)
>>>
>>>
>>> Begin forwarded message:
>>>
>>>
>>>
>>> From: Nuno Fernandes <[hidden email]>
>>> Date: May 14, 2010 7:43:28 AM EDT
>>> To: Steve Swinsburg <[hidden email]>
>>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
>>> <[hidden email]>, Sakai QA <[hidden email]>
>>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
>>> profile2, sitestats)
>>>
>>> Hi,
>>>
>>> I have downloaded 3.2.5.qa source and applied the patch (not the one
>>> attached on to HHH-1012, but a patch obtained from the commit):
>>>
>>> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
>>>
>>> I have just started up cafe 2.6.x with this patched
>>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
>>> I will now try with a fresh 2.7.x install.
>>>
>>> This patch only changes this method:
>>>  "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
>>> So it won't have any other implications...
>>>
>>> Would it be risky to use this patched jar?
>>> Or, we could deploy it to Sakai Maven Repo and reference in Install Docs
>>> how to use it (at own risk), by just changing <sakai.hibernate.version> in
>>> master/pom.xml.
>>>
>>> Nuno
>>>
>>>
>>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
>>> <[hidden email]> wrote:
>>> The indexes not being created with auto.ddl=true on MySQL is a real pain.
>>> Profile2 also has indexes which aren't created when auto.ddl=true on MySQL.
>>> I wasn't aware of a fix, so nice find. Have you tried it to see if it does
>>> fix the issue, the thread seems to have varying responses.
>>>
>>> For users that are upgrading, then the indexes are fine (since they'll
>>> run the SQL manually), it's just new installs that have auto.ddl on, so
>>> perhaps we should include info in the install guide about it. The index only
>>> script is a good idea and is an approach I use for Profile2 as well.
>>>
>>> cheers,
>>> Steve
>>>
>>>
>>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
>>>
>>>
>>>
>>> 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,
>>> 3.3.x, 3.5.0-Beta-2]:
>>> 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 <[hidden email]> 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 }
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sakai-qa mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of "unsubscribe"
>>>
>>> _______________________________________________
>>> sakai-dev mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of
>>> "unsubscribe"
>>
>>
>>
>> --
>> 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 }
>
> _______________________________________________
> sakai-qa mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>
> TO UNSUBSCRIBE: send email to [hidden email]
> with a subject of "unsubscribe"
>



--
Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"


_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
nfgrilo nfgrilo
Reply | Threaded
Open this post in threaded view
|

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

Yes, I agree Alan - 2.7.0 is out of question!

My last question was if we should (always working on trunk) work on upgrading to a custom 3.2.5.ga (+ HHH-1012) and target it for 2.7.1 OR work to upgrade to 3.5.2 (official, latest stable) and target it for 2.8.

WDYT?

Testing the custom 3.2.5.ga + HHH-1012 revealed that MessageCenter, RWiki and Gradebook have issues with MySQL index keys (related to key length and key duplication). Sakai startup just halt after GB db key error so, I'm not sure if there are more tools with db key related errors.

Nuno


On Mon, May 17, 2010 at 12:29 PM, Berg, Alan <[hidden email]> wrote:
Sorry if I have replied twice to this email. I have had some trouble with the local email server.
 
. I do not believe we have the time or resources to fully test the disturbance caused by a new version of hibernate before 2.7 is released. I would suggest it is better to clearly document the conversion issues and place the scripts correctly and then am for a hibernate update for 2.7.1 
 
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


From: [hidden email] on behalf of Aaron Zeckoski
Sent: Mon 5/17/2010 12:38
To: Nuno Fernandes
Cc: sakai-qa; sakai-dev
Subject: Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)

If that avoids us maintaining our own copy then I think it is preferable.
-AZ


On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:
> Since the use of the modifed hibernate jar (with fix for creating indexes on
> MySQL on auto.ddl=true) also requires fixes on some sakai tools (see email
> below), does it make sense to think in upgrading to 3.5.2 instead (which
> should include HHH-1012, accordingly to its fix version)?
> Nuno
>
> On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
>>
>> Hi all,
>> +1 to do it in trunk
>> Good & bad news:
>> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
>> version.
>> The good news is that the bad news are also good news. Sakai won't start
>> up because hibernate is now able to create the missing indexes and, it
>> complains about some invalid indexes for MySQL. Here are the (truncated)
>> startup messages:
>>
>> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
>> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
>>
>> java.sql.SQLException: Specified key was too long; max key length is 1024
>> bytes
>>
>> Unsuccessful schema statement: create index irwikihistory_ref on
>> rwikihistory (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Unsuccessful schema statement: create index irwikiobject_ref on
>> rwikiobject (referenced)
>>
>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>> specification without a key length
>>
>> Sql.dbWrite(): error code: 1061 sql:  create index
>> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
>>
>> java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
>>
>> After this Sakai dies when Gradebook tries to insert/update data during
>> startup (SqlService.dbWrite failure).
>> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain
>> about the same issues.
>> Also, it looks to me that updating on master/pom.xml is insufficient: I
>> guess kernel/pom.xml also needs update which complicates a bit for an
>> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
>> I will open a JIRA for this. Should this be under Global or Kernel?
>> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
>> test this, just replace existing hibernate from shared/lib with this one)
>> Thanks,
>> Nuno
>>
>>
>> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]>
>> wrote:
>>>
>>> +1 to doing this in trunk and analysing before making a call.
>>>
>>> This is an old issue that has been known about since hibernate 2.
>>>
>>>
>>> As a side node if we cut a modified hibernate jar we *must* change the
>>> version number - otherwise we are exposing all users to unknowingly use the
>>> modified jar.
>>>
>>> D
>>>
>>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
>>>
>>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2
>>> db ->2.7.0 db by applying the current 2.7.0 conversion script
>>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup
>>> (auto.ddl=true) I found (among other issues) that a number of
>>> hibernate-generated indexes were NOT being created by the auto.ddl = true
>>> process.
>>>
>>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
>>> address the problem.  However, changing an hbm jar dependency (as Nuno
>>> points out) at such a late date in the release process is risky although the
>>> patch changes only a single method (see thread below).
>>>
>>> SiteStats is hit hard by this problem and it concerns me that new
>>> deployers may encounter performance issues given the large number of missing
>>> indexes.
>>>
>>> To reiterate, this problem is limited to new deployments and not those
>>> upgrading from a previous release.
>>>
>>> We can
>>>
>>> 1) add the jar and update the hbm dependency in both trunk and the 2.7.x
>>> master pom (<sakai.hibernate.version>).
>>> 2) delay updating the jar until after the 2.7.0 release and work to
>>> address the problem for 2.7.1 (late summer release I reckon).  We can list
>>> the problem as a known issue in the 2.7.0 release notes and add the patched
>>> jar to the maven2 repo if deployers want to use it.
>>>
>>> As a first step I recommend we try it out in trunk.  Whether we opt to
>>> use it with 2.7.x we should resolve on list.
>>>
>>> Cheers,
>>>
>>> Anth
>>>
>>>
>>> Missing Indexes:
>>>     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)
>>>
>>>
>>> Begin forwarded message:
>>>
>>>
>>>
>>> From: Nuno Fernandes <[hidden email]>
>>> Date: May 14, 2010 7:43:28 AM EDT
>>> To: Steve Swinsburg <[hidden email]>
>>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
>>> <[hidden email]>, Sakai QA <[hidden email]>
>>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
>>> profile2, sitestats)
>>>
>>> Hi,
>>>
>>> I have downloaded 3.2.5.qa source and applied the patch (not the one
>>> attached on to HHH-1012, but a patch obtained from the commit):
>>>
>>> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
>>>
>>> I have just started up cafe 2.6.x with this patched
>>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
>>> I will now try with a fresh 2.7.x install.
>>>
>>> This patch only changes this method:
>>>  "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
>>> So it won't have any other implications...
>>>
>>> Would it be risky to use this patched jar?
>>> Or, we could deploy it to Sakai Maven Repo and reference in Install Docs
>>> how to use it (at own risk), by just changing <sakai.hibernate.version> in
>>> master/pom.xml.
>>>
>>> Nuno
>>>
>>>
>>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
>>> <[hidden email]> wrote:
>>> The indexes not being created with auto.ddl=true on MySQL is a real pain.
>>> Profile2 also has indexes which aren't created when auto.ddl=true on MySQL.
>>> I wasn't aware of a fix, so nice find. Have you tried it to see if it does
>>> fix the issue, the thread seems to have varying responses.
>>>
>>> For users that are upgrading, then the indexes are fine (since they'll
>>> run the SQL manually), it's just new installs that have auto.ddl on, so
>>> perhaps we should include info in the install guide about it. The index only
>>> script is a good idea and is an approach I use for Profile2 as well.
>>>
>>> cheers,
>>> Steve
>>>
>>>
>>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
>>>
>>>
>>>
>>> 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,
>>> 3.3.x, 3.5.0-Beta-2]:
>>> 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 <[hidden email]> 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 }
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sakai-qa mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of "unsubscribe"
>>>
>>> _______________________________________________
>>> sakai-dev mailing list
>>> [hidden email]
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>
>>> TO UNSUBSCRIBE: send email to
>>> [hidden email] with a subject of
>>> "unsubscribe"
>>
>>
>>
>> --
>> 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 }
>
> _______________________________________________
> sakai-qa mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>
> TO UNSUBSCRIBE: send email to [hidden email]
> with a subject of "unsubscribe"
>



--
Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"




--
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 }

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
Steve Swinsburg-3 Steve Swinsburg-3
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Aaron Zeckoski-3
+1

Lets look at getting this into trunk, resolving any various issues and going from there. Once that is stabilised we *might* be able to look at the same for a 2.7.x release if it's not too disruptive. Nice work Nuno.

cheers,
Steve

On 17/05/2010, at 8:38 PM, Aaron Zeckoski wrote:

> If that avoids us maintaining our own copy then I think it is preferable.
> -AZ
>
>
> On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:
>> Since the use of the modifed hibernate jar (with fix for creating indexes on
>> MySQL on auto.ddl=true) also requires fixes on some sakai tools (see email
>> below), does it make sense to think in upgrading to 3.5.2 instead (which
>> should include HHH-1012, accordingly to its fix version)?
>> Nuno
>>
>> On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
>>>
>>> Hi all,
>>> +1 to do it in trunk
>>> Good & bad news:
>>> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
>>> version.
>>> The good news is that the bad news are also good news. Sakai won't start
>>> up because hibernate is now able to create the missing indexes and, it
>>> complains about some invalid indexes for MySQL. Here are the (truncated)
>>> startup messages:
>>>
>>> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
>>> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
>>>
>>> java.sql.SQLException: Specified key was too long; max key length is 1024
>>> bytes
>>>
>>> Unsuccessful schema statement: create index irwikihistory_ref on
>>> rwikihistory (referenced)
>>>
>>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>>> specification without a key length
>>>
>>> Unsuccessful schema statement: create index irwikiobject_ref on
>>> rwikiobject (referenced)
>>>
>>> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
>>> specification without a key length
>>>
>>> Sql.dbWrite(): error code: 1061 sql:  create index
>>> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
>>>
>>> java.sql.SQLException: Duplicate key name 'GB_GRADE_RECORD_STUDENT_ID_IDX'
>>>
>>> After this Sakai dies when Gradebook tries to insert/update data during
>>> startup (SqlService.dbWrite failure).
>>> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not complain
>>> about the same issues.
>>> Also, it looks to me that updating on master/pom.xml is insufficient: I
>>> guess kernel/pom.xml also needs update which complicates a bit for an
>>> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
>>> I will open a JIRA for this. Should this be under Global or Kernel?
>>> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
>>> test this, just replace existing hibernate from shared/lib with this one)
>>> Thanks,
>>> Nuno
>>>
>>>
>>> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]>
>>> wrote:
>>>>
>>>> +1 to doing this in trunk and analysing before making a call.
>>>>
>>>> This is an old issue that has been known about since hibernate 2.
>>>>
>>>>
>>>> As a side node if we cut a modified hibernate jar we *must* change the
>>>> version number - otherwise we are exposing all users to unknowingly use the
>>>> modified jar.
>>>>
>>>> D
>>>>
>>>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
>>>>
>>>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a 2.6.2
>>>> db ->2.7.0 db by applying the current 2.7.0 conversion script
>>>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first startup
>>>> (auto.ddl=true) I found (among other issues) that a number of
>>>> hibernate-generated indexes were NOT being created by the auto.ddl = true
>>>> process.
>>>>
>>>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
>>>> address the problem.  However, changing an hbm jar dependency (as Nuno
>>>> points out) at such a late date in the release process is risky although the
>>>> patch changes only a single method (see thread below).
>>>>
>>>> SiteStats is hit hard by this problem and it concerns me that new
>>>> deployers may encounter performance issues given the large number of missing
>>>> indexes.
>>>>
>>>> To reiterate, this problem is limited to new deployments and not those
>>>> upgrading from a previous release.
>>>>
>>>> We can
>>>>
>>>> 1) add the jar and update the hbm dependency in both trunk and the 2.7.x
>>>> master pom (<sakai.hibernate.version>).
>>>> 2) delay updating the jar until after the 2.7.0 release and work to
>>>> address the problem for 2.7.1 (late summer release I reckon).  We can list
>>>> the problem as a known issue in the 2.7.0 release notes and add the patched
>>>> jar to the maven2 repo if deployers want to use it.
>>>>
>>>> As a first step I recommend we try it out in trunk.  Whether we opt to
>>>> use it with 2.7.x we should resolve on list.
>>>>
>>>> Cheers,
>>>>
>>>> Anth
>>>>
>>>>
>>>> Missing Indexes:
>>>>    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)
>>>>
>>>>
>>>> Begin forwarded message:
>>>>
>>>>
>>>>
>>>> From: Nuno Fernandes <[hidden email]>
>>>> Date: May 14, 2010 7:43:28 AM EDT
>>>> To: Steve Swinsburg <[hidden email]>
>>>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
>>>> <[hidden email]>, Sakai QA <[hidden email]>
>>>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
>>>> profile2, sitestats)
>>>>
>>>> Hi,
>>>>
>>>> I have downloaded 3.2.5.qa source and applied the patch (not the one
>>>> attached on to HHH-1012, but a patch obtained from the commit):
>>>>
>>>> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
>>>>
>>>> I have just started up cafe 2.6.x with this patched
>>>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
>>>> I will now try with a fresh 2.7.x install.
>>>>
>>>> This patch only changes this method:
>>>> "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
>>>> So it won't have any other implications...
>>>>
>>>> Would it be risky to use this patched jar?
>>>> Or, we could deploy it to Sakai Maven Repo and reference in Install Docs
>>>> how to use it (at own risk), by just changing <sakai.hibernate.version> in
>>>> master/pom.xml.
>>>>
>>>> Nuno
>>>>
>>>>
>>>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
>>>> <[hidden email]> wrote:
>>>> The indexes not being created with auto.ddl=true on MySQL is a real pain.
>>>> Profile2 also has indexes which aren't created when auto.ddl=true on MySQL.
>>>> I wasn't aware of a fix, so nice find. Have you tried it to see if it does
>>>> fix the issue, the thread seems to have varying responses.
>>>>
>>>> For users that are upgrading, then the indexes are fine (since they'll
>>>> run the SQL manually), it's just new installs that have auto.ddl on, so
>>>> perhaps we should include info in the install guide about it. The index only
>>>> script is a good idea and is an approach I use for Profile2 as well.
>>>>
>>>> cheers,
>>>> Steve
>>>>
>>>>
>>>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
>>>>
>>>>
>>>>
>>>> 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,
>>>> 3.3.x, 3.5.0-Beta-2]:
>>>> 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 <[hidden email]> 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 }
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> sakai-qa mailing list
>>>> [hidden email]
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>>>
>>>> TO UNSUBSCRIBE: send email to
>>>> [hidden email] with a subject of "unsubscribe"
>>>>
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> [hidden email]
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>>
>>>> TO UNSUBSCRIBE: send email to
>>>> [hidden email] with a subject of
>>>> "unsubscribe"
>>>
>>>
>>>
>>> --
>>> 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 }
>>
>> _______________________________________________
>> sakai-qa mailing list
>> [hidden email]
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>
>> TO UNSUBSCRIBE: send email to [hidden email]
>> with a subject of "unsubscribe"
>>
>
>
>
> --
> Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
> _______________________________________________
> sakai-qa mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>
> TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
Berg, Alan Berg, Alan
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by nfgrilo
RE: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)

I think  we should upgrade to the latest and most stable and clean-up moving into 2.7.x when time and resources allows.

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: Nuno Fernandes [[hidden email]]
Sent: Mon 5/17/2010 13:48
To: Berg, Alan
Cc: Aaron Zeckoski; sakai-qa; sakai-dev
Subject: Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)

Yes, I agree Alan - 2.7.0 is out of question!

My last question was if we should (always working on trunk) work on
upgrading to a custom 3.2.5.ga (+ HHH-1012) and target it for 2.7.1 OR work
to upgrade to 3.5.2 (official, latest stable) and target it for 2.8.

WDYT?

Testing the custom 3.2.5.ga + HHH-1012 revealed that MessageCenter, RWiki
and Gradebook have issues with MySQL index keys (related to key length and
key duplication). Sakai startup just halt after GB db key error so, I'm not
sure if there are more tools with db key related errors.

Nuno


On Mon, May 17, 2010 at 12:29 PM, Berg, Alan <[hidden email]> wrote:

>   Sorry if I have replied twice to this email. I have had some trouble
> with the local email server.
>
> . I do not believe we have the time or resources to fully test the
> disturbance caused by a new version of hibernate before 2.7 is released. I
> would suggest it is better to clearly document the conversion issues
> and place the scripts correctly and then am for a hibernate update for
> 2.7.1
>
>
> 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
>
>
> ------------------------------
> *From:* [hidden email] on behalf of Aaron
> Zeckoski
> *Sent:* Mon 5/17/2010 12:38
> *To:* Nuno Fernandes
> *Cc:* sakai-qa; sakai-dev
> *Subject:* Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion
> scripts:index variations (msgcntr, profile2, sitestats)
>
>  If that avoids us maintaining our own copy then I think it is preferable.
> -AZ
>
>
> On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:
> > Since the use of the modifed hibernate jar (with fix for creating indexes
> on
> > MySQL on auto.ddl=true) also requires fixes on some sakai tools (see
> email
> > below), does it make sense to think in upgrading to 3.5.2 instead (which
> > should include HHH-1012, accordingly to its fix version)?
> > Nuno
> >
> > On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
> >>
> >> Hi all,
> >> +1 to do it in trunk
> >> Good & bad news:
> >> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
> >> version.
> >> The good news is that the bad news are also good news. Sakai won't start
> >> up because hibernate is now able to create the missing indexes and, it
> >> complains about some invalid indexes for MySQL. Here are the (truncated)
> >> startup messages:
> >>
> >> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
> >> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
> >>
> >> java.sql.SQLException: Specified key was too long; max key length is
> 1024
> >> bytes
> >>
> >> Unsuccessful schema statement: create index irwikihistory_ref on
> >> rwikihistory (referenced)
> >>
> >> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
> >> specification without a key length
> >>
> >> Unsuccessful schema statement: create index irwikiobject_ref on
> >> rwikiobject (referenced)
> >>
> >> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
> >> specification without a key length
> >>
> >> Sql.dbWrite(): error code: 1061 sql:  create index
> >> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
> >>
> >> java.sql.SQLException: Duplicate key name
> 'GB_GRADE_RECORD_STUDENT_ID_IDX'
> >>
> >> After this Sakai dies when Gradebook tries to insert/update data during
> >> startup (SqlService.dbWrite failure).
> >> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not
> complain
> >> about the same issues.
> >> Also, it looks to me that updating on master/pom.xml is insufficient: I
> >> guess kernel/pom.xml also needs update which complicates a bit for an
> >> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
> >> I will open a JIRA for this. Should this be under Global or Kernel?
> >> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
> >> test this, just replace existing hibernate from shared/lib with this
> one)
> >> Thanks,
> >> Nuno
> >>
> >>
> >> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]
> >
> >> wrote:
> >>>
> >>> +1 to doing this in trunk and analysing before making a call.
> >>>
> >>> This is an old issue that has been known about since hibernate 2.
> >>>
> >>>
> >>> As a side node if we cut a modified hibernate jar we *must* change the
> >>> version number - otherwise we are exposing all users to unknowingly use
> the
> >>> modified jar.
> >>>
> >>> D
> >>>
> >>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
> >>>
> >>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a
> 2.6.2
> >>> db ->2.7.0 db by applying the current 2.7.0 conversion script
> >>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first
> startup
> >>> (auto.ddl=true) I found (among other issues) that a number of
> >>> hibernate-generated indexes were NOT being created by the auto.ddl =
> true
> >>> process.
> >>>
> >>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
> >>> address the problem.  However, changing an hbm jar dependency (as Nuno
> >>> points out) at such a late date in the release process is risky
> although the
> >>> patch changes only a single method (see thread below).
> >>>
> >>> SiteStats is hit hard by this problem and it concerns me that new
> >>> deployers may encounter performance issues given the large number of
> missing
> >>> indexes.
> >>>
> >>> To reiterate, this problem is limited to new deployments and not those
> >>> upgrading from a previous release.
> >>>
> >>> We can
> >>>
> >>> 1) add the jar and update the hbm dependency in both trunk and the
> 2.7.x
> >>> master pom (<sakai.hibernate.version>).
> >>> 2) delay updating the jar until after the 2.7.0 release and work to
> >>> address the problem for 2.7.1 (late summer release I reckon).  We can
> list
> >>> the problem as a known issue in the 2.7.0 release notes and add the
> patched
> >>> jar to the maven2 repo if deployers want to use it.
> >>>
> >>> As a first step I recommend we try it out in trunk.  Whether we opt to
> >>> use it with 2.7.x we should resolve on list.
> >>>
> >>> Cheers,
> >>>
> >>> Anth
> >>>
> >>>
> >>> Missing Indexes:
> >>>     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)
> >>>
> >>>
> >>> Begin forwarded message:
> >>>
> >>>
> >>>
> >>> From: Nuno Fernandes <[hidden email]>
> >>> Date: May 14, 2010 7:43:28 AM EDT
> >>> To: Steve Swinsburg <[hidden email]>
> >>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
> >>> <[hidden email]>, Sakai QA <[hidden email]>
> >>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
> >>> profile2, sitestats)
> >>>
> >>> Hi,
> >>>
> >>> I have downloaded 3.2.5.qa source and applied the patch (not the one
> >>> attached on to HHH-1012, but a patch obtained from the commit):
> >>>
> >>>
> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
> >>>
> >>> I have just started up cafe 2.6.x with this patched
> >>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
> >>> I will now try with a fresh 2.7.x install.
> >>>
> >>> This patch only changes this method:
> >>>  "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
> >>> So it won't have any other implications...
> >>>
> >>> Would it be risky to use this patched jar?
> >>> Or, we could deploy it to Sakai Maven Repo and reference in Install
> Docs
> >>> how to use it (at own risk), by just changing <sakai.hibernate.version>
> in
> >>> master/pom.xml.
> >>>
> >>> Nuno
> >>>
> >>>
> >>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
> >>> <[hidden email]> wrote:
> >>> The indexes not being created with auto.ddl=true on MySQL is a real
> pain.
> >>> Profile2 also has indexes which aren't created when auto.ddl=true on
> MySQL.
> >>> I wasn't aware of a fix, so nice find. Have you tried it to see if it
> does
> >>> fix the issue, the thread seems to have varying responses.
> >>>
> >>> For users that are upgrading, then the indexes are fine (since they'll
> >>> run the SQL manually), it's just new installs that have auto.ddl on, so
> >>> perhaps we should include info in the install guide about it. The index
> only
> >>> script is a good idea and is an approach I use for Profile2 as well.
> >>>
> >>> cheers,
> >>> Steve
> >>>
> >>>
> >>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
> >>>
> >>>
> >>>
> >>> 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,
> >>> 3.3.x, 3.5.0-Beta-2]:
> >>> 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 <[hidden email]>
> 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 }
> >>>
> >>>
> >>>
> >>>
> >>> _______________________________________________
> >>> sakai-qa mailing list
> >>> [hidden email]
> >>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
> >>>
> >>> TO UNSUBSCRIBE: send email to
> >>> [hidden email] with a subject of
> "unsubscribe"
> >>>
> >>> _______________________________________________
> >>> sakai-dev mailing list
> >>> [hidden email]
> >>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> >>>
> >>> TO UNSUBSCRIBE: send email to
> >>> [hidden email] with a subject of
> >>> "unsubscribe"
> >>
> >>
> >>
> >> --
> >> 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 }
> >
> > _______________________________________________
> > sakai-qa mailing list
> > [hidden email]
> > http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
> >
> > TO UNSUBSCRIBE: send email to
> [hidden email]
> > with a subject of "unsubscribe"
> >
>
>
>
> --
> Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
> _______________________________________________
> sakai-dev mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> [hidden email] with a subject of
> "unsubscribe"
>



--
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 }


_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
Berg, Alan Berg, Alan
Reply | Threaded
Open this post in threaded view
|

[Building Sakai] Citation helper configuration question

Citation helper configuration question

Hi all,

I want to quickly test the search capabilities of the citation helper against a range of search engines. Can someone please send me example configuration (config.xml,sakai.properties) details that will work without any extra changes on a QA server.

Thanks,

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



_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"
nfgrilo nfgrilo
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Berg, Alan
For further progress on this issue, please see SAK-18510

Nuno

On Mon, May 17, 2010 at 12:51 PM, Berg, Alan <[hidden email]> wrote:

I think  we should upgrade to the latest and most stable and clean-up moving into 2.7.x when time and resources allows.



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: Nuno Fernandes [[hidden email]]
Sent: Mon 5/17/2010 13:48
To: Berg, Alan
Cc: Aaron Zeckoski; sakai-qa; sakai-dev
Subject: Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion scripts:index variations (msgcntr, profile2, sitestats)

Yes, I agree Alan - 2.7.0 is out of question!

My last question was if we should (always working on trunk) work on
upgrading to a custom 3.2.5.ga (+ HHH-1012) and target it for 2.7.1 OR work
to upgrade to 3.5.2 (official, latest stable) and target it for 2.8.

WDYT?

Testing the custom 3.2.5.ga + HHH-1012 revealed that MessageCenter, RWiki
and Gradebook have issues with MySQL index keys (related to key length and
key duplication). Sakai startup just halt after GB db key error so, I'm not
sure if there are more tools with db key related errors.

Nuno


On Mon, May 17, 2010 at 12:29 PM, Berg, Alan <[hidden email]> wrote:

>   Sorry if I have replied twice to this email. I have had some trouble
> with the local email server.
>
> . I do not believe we have the time or resources to fully test the
> disturbance caused by a new version of hibernate before 2.7 is released. I
> would suggest it is better to clearly document the conversion issues
> and place the scripts correctly and then am for a hibernate update for
> 2.7.1
>
>
> 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
>
>
> ------------------------------
> *From:* [hidden email] on behalf of Aaron
> Zeckoski
> *Sent:* Mon 5/17/2010 12:38
> *To:* Nuno Fernandes
> *Cc:* sakai-qa; sakai-dev
> *Subject:* Re: [Building Sakai] [WG: Sakai QA] Fwd: 2.7.0 conversion
> scripts:index variations (msgcntr, profile2, sitestats)
>
>  If that avoids us maintaining our own copy then I think it is preferable.
> -AZ
>
>
> On Mon, May 17, 2010 at 11:31 AM, Nuno Fernandes <[hidden email]> wrote:
> > Since the use of the modifed hibernate jar (with fix for creating indexes
> on
> > MySQL on auto.ddl=true) also requires fixes on some sakai tools (see
> email
> > below), does it make sense to think in upgrading to 3.5.2 instead (which
> > should include HHH-1012, accordingly to its fix version)?
> > Nuno
> >
> > On Fri, May 14, 2010 at 4:36 PM, Nuno Fernandes <[hidden email]> wrote:
> >>
> >> Hi all,
> >> +1 to do it in trunk
> >> Good & bad news:
> >> The bad news is that Sakai won't start with the modified 3.2.7.ga-sakai
> >> version.
> >> The good news is that the bad news are also good news. Sakai won't start
> >> up because hibernate is now able to create the missing indexes and, it
> >> complains about some invalid indexes for MySQL. Here are the (truncated)
> >> startup messages:
> >>
> >> Unsuccessful schema statement: create index MFR_PVT_MSG_USR_I1 on
> >> MFR_PVT_MSG_USR_T (USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
> >>
> >> java.sql.SQLException: Specified key was too long; max key length is
> 1024
> >> bytes
> >>
> >> Unsuccessful schema statement: create index irwikihistory_ref on
> >> rwikihistory (referenced)
> >>
> >> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
> >> specification without a key length
> >>
> >> Unsuccessful schema statement: create index irwikiobject_ref on
> >> rwikiobject (referenced)
> >>
> >> java.sql.SQLException: BLOB/TEXT column 'referenced' used in key
> >> specification without a key length
> >>
> >> Sql.dbWrite(): error code: 1061 sql:  create index
> >> GB_GRADE_RECORD_STUDENT_ID_IDX on GB_GRADE_RECORD_T (STUDENT_ID) binds:
> >>
> >> java.sql.SQLException: Duplicate key name
> 'GB_GRADE_RECORD_STUDENT_ID_IDX'
> >>
> >> After this Sakai dies when Gradebook tries to insert/update data during
> >> startup (SqlService.dbWrite failure).
> >> Please note that this is on MySQL 4.1.15 - MySQL 5 may or may not
> complain
> >> about the same issues.
> >> Also, it looks to me that updating on master/pom.xml is insufficient: I
> >> guess kernel/pom.xml also needs update which complicates a bit for an
> >> ordinary user to replace 3.2.7.ga with the custom hibernate jar.
> >> I will open a JIRA for this. Should this be under Global or Kernel?
> >> (for the record, attached is hibernate-3.2.7.ga-sakai.jar If you wish to
> >> test this, just replace existing hibernate from shared/lib with this
> one)
> >> Thanks,
> >> Nuno
> >>
> >>
> >> On Fri, May 14, 2010 at 1:23 PM, David Horwitz <[hidden email]
> >
> >> wrote:
> >>>
> >>> +1 to doing this in trunk and analysing before making a call.
> >>>
> >>> This is an old issue that has been known about since hibernate 2.
> >>>
> >>>
> >>> As a side node if we cut a modified hibernate jar we *must* change the
> >>> version number - otherwise we are exposing all users to unknowingly use
> the
> >>> modified jar.
> >>>
> >>> D
> >>>
> >>> On 05/14/2010 02:13 PM, Anthony Whyte wrote:
> >>>
> >>> When comparing MySQL 2.7.0 databases created by 1) upgrading from a
> 2.6.2
> >>> db ->2.7.0 db by applying the current 2.7.0 conversion script
> >>> (auto.ddl=false) and 2) by auto-generating 2.7.0 db objects on first
> startup
> >>> (auto.ddl=true) I found (among other issues) that a number of
> >>> hibernate-generated indexes were NOT being created by the auto.ddl =
> true
> >>> process.
> >>>
> >>> Nuno is testing a patched hibernate-3.2.5.qa-sakai jar that appears to
> >>> address the problem.  However, changing an hbm jar dependency (as Nuno
> >>> points out) at such a late date in the release process is risky
> although the
> >>> patch changes only a single method (see thread below).
> >>>
> >>> SiteStats is hit hard by this problem and it concerns me that new
> >>> deployers may encounter performance issues given the large number of
> missing
> >>> indexes.
> >>>
> >>> To reiterate, this problem is limited to new deployments and not those
> >>> upgrading from a previous release.
> >>>
> >>> We can
> >>>
> >>> 1) add the jar and update the hbm dependency in both trunk and the
> 2.7.x
> >>> master pom (<sakai.hibernate.version>).
> >>> 2) delay updating the jar until after the 2.7.0 release and work to
> >>> address the problem for 2.7.1 (late summer release I reckon).  We can
> list
> >>> the problem as a known issue in the 2.7.0 release notes and add the
> patched
> >>> jar to the maven2 repo if deployers want to use it.
> >>>
> >>> As a first step I recommend we try it out in trunk.  Whether we opt to
> >>> use it with 2.7.x we should resolve on list.
> >>>
> >>> Cheers,
> >>>
> >>> Anth
> >>>
> >>>
> >>> Missing Indexes:
> >>>     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)
> >>>
> >>>
> >>> Begin forwarded message:
> >>>
> >>>
> >>>
> >>> From: Nuno Fernandes <[hidden email]>
> >>> Date: May 14, 2010 7:43:28 AM EDT
> >>> To: Steve Swinsburg <[hidden email]>
> >>> Cc: Anthony Whyte <[hidden email]>, Holladay Bryan
> >>> <[hidden email]>, Sakai QA <[hidden email]>
> >>> Subject: Re: 2.7.0 conversion scripts: index variations (msgcntr,
> >>> profile2, sitestats)
> >>>
> >>> Hi,
> >>>
> >>> I have downloaded 3.2.5.qa source and applied the patch (not the one
> >>> attached on to HHH-1012, but a patch obtained from the commit):
> >>>
> >>>
> http://fisheye.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/cfg/Configuration.java?r1=16659&r2=17868
> >>>
> >>> I have just started up cafe 2.6.x with this patched
> >>> hibernate-3.2.5.qa-sakai.jar and.... yes, it does fix it :)
> >>> I will now try with a fresh 2.7.x install.
> >>>
> >>> This patch only changes this method:
> >>>  "org.hibernate.cfg.Configuration.generateSchemaUpdateScript()"
> >>> So it won't have any other implications...
> >>>
> >>> Would it be risky to use this patched jar?
> >>> Or, we could deploy it to Sakai Maven Repo and reference in Install
> Docs
> >>> how to use it (at own risk), by just changing <sakai.hibernate.version>
> in
> >>> master/pom.xml.
> >>>
> >>> Nuno
> >>>
> >>>
> >>> On Fri, May 14, 2010 at 12:35 PM, Steve Swinsburg
> >>> <[hidden email]> wrote:
> >>> The indexes not being created with auto.ddl=true on MySQL is a real
> pain.
> >>> Profile2 also has indexes which aren't created when auto.ddl=true on
> MySQL.
> >>> I wasn't aware of a fix, so nice find. Have you tried it to see if it
> does
> >>> fix the issue, the thread seems to have varying responses.
> >>>
> >>> For users that are upgrading, then the indexes are fine (since they'll
> >>> run the SQL manually), it's just new installs that have auto.ddl on, so
> >>> perhaps we should include info in the install guide about it. The index
> only
> >>> script is a good idea and is an approach I use for Profile2 as well.
> >>>
> >>> cheers,
> >>> Steve
> >>>
> >>>
> >>> On 14/05/2010, at 8:28 PM, Nuno Fernandes wrote:
> >>>
> >>>
> >>>
> >>> 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,
> >>> 3.3.x, 3.5.0-Beta-2]:
> >>> 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 <[hidden email]>
> 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 }
> >>>
> >>>
> >>>
> >>>
> >>> _______________________________________________
> >>> sakai-qa mailing list
> >>> [hidden email]
> >>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
> >>>
> >>> TO UNSUBSCRIBE: send email to
> >>> [hidden email] with a subject of
> "unsubscribe"
> >>>
> >>> _______________________________________________
> >>> sakai-dev mailing list
> >>> [hidden email]
> >>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> >>>
> >>> TO UNSUBSCRIBE: send email to
> >>> [hidden email] with a subject of
> >>> "unsubscribe"
> >>
> >>
> >>
> >> --
> >> 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 }
> >
> > _______________________________________________
> > sakai-qa mailing list
> > [hidden email]
> > http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
> >
> > TO UNSUBSCRIBE: send email to
> [hidden email]
> > with a subject of "unsubscribe"
> >
>
>
>
> --
> Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile
> _______________________________________________
> sakai-dev mailing list
> [hidden email]
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> [hidden email] with a subject of
> "unsubscribe"
>



--
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 }

_______________________________________________
sakai-dev mailing list
[hidden email]
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to [hidden email] with a subject of "unsubscribe"