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

Mon Aug 22 20:37:06 PDT 2011

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