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

Nuno Fernandes nuno at ufp.edu.pt
Mon May 17 03:31:03 PDT 2010


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> <nuno at ufp.edu.pt>
>> Date: May 14, 2010 7:43:28 AM EDT
>> To: Steve Swinsburg <steve.swinsburg at gmail.com> <steve.swinsburg at gmail.com>
>> Cc: Anthony Whyte <arwhyte at umich.edu> <arwhyte at umich.edu>, Holladay Bryan <bahollad at indiana.edu> <bahollad at indiana.edu>, Sakai QA <sakai-qa at collab.sakaiproject.org> <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> <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> <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 }
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-qa/attachments/20100517/911b042e/attachment-0001.html 


More information about the sakai-qa mailing list