[WG: Sakai QA] [Building Sakai] Fwd: 2.7.0 conversion scripts: index variations (msgcntr, profile2, sitestats)
Steve Swinsburg
steve.swinsburg at gmail.com
Mon May 17 04:48:48 PDT 2010
+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 <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
> _______________________________________________
> 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"
More information about the sakai-qa
mailing list