[Building Sakai] Sakai 2.7 with mysql 5.5 and SAKAI_PERSON_T?
John Bush
john.bush at rsmart.com
Wed May 18 13:40:48 PDT 2011
This does not work for Oracle, the tries to create everything like longs,
hibernate generates this:
CREATE
table SAKAI_PERSON_T
(
ID number(19,0) not null,
PERSON_TYPE varchar2(3 char) not null,
VERSION number(10,0) not null,
UUID varchar2(36 char) not null unique,
LAST_MODIFIED_BY varchar2(36 char) not null,
LAST_MODIFIED_DATE timestamp not null,
CREATED_BY varchar2(36 char) not null,
CREATED_DATE timestamp not null,
AGENT_UUID varchar2(99 char) not null,
TYPE_UUID varchar2(36 char) not null,
COMMON_NAME varchar2(255 char),
DESCRIPTION varchar2(255 char),
SEE_ALSO varchar2(255 char),
STREET varchar2(255 char),
SURNAME varchar2(255 char),
TELEPHONE_NUMBER varchar2(255 char),
FAX_NUMBER varchar2(255 char),
LOCALITY_NAME varchar2(255 char),
OU varchar2(255 char),
PHYSICAL_DELIVERY_OFFICE_NAME varchar2(255 char),
POSTAL_ADDRESS varchar2(255 char),
POSTAL_CODE varchar2(255 char),
POST_OFFICE_BOX varchar2(255 char),
STATE_PROVINCE_NAME varchar2(255 char),
STREET_ADDRESS varchar2(255 char),
TITLE varchar2(255 char),
BUSINESS_CATEGORY varchar2(255 char),
CAR_LICENSE varchar2(255 char),
DEPARTMENT_NUMBER varchar2(255 char),
DISPLAY_NAME varchar2(255 char),
EMPLOYEE_NUMBER varchar2(255 char),
EMPLOYEE_TYPE varchar2(255 char),
GIVEN_NAME varchar2(255 char),
HOME_PHONE varchar2(255 char),
HOME_POSTAL_ADDRESS varchar2(255 char),
INITIALS varchar2(255 char),
JPEG_PHOTO blob,
LABELED_URI varchar2(255 char),
MAIL varchar2(255 char),
MANAGER varchar2(255 char),
MOBILE varchar2(255 char),
ORGANIZATION varchar2(255 char),
PAGER varchar2(255 char),
PREFERRED_LANGUAGE varchar2(255 char),
ROOM_NUMBER varchar2(255 char),
SECRETARY varchar2(255 char),
UID_C varchar2(255 char),
USER_CERTIFICATE raw(255),
USER_PKCS12 raw(255),
USER_SMIME_CERTIFICATE raw(255),
X500_UNIQUE_ID varchar2(255 char),
AFFILIATION varchar2(255 char),
ENTITLEMENT varchar2(255 char),
NICKNAME varchar2(255 char),
ORG_DN varchar2(255 char),
ORG_UNIT_DN varchar2(255 char),
PRIMARY_AFFILIATION varchar2(255 char),
PRIMARY_ORG_UNIT_DN varchar2(255 char),
PRINCIPAL_NAME varchar2(255 char),
CAMPUS varchar2(255 char),
HIDE_PRIVATE_INFO number(1,0),
HIDE_PUBLIC_INFO number(1,0),
NOTES long,
PICTURE_URL varchar2(255 char),
SYSTEM_PICTURE_PREFERRED number(1,0),
ferpaEnabled number(1,0),
dateOfBirth date,
locked number(1,0),
FAVOURITE_BOOKS long,
FAVOURITE_TV_SHOWS long,
FAVOURITE_MOVIES long,
FAVOURITE_QUOTES long,
EDUCATION_COURSE long,
EDUCATION_SUBJECTS long,
NORMALIZEDMOBILE varchar2(255 char),
STAFF_PROFILE long,
UNIVERSITY_PROFILE_URL long,
ACADEMIC_PROFILE_URL long,
PUBLICATIONS long,
BUSINESS_BIOGRAPHY long,
primary key (ID),
unique (AGENT_UUID, TYPE_UUID)
)
12:52:55 [CREATE - 0 row(s), 0.000 secs] [Error Code: 1754, SQL State:
42000] ORA-01754: a table may contain only one column of type LONG
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000
sec [0 successful, 0 warnings, 1 errors]
I had the same problem back in 2.7 with db2, see
https://jira.sakaiproject.org/browse/KNL-611. Db2 will not allow you to
have that many varchar2 with 4000k length in one row, cause there is some
sort of limitation on row size.
I ended up having to create a sakai specific dialect to deal with this, but
really the problem is with the hbm, imho. Hibernate dialects look at the
type and the length to make database type decisions. There where/are?
places in sakai that do not specify lengths. That's one way you get into
this. People should always specify lengths. For the db2 work I ended up
writing some xsl that transforms the hbms at runtime, cause I got sick of
these battles, but that was more about Blob vs binary then varchar vs
clob. I digress...
I believe the proper solution here is to specify a type of text and a big
length, Earle's suggestion of 75k works, I've used 10000000 before, whatever
it doesn't really matter. That works on mysql (both dialects), db2, and
oracle, as all of those guys will pick a clob or text field.
This is how PresentationImpl.hbm.xml in osp deals with it.
That is how we shipped out 2.7 code in regard to SAKAI_PERSON_T specifically
. I can't find all the discussion about that, but from what I remember
there was some apprehension to the use of clobs in Oracle. I'm hoping maybe
that apprehension is gone now and we can just fix this. I know we tested on
db2, oracle, mysql (both dialects) without issue. I do not know about mysql
5.5, we tested using 5.0/5.1.
The only issue would be converting SAKAI_PERSON_T in Oracle, which I think
might need a temporary column to do that, I don't think you can just alter
the table, but I might be wrong about that.
On Mon, May 16, 2011 at 4:54 PM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:
> 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"
>
>
>
>
> _______________________________________________
> 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"
>
--
John Bush
602-490-0470
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110518/72152033/attachment.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sakai_person.patch
Type: application/octet-stream
Size: 3547 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110518/72152033/attachment.obj
More information about the sakai-dev
mailing list