[Building Sakai] Sakai 2.7 with mysql 5.5 and SAKAI_PERSON_T?

[Steve Swinsburg]

Profile2 uses the original db table from Profile classique and the original SakaiPerson API to get and set that data. These live in the 'common' project.

The SAKAI_PERSON_T has many columns in it, many of which are meant to be 'text' on MySQL and varchar2(4000) on Oracle. Not the greatest DB design but one which was created a long time ago and we tried to work within the bounds of it when creating Profile2. 

In hindsight we would have structured this differently, and that reimplementation is still on the cards (PRFL-230).

However, the issue with what you patched is that much more data is allowed to be entered as text in the fields that are marked as so, but setting them to varchar(255) at the DB level will cause data truncation and probably insert errors.

The issue is with Hibernate and it not selecting the correct column type when creating the table. I've spent countless hours trying to figure out its nuances and inconsistencies in this regard, and many people have weighed in to the discussion as well:
https://jira.sakaiproject.org/browse/SAK-17838

Looks like we need to either revisit this hibernate mapping/dialect issue, or abandon the current SakaiPerson backend and reimplement it as per (PRFL-230).

cheers,
Steve



On 13/05/2011, at 12:12 PM, Aaron Zeckoski wrote:

> Unfortunately, setting MySQLInnoDBDialect on MySQL 5.5 yields hundreds
> of insert errors (on OSX anyway). Flipping this to MySQL5InnoDBDialect
> yields only one error and it is the one I reported. I think perhaps
> the problem is with SAKAI_PERSON_T and the way it is setup.
> 
> For now I can just patch profile2 for my own local install but it's
> probably worth a note that profile2 is not compatible with mysql 5.5.
> 
> -AZ
> 
> 
> On Thu, May 12, 2011 at 10:00 PM, Steve Swinsburg
> <steve.swinsburg at gmail.com> wrote:
>> They actually need to be text, not varchar.
>> 
>> From what I have seen, this is hibernate dialect based. If you have the correct dialect it should render them as text instead of varchar.
>> 
>> https://jira.sakaiproject.org/browse/PRFL-392
>> 
>> cheers,
>> Steve
>> 
>> 
>> On 13/05/2011, at 7:13 AM, Aaron Zeckoski wrote:
>> 
>>> I just upgraded my local database to mysql 5.5 (from 5.1) and I am
>>> finding that I cannot get sakai 2.7 to startup with a fresh DB. I am
>>> using the newest connector (5.1.16) but when I try to startup Sakai I
>>> get this failure message when trying to create the SAKAI_PERSON_T
>>> table:
>>> 
>>> ERROR 1118 (42000): Row size too large
>>> 
>>> 2011-05-12 17:02:27,386  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
>>> 
>>> I think this is caused by making varchars which are larger than 255
>>> chars. Dropping those down to 255 chars allows this to run.
>>> 
>>> Everything else seems to work fine.
>>> -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