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

Aaron Zeckoski azeckoski at unicon.net
Mon May 17 03:38:38 PDT 2010


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 <nuno at ufp.edu.pt> 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 <nuno at ufp.edu.pt> 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 <david.horwitz at uct.ac.za>
>> 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 <nuno at ufp.edu.pt>
>>> Date: May 14, 2010 7:43:28 AM EDT
>>> To: Steve Swinsburg <steve.swinsburg at gmail.com>
>>> Cc: Anthony Whyte <arwhyte at umich.edu>, Holladay Bryan
>>> <bahollad at indiana.edu>, Sakai QA <sakai-qa at collab.sakaiproject.org>
>>> 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
>>> <steve.swinsburg at gmail.com> 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 <arwhyte at umich.edu> wrote:
>>> Gents--I've been using liquibase to diff between a MySQL 2.6.2->2.7.0 db
>>> upgraded with the 2.7.0 conversion script and an auto-generated 2.7.0 db.
>>> There are a variety of indexes included in the conversion scripts that are
>>> absent when you generate a 2.7.0 db objects on a first startup (see below).
>>>
>>> Please review the indexes relevant to your projects.  I should note that
>>> when I checked 2.7.x regarding the index
>>>
>>> SST_EVENTS_DATE_IX unique  on sst_events(EVENT_DATE)
>>>
>>> SiteStats 2.1.x EventStatImpl.hbm.xml's hibernate-mapping includes:
>>>
>>> <property name="date" column="EVENT_DATE" type="date" not-null="true"
>>> index="SST_EVENTS_DATE_IX"/>
>>>
>>> The same is true when I check
>>>
>>> MFR_EMAIL_USER_ID_I unique  on mfr_email_notification_t(USER_ID)
>>>
>>> msgcntr-2.7.x EmailNotification.hbm.xml lists:
>>>
>>> <property name="userId">
>>>    <column name="USER_ID" length="255" not-null="true"
>>> index="MFR_EMAIL_USER_ID_I" />
>>> </property>
>>>
>>> I'm not sure (without digging deeper) why these particular indexes are
>>> not getting generated (or any of the others for that matter) when I run
>>> 2.7.x with an empty db instance for the first time.  Please see if you can
>>> confirm my findings (install and deploy 2.7.x against a new MySQL 5.1 sakai
>>> db).  If confirmed and you can fix it quickly, all the better as we are
>>> delaying the release of 2.7.x until we've resolved these db irregularities.
>>>
>>> Assuming the indexes are indeed missing for those who start with a clean
>>> 2.7.x db we'll need to sort out what's preventing the indexes from being
>>> generated.
>>>
>>> Cheers,
>>>
>>> Anth
>>>
>>> arwhyte-macbookpro-2010:liquibase-1.9.5 arwhyte$ java -jar
>>> ./liquibase-1.9.5.jar --driver=com.mysql.jdbc.Driver
>>> --url=jdbc:mysql://127.0.0.1:3306/sakai262to270convert --username=sakaiuser
>>> --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai270autogen
>>> --baseUsername=sakaiuser --basePassword=sushi
>>> Diff Results:
>>> Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai270autogen
>>> Target Database: sakaiuser
>>> jdbc:mysql://127.0.0.1:3306/sakai262to270convert
>>>
>>> Missing Indexes (auto-generated 2.7.x db):
>>>    MFR_EMAIL_CONTEXT_ID_I unique  on mfr_email_notification_t(CONTEXT_ID)
>>>    MFR_EMAIL_USER_ID_I unique  on mfr_email_notification_t(USER_ID)
>>>    PROFILE_FRIENDS_FRIEND_UUID_I unique  on
>>> profile_friends_t(FRIEND_UUID)
>>>    PROFILE_FRIENDS_USER_UUID_I unique  on profile_friends_t(USER_UUID)
>>>    PROFILE_IMAGES_IS_CURRENT_I unique  on profile_images_t(IS_CURRENT)
>>>    PROFILE_IMAGES_USER_UUID_I unique  on profile_images_t(USER_UUID)
>>>    SAKAI_PERSON_META_PROPERTY_I unique  on sakai_person_meta_t(PROPERTY)
>>>    SAKAI_PERSON_META_USER_UUID_I unique  on
>>> sakai_person_meta_t(USER_UUID)
>>>    SST_EVENTS_DATE_IX unique  on sst_events(EVENT_DATE)
>>>    SST_EVENTS_EVENT_ID_IX unique  on sst_events(EVENT_ID)
>>>    SST_EVENTS_SITEEVENTUSER_ID_IX unique  on sst_events(SITE_ID,
>>> EVENT_ID, USER_ID)
>>>    SST_EVENTS_SITE_ID_IX unique  on sst_events(SITE_ID)
>>>    SST_EVENTS_USER_ID_IX unique  on sst_events(USER_ID)
>>>    SST_PREFERENCES_SITE_ID_IX unique  on sst_preferences(SITE_ID)
>>>    SST_REPORTS_SITE_ID_IX unique  on sst_reports(SITE_ID)
>>>    SST_RESOURCES_DATE_IX unique  on sst_resources(RESOURCE_DATE)
>>>    SST_RESOURCES_RES_ACT_IDX unique  on sst_resources(RESOURCE_ACTION)
>>>    SST_RESOURCES_SITE_ID_IX unique  on sst_resources(SITE_ID)
>>>    SST_RESOURCES_USER_ID_IX unique  on sst_resources(USER_ID)
>>>    SST_SITEACTIVITY_DATE_IX unique  on sst_siteactivity(ACTIVITY_DATE)
>>>    SST_SITEACTIVITY_EVENT_ID_IX unique  on sst_siteactivity(EVENT_ID)
>>>    SST_SITEACTIVITY_SITE_ID_IX unique  on sst_siteactivity(SITE_ID)
>>>    SST_SITEVISITS_DATE_IX unique  on sst_sitevisits(VISITS_DATE)
>>>    SST_SITEVISITS_SITE_ID_IX unique  on sst_sitevisits(SITE_ID)
>>>
>>> Unexpected Indexes:
>>>    SITE_ID on sst_preferences(SITE_ID)
>>>
>>> Nuno--I believe the above unexpected index involves the CREATE TABLE
>>> statement that defines SITE_ID as NOT NULL unique followed by an index
>>> statement on the same field.  I assume "unique" needs to be removed from
>>> 2.1.x and a new release of sitestats 2.1.x generated.  My modified
>>> conversion script (not checked in yet) removed unique from the statement in
>>> the conversion scripts and the 2.6.2->2.7.0 upgraded db includes the key.
>>> The auto-generated 2.7.x db does not.
>>>
>>> EXAMPLE
>>>
>>> 2.6.2->2.7.0 conv script updated db (dump of the structure)
>>>
>>> CREATE TABLE `sst_events` (
>>>   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>>   `USER_ID` varchar(99) COLLATE utf8_bin NOT NULL,
>>>   `SITE_ID` varchar(99) COLLATE utf8_bin NOT NULL,
>>>   `EVENT_ID` varchar(32) COLLATE utf8_bin NOT NULL,
>>>   `EVENT_DATE` date NOT NULL,
>>>   `EVENT_COUNT` bigint(20) NOT NULL,
>>>   PRIMARY KEY (`ID`),
>>>   KEY `SST_EVENTS_SITE_ID_IX` (`SITE_ID`),
>>>   KEY `SST_EVENTS_USER_ID_IX` (`USER_ID`),
>>>   KEY `SST_EVENTS_EVENT_ID_IX` (`EVENT_ID`),
>>>   KEY `SST_EVENTS_DATE_IX` (`EVENT_DATE`),
>>>   KEY `SST_EVENTS_SITEEVENTUSER_ID_IX` (`SITE_ID`,`EVENT_ID`,`USER_ID`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>>>
>>>
>>> 2.7.0 auto-gen db (dump of the structure)
>>>
>>> CREATE TABLE `sst_events` (
>>>   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>>   `USER_ID` varchar(99) COLLATE utf8_bin NOT NULL,
>>>   `SITE_ID` varchar(99) COLLATE utf8_bin NOT NULL,
>>>   `EVENT_ID` varchar(32) COLLATE utf8_bin NOT NULL,
>>>   `EVENT_DATE` date NOT NULL,
>>>   `EVENT_COUNT` bigint(20) NOT NULL,
>>>   PRIMARY KEY (`ID`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>>>
>>>
>>>
>>> --
>>> Nuno Fernandes  .  { Analyst/Programmer }
>>>
>>> || web  . { http://www.ufp.pt  |  http://tinyurl.com/nfgrilo  |
>>> follow_me @ nfgrilo }
>>> || work . { Universidade Fernando Pessoa  |  Praça 9 de Abril, 349  |
>>> 4249-004 Porto }
>>>
>>>
>>> --
>>> Nuno Fernandes  .  { Analyst/Programmer }
>>>
>>> || web  . { http://www.ufp.pt  |  http://tinyurl.com/nfgrilo  |
>>> follow_me @ nfgrilo }
>>> || work . { Universidade Fernando Pessoa  |  Praça 9 de Abril, 349  |
>>> 4249-004 Porto }
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sakai-qa mailing list
>>> sakai-qa at collab.sakaiproject.org
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>>>
>>> TO UNSUBSCRIBE: send email to
>>> sakai-qa-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>>>
>>> _______________________________________________
>>> sakai-dev mailing list
>>> sakai-dev at collab.sakaiproject.org
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>
>>> TO UNSUBSCRIBE: send email to
>>> sakai-dev-unsubscribe at collab.sakaiproject.org 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
> sakai-qa at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-qa
>
> TO UNSUBSCRIBE: send email to sakai-qa-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"
>



-- 
Aaron Zeckoski - Software Engineer - http://tinyurl.com/azprofile


More information about the sakai-qa mailing list