[Building Sakai] Roster/Profile2 failures in sakai 2.7.x?

Steve Swinsburg steve.swinsburg at gmail.com
Tue Aug 23 16:03:36 PDT 2011 

AFAIK the issue only manifests itself in MySQL 5.5 or when using a certain Hibernate dialect. 

MySQL 5.5 isn't on the officially supported list of MySQL versions and there is a known workaround for the dialect issue (if people get adventurous and change it, see end of https://jira.sakaiproject.org/browse/SAK-17838)

cheers,
Steve




On 23/08/2011, at 10:05 PM, Aaron Zeckoski wrote:

> Well, I already fixed it for myself but I am more concerned about new
> users who go to install 2.7 and get this failure. What are we doing to
> support them (aside from waiting for things to fail and tell them
> about https://jira.sakaiproject.org/browse/SAK-20598 in the jira).
> Aside from just changing the SQL, I don't have a solution for the
> problem but maybe someone can get creative.
> 
> -AZ
> 
> 
> On Tue, Aug 23, 2011 at 12:23 AM, Steve Swinsburg
> <steve.swinsburg at gmail.com> wrote:
>> The 4000 char limit issue has been fixed in trunk and 2.8.x by forcing the columns to longtext (and clob on oracle) but it's not in the 2.7 branch.
>> 
>> https://jira.sakaiproject.org/browse/SAK-20598
>> 
>> You could run the conversion attached to that JIRA though.
>> 
>> cheers,
>> S
>> 
>> On 23/08/2011, at 1:37 PM, Aaron Zeckoski wrote:
>> 
>>> On Mon, Aug 22, 2011 at 10:51 PM, Steve Swinsburg
>>> <steve.swinsburg at gmail.com> > Can you turn on the hibernate sql
>>> logging and send it so I can see the query it's failing on?
>>> 
>>> 2011-08-22 23:30:21,102 ERROR http-8080-Processor22
>>> org.sakaiproject.profile2.logic.SakaiProxyImpl -
>>> SakaiProxy.getSakaiPerson(): Couldn't get SakaiPerson for: admin :
>>> class org.springframework.dao.InvalidDataAccessResourceUsageException
>>> : could not execute query; nested exception is
>>> org.hibernate.exception.SQLGrammarException: could not execute query
>>> Hibernate: select personimpl0_.ID as ID115_, personimpl0_.VERSION as
>>> VERSION115_, personimpl0_.UUID as UUID115_,
>>> personimpl0_.LAST_MODIFIED_BY as LAST5_115_,
>>> personimpl0_.LAST_MODIFIED_DATE as LAST6_115_, personimpl0_.CREATED_BY
>>> as CREATED7_115_, personimpl0_.CREATED_DATE as CREATED8_115_,
>>> personimpl0_.AGENT_UUID as AGENT9_115_, personimpl0_.TYPE_UUID as
>>> TYPE10_115_, personimpl0_.COMMON_NAME as COMMON11_115_,
>>> personimpl0_.DESCRIPTION as DESCRIP12_115_, personimpl0_.SEE_ALSO as
>>> SEE13_115_, personimpl0_.STREET as STREET115_, personimpl0_.SURNAME as
>>> SURNAME115_, personimpl0_.TELEPHONE_NUMBER as TELEPHONE16_115_,
>>> personimpl0_.FAX_NUMBER as FAX17_115_, personimpl0_.LOCALITY_NAME as
>>> LOCALITY18_115_, personimpl0_.OU as OU115_,
>>> personimpl0_.PHYSICAL_DELIVERY_OFFICE_NAME as PHYSICAL20_115_,
>>> personimpl0_.POSTAL_ADDRESS as POSTAL21_115_, personimpl0_.POSTAL_CODE
>>> as POSTAL22_115_, personimpl0_.POST_OFFICE_BOX as POST23_115_,
>>> personimpl0_.STATE_PROVINCE_NAME as STATE24_115_,
>>> personimpl0_.STREET_ADDRESS as STREET25_115_, personimpl0_.TITLE as
>>> TITLE115_, personimpl0_.BUSINESS_CATEGORY as BUSINESS27_115_,
>>> personimpl0_.CAR_LICENSE as CAR28_115_, personimpl0_.DEPARTMENT_NUMBER
>>> as DEPARTMENT29_115_, personimpl0_.DISPLAY_NAME as DISPLAY30_115_,
>>> personimpl0_.EMPLOYEE_NUMBER as EMPLOYEE31_115_,
>>> personimpl0_.EMPLOYEE_TYPE as EMPLOYEE32_115_, personimpl0_.GIVEN_NAME
>>> as GIVEN33_115_, personimpl0_.HOME_PHONE as HOME34_115_,
>>> personimpl0_.HOME_POSTAL_ADDRESS as HOME35_115_, personimpl0_.INITIALS
>>> as INITIALS115_, personimpl0_.JPEG_PHOTO as JPEG37_115_,
>>> personimpl0_.LABELED_URI as LABELED38_115_, personimpl0_.MAIL as
>>> MAIL115_, personimpl0_.MANAGER as MANAGER115_, personimpl0_.MOBILE as
>>> MOBILE115_, personimpl0_.ORGANIZATION as ORGANIZ42_115_,
>>> personimpl0_.PAGER as PAGER115_, personimpl0_.PREFERRED_LANGUAGE as
>>> PREFERRED44_115_, personimpl0_.ROOM_NUMBER as ROOM45_115_,
>>> personimpl0_.SECRETARY as SECRETARY115_, personimpl0_.UID_C as
>>> UID47_115_, personimpl0_.USER_CERTIFICATE as USER48_115_,
>>> personimpl0_.USER_PKCS12 as USER49_115_,
>>> personimpl0_.USER_SMIME_CERTIFICATE as USER50_115_,
>>> personimpl0_.X500_UNIQUE_ID as X51_115_, personimpl0_.AFFILIATION as
>>> AFFILIA52_115_, personimpl0_.ENTITLEMENT as ENTITLE53_115_,
>>> personimpl0_.NICKNAME as NICKNAME115_, personimpl0_.ORG_DN as
>>> ORG55_115_, personimpl0_.ORG_UNIT_DN as ORG56_115_,
>>> personimpl0_.PRIMARY_AFFILIATION as PRIMARY57_115_,
>>> personimpl0_.PRIMARY_ORG_UNIT_DN as PRIMARY58_115_,
>>> personimpl0_.PRINCIPAL_NAME as PRINCIPAL59_115_, personimpl0_.CAMPUS
>>> as CAMPUS115_, personimpl0_.HIDE_PRIVATE_INFO as HIDE61_115_,
>>> personimpl0_.HIDE_PUBLIC_INFO as HIDE62_115_, personimpl0_.NOTES as
>>> NOTES115_, personimpl0_.PICTURE_URL as PICTURE64_115_,
>>> personimpl0_.SYSTEM_PICTURE_PREFERRED as SYSTEM65_115_,
>>> personimpl0_.ferpaEnabled as ferpaEn66_115_, personimpl0_.dateOfBirth
>>> as dateOfB67_115_, personimpl0_.locked as locked115_,
>>> personimpl0_.FAVOURITE_BOOKS as FAVOURITE69_115_,
>>> personimpl0_.FAVOURITE_TV_SHOWS as FAVOURITE70_115_,
>>> personimpl0_.FAVOURITE_MOVIES as FAVOURITE71_115_,
>>> personimpl0_.FAVOURITE_QUOTES as FAVOURITE72_115_,
>>> personimpl0_.EDUCATION_COURSE as EDUCATION73_115_,
>>> personimpl0_.EDUCATION_SUBJECTS as EDUCATION74_115_,
>>> personimpl0_.NORMALIZEDMOBILE as NORMALI75_115_,
>>> personimpl0_.PERSON_TYPE as PERSON2_115_ from SAKAI_PERSON_T
>>> personimpl0_ where personimpl0_.AGENT_UUID=? and
>>> personimpl0_.TYPE_UUID=?
>>> 2011-08-22 23:30:21,102  WARN http-8080-Processor22
>>> org.hibernate.util.JDBCExceptionReporter - SQL Error: 1146, SQLState:
>>> 42S02
>>> 2011-08-22 23:30:21,102 ERROR http-8080-Processor22
>>> org.hibernate.util.JDBCExceptionReporter - Table
>>> 'sakai_27x.sakai_person_t' doesn't exist
>>> 2011-08-22 23:30:21,103 ERROR http-8080-Processor22
>>> org.sakaiproject.profile2.logic.SakaiProxyImpl -
>>> SakaiProxy.getSakaiPerson(): Couldn't get SakaiPerson for:
>>> 30ae7737-b92b-400f-9f17-26aefefa84be : class
>>> org.springframework.dao.InvalidDataAccessResourceUsageException :
>>> could not execute query; nested exception is
>>> org.hibernate.exception.SQLGrammarException: could not execute query
>>> 
>>> What's weird is that this is a fresh DB and a clean install of Sakai
>>> 2.7.x but there is not sakai_person_t, only a sakai_person_meta_t.
>>> 
>>> 
>>>> Also, what db/version?
>>> 
>>> Mysql Server version: 5.5.10-log MySQL Community Server (GPL)
>>> 
>>> 
>>> Looking back in the logs from the first startup, I am seeing that the
>>> sakai_person_t cannot be created because of the 4000 char varchars. I
>>> seem to recall this happened before and I fixed it by changing the
>>> query to reduce all the 4000 down to 255. Is there a more permanent
>>> fix for this issue because even though I can hack around this, I don't
>>> guess that it is something we should leave like this for the general
>>> user.
>>> 
>>> 
>>> 2011-08-22 23:28:45,715  WARN main
>>> org.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean
>>> - Unsuccessful schema statement: create table SAKAI_PERSON_T (ID
>>> bigint not null auto_increment, PERSON_TYPE varchar(3) not null,
>>> VERSION integer not null, UUID varchar(36) not null unique,
>>> LAST_MODIFIED_BY varchar(36) not null, LAST_MODIFIED_DATE datetime not
>>> null, CREATED_BY varchar(36) not null, CREATED_DATE datetime not null,
>>> AGENT_UUID varchar(99) not null, TYPE_UUID varchar(36) not null,
>>> COMMON_NAME varchar(255), DESCRIPTION varchar(255), SEE_ALSO
>>> varchar(255), STREET varchar(255), SURNAME varchar(255),
>>> TELEPHONE_NUMBER varchar(255), FAX_NUMBER varchar(255), LOCALITY_NAME
>>> varchar(255), OU varchar(255), PHYSICAL_DELIVERY_OFFICE_NAME
>>> varchar(255), POSTAL_ADDRESS varchar(255), POSTAL_CODE varchar(255),
>>> POST_OFFICE_BOX varchar(255), STATE_PROVINCE_NAME varchar(255),
>>> STREET_ADDRESS varchar(255), TITLE varchar(255), BUSINESS_CATEGORY
>>> varchar(255), CAR_LICENSE varchar(255), DEPARTMENT_NUMBER
>>> varchar(255), DISPLAY_NAME varchar(255), EMPLOYEE_NUMBER varchar(255),
>>> EMPLOYEE_TYPE varchar(255), GIVEN_NAME varchar(255), HOME_PHONE
>>> varchar(255), HOME_POSTAL_ADDRESS varchar(255), INITIALS varchar(255),
>>> JPEG_PHOTO blob, LABELED_URI varchar(255), MAIL varchar(255), MANAGER
>>> varchar(255), MOBILE varchar(255), ORGANIZATION varchar(255), PAGER
>>> varchar(255), PREFERRED_LANGUAGE varchar(255), ROOM_NUMBER
>>> varchar(255), SECRETARY varchar(255), UID_C varchar(255),
>>> USER_CERTIFICATE tinyblob, USER_PKCS12 tinyblob,
>>> USER_SMIME_CERTIFICATE tinyblob, X500_UNIQUE_ID varchar(255),
>>> AFFILIATION varchar(255), ENTITLEMENT varchar(255), NICKNAME
>>> varchar(255), ORG_DN varchar(255), ORG_UNIT_DN varchar(255),
>>> PRIMARY_AFFILIATION varchar(255), PRIMARY_ORG_UNIT_DN varchar(255),
>>> PRINCIPAL_NAME varchar(255), CAMPUS varchar(255), HIDE_PRIVATE_INFO
>>> bit, HIDE_PUBLIC_INFO bit, NOTES varchar(4000), PICTURE_URL
>>> varchar(255), SYSTEM_PICTURE_PREFERRED bit, ferpaEnabled bit,
>>> dateOfBirth date, locked bit, FAVOURITE_BOOKS varchar(4000),
>>> FAVOURITE_TV_SHOWS varchar(4000), FAVOURITE_MOVIES varchar(4000),
>>> FAVOURITE_QUOTES varchar(4000), EDUCATION_COURSE varchar(4000),
>>> EDUCATION_SUBJECTS varchar(4000), NORMALIZEDMOBILE varchar(255),
>>> primary key (ID), unique (AGENT_UUID, TYPE_UUID)) ENGINE=InnoDB
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size
>>> too large. The maximum row size for the used table type, not counting
>>> BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
>>> 
>>> 
>>> -AZ
>>> 
>>> 
>>>> On 23/08/2011, at 5:21 AM, Aaron Zeckoski wrote:
>>>> 
>>>>> I am having a problem which might totally be a local issue with my
>>>>> development environment so I wanted to see if anyone else is seeing
>>>>> this also (this is with the newest 2.7.x code). It causes roster to
>>>>> basically render a stacktrace on screen.
>>>>> 
>>>>> 2011-08-22 14:57:33,697 ERROR http-8080-Processor24
>>>>> org.sakaiproject.profile2.logic.SakaiProxyImpl -
>>>>> SakaiProxy.getSakaiPerson(): Couldn't get SakaiPerson for:
>>>>> caa3725b-8380-4f2f-ac3d-a637454e78c7 : class
>>>>> org.springframework.dao.InvalidDataAccessResourceUsageException :
>>>>> could not execute query; nested exception is
>>>>> org.hibernate.exception.SQLGrammarException: could not execute query
>>>>> 2011-08-22 14:57:33,697  WARN http-8080-Processor24
>>>>> org.hibernate.util.JDBCExceptionReporter - SQL Error: 1146, SQLState:
>>>>> 42S02
>>>>> 
>>>>> These users are mostly loaded via my local UDP but I also tried a site
>>>>> with only internal (DB stored) users and I get the same issue.
>>>>> Anyone else seeing anything like this?
>>>>> -AZ
>>>>> 
>>>>> 
>>>>> --
>>>>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>>>>> _______________________________________________
>>>>> 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"
>>>> 
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>> 
>> 
> 
> 
> 
> -- 
> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile

More information about the sakai-dev mailing list