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

[Matthew Jones]

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