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

[Matthew Jones]

Seeing that we have already done this long->clob for 2.5 without the
conversion or these new columns that are created with the conversion,
it seems like it doesn't matter when do this. You should be able to
run it now before you upgrade if you wanted and had a lot of rows. I
will verify this though before the final release notes as I haven't
actually run the conversion script on the clob column.

On Thu, Jun 11, 2009 at 5:45 PM, Kevin Chan<kevin at media.berkeley.edu> wrote:
> 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"
>