[Building Sakai] Regarding SAK-16463 - 2.6.x oracle database conversion needed

[Kevin Chan]

Hi Matthew,

Should the "alter table" statement be applied before or after the 
mailarchive binary-conversion upgrade for those of us moving from 2.5 to 
2.6? I would seem safest to apply this statement after the conversion is 
complete.

   Kevin Chan

   Operations Team
   Educational Technology Services
   University of California, Berkeley


On 6/11/09 12:41 PM, Matthew Jones wrote:
> We resolved the last blocker today at the release meeting for rc05 on
> mailarchive. There were some issues with this worth mentioning
> specifically for those running Oracle.
>
> Summary:
> ---------------
> SAK-16463 (Oracle DB write failure in Email Archive). This caused an
> oracle stack dump when inserting a medium sized message in email
> archive because of a change made in SAK-13584 which added a new column
> (body) as a clob field. In Oracle you cannot bind more than 4000 bytes
> to a LOB and LONG columns in 1 statement (ORA-22295). Previously (and
> currently) mailarchive serialized the body into XML and stored in in
> one column, however in 2.6 it is separated to allow searching,
> filtering and sorting.
>
> Solution:
> ------------
> Run an SQL column conversion in oracle to convert this column. This
> script will be added to the 2.6 conversion scripts. This was
> originally proposed by Michael Applyby and verified by myself. We had
> a couple other ideas to fix this issue but this seemed the fastest.
> There are some future tasks related to improving this. We have been
> running this column as clob as Michigan for awhile now. Michael noted
> that on 40,000 rows it took him 14 minutes, so it might take awhile.
>
> SQL: alter table MAILARCHIVE_MESSAGE modify XML CLOB;
>
> Related Information:
> ----------------------------
> There is a database conversion recommended in mail archive for 2.6 to
> take advantage of the new sorting and searching features. It is part
> of SAK-13584 and is applicable to all database users to improve
> performance. The information will be formatted better on the official
> release notes but is contanted in the subversion logs at the moment if
> you want to get a jump on it. All new emails inserted into the archive
> will be inserted both into the old serialized xml field and the new
> body/subject columns.
>
> http://source.sakaiproject.org/viewsvn/mailarchive/trunk/mailarchive-runconversion.sh?view=log
>
> Future Work:
> ------------------
> I created a SAK (SAK-16539) to track some future related work that
> came up of this issue to clean out the duplicated data that is still
> being kept both in the xml and the new columns. By keeping the body in
> the XML, it still has to deserialize it for every item on the index,
> slowing down processing, especially noticable on larger messages. This
> would require another conversion later (2.6.?). It could have been
> useful to do this as to resolve this issue, then this column wouldn't
> needed to be converted. However this task was too big to finish
> quickly and this Oracle issue was a blocker on the release.
>
> On Thu, Jun 11, 2009 at 12:59 PM, Anthony Whyte<arwhyte at umich.edu>  wrote:
>    
>> Matt--could you draft a message to the dev/prod lists that lays out the
>> problem and the proposed solution as well as notes any conversion script
>> issues.
>> Cheers,
>>
>> Anth
>>      
> _______________________________________________
> 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/20090611/6fa9b98f/attachment.html