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

Mon May 16 16:54:17 PDT 2011

I've had a chat with Earle N off list and it seems that setting the length to 75000 in the hibernate mapping forces the fields to mediumtext, in both MySQL5InnoDBDialect and MySQLInnoDBDialect, and that should be a CLOB in Oracle. Testing this shows that it is working correctly again, for MySQL under both dialects (MySQL 5.1.40, Sakai trunk)

However, I am unclear about what this means for Oracle. I was under the impression that an Oracle CLOB had to have some conversion done to it before it could be used as a string? If not, then increasing the length of the fields could be the solution

current:
<property name="notes" column="NOTES" type="string" length="4000" />

proposed:
<property name="notes" column="NOTES" type="string" length="75000" />

Duffy, to answer your question, MySQLInnoDBDialect should work fine for MySQL 5.1. YMMV though!

In any case, we could fix this issue in common pretty quickly if a person knowledgeable in Oracle can confirm that the CLOBS are ok.

thanks,
Steve

On 17/05/2011, at 6:17 AM, Duffy Gillman wrote:

> For what its worth I have the same trouble with MySQL 5.1.53, using MySQL5InnoDBDialect and had supported it locally by modifying the mapping to use "text". I had not understood the objection to this mapping - something about it causing Hibernate to use BLOB fields in Oracle. I am not aware of the impact that has for Oracle installations.
> 
> I see, Steve, that you note the failure of MySQL5InnoDBDialect in a comment on June 29, 2010 [1]. I had assumed that MySQL5InnoDBDialect was the appropriate dialect for anything after 5.0. Should I have any concern about MySQLInnoDBDialect, the pre-5.0 dialect, running against a 5.1 version of MySQL?
> 
> - Duffy Gillman
> 
>   Sr. Software Engineer
>   The rSmart Group, Inc.
> 
> [1] https://jira.sakaiproject.org/browse/SAK-17838?focusedCommentId=101344&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-101344
> 
> 
> On May 12, 2011, at 7:36 PM, Steve Swinsburg wrote:
> 
>> 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
>> 
>> _______________________________________________
>> 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"
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110517/a10bcfa9/attachment.html 