Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-16463

Oracle DB write failure in Email Archive

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.6.0
    • Fix Version/s: 2.6.0
    • Component/s: Email Archive
    • Labels:
      None
    • Environment:
      Oracle 10g
    • Previous Issue Keys:

      Description

      On Oracle the Email Archive can fail to store the message to MAILARCHIVE_MESSAGE. This results in a blank record in the Email Archive UI.

      Here is the exception:

      java.sql.SQLException: ORA-22295: cannot bind more than 4000 bytes data to LOB and LONG columns in 1 statement

      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
      at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1205)
      at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1039)
      at org.sakaiproject.util.BaseDbDoubleStorage.commitResource(BaseDbDoubleStorage.java:1326)
      at org.sakaiproject.mailarchive.impl.DbMailArchiveService$DbStorage.commitMessage(DbMailArchiveService.java:375)
      at org.sakaiproject.message.impl.BaseMessageService$BaseMessageChannelEdit.commitMessage(BaseMessageService.java:2679)
      at org.sakaiproject.message.impl.BaseMessageService$BaseMessageChannelEdit.commitMessage(BaseMessageService.java:2612)
      at org.sakaiproject.mailarchive.impl.BaseMailArchiveService$BaseMailArchiveChannelEdit.addMailArchiveMessage(BaseMailArchiveService.java:843)
      at org.sakaiproject.james.SakaiMailet.service(SakaiMailet.java:358)
      at org.apache.james.transport.LinearProcessor.service(LinearProcessor.java:413)
      at org.apache.james.transport.JamesSpoolManager.process(JamesSpoolManager.java:436)
      at org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.java:366)
      at org.apache.avalon.excalibur.thread.impl.ExecutableRunnable.execute(ExecutableRunnable.java:47)
      at org.apache.avalon.excalibur.thread.impl.WorkerThread.run(WorkerThread.java:80)

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            Anthony Whyte added a comment -

            After looking over the code and talking with Matt I recommend limiting the 2.6.0 fix to changing the datatype of the "XML" field in the mailarchive message table to eliminate the db write failure caused by trying to insert into a long and clob fields in the same statement. I would then close the issue and open a new one that recommends as Matt has suggested removing the redundant "body" and "subject" data elements from the "XML" field since this data is duplicated in the new subject and body fields. This would involve a bit of refactoring as well as conversion script adjustments. It could be targeted for 2.6.1 or 2.6.2.

            Show
            Anthony Whyte added a comment - After looking over the code and talking with Matt I recommend limiting the 2.6.0 fix to changing the datatype of the "XML" field in the mailarchive message table to eliminate the db write failure caused by trying to insert into a long and clob fields in the same statement. I would then close the issue and open a new one that recommends as Matt has suggested removing the redundant "body" and "subject" data elements from the "XML" field since this data is duplicated in the new subject and body fields. This would involve a bit of refactoring as well as conversion script adjustments. It could be targeted for 2.6.1 or 2.6.2.
            Hide
            Matthew Jones added a comment -

            This patch just does a the database conversion that Michael suggested. I'll open a new SAK that has my concerns in it. This does fix the problem as he described. He has a possibly long conversion time. We already did the conversion to clob at Michigan sometime in the past on this column and it works fine.

            Show
            Matthew Jones added a comment - This patch just does a the database conversion that Michael suggested. I'll open a new SAK that has my concerns in it. This does fix the problem as he described. He has a possibly long conversion time. We already did the conversion to clob at Michigan sometime in the past on this column and it works fine.
            Hide
            Matthew Jones added a comment -

            Attaching a new patch that mentions the index issue and removes this from the global conversion script.

            Show
            Matthew Jones added a comment - Attaching a new patch that mentions the index issue and removes this from the global conversion script.
            Hide
            Matthew Jones added a comment -

            Patch file was removed because it was incomplete but fix was committed. I added additional comments concerning the index problem that came up on the mailing list which will also be mentioned in the release notes.

            Show
            Matthew Jones added a comment - Patch file was removed because it was incomplete but fix was committed. I added additional comments concerning the index problem that came up on the mailing list which will also be mentioned in the release notes.
            Hide
            Anthony Whyte added a comment -

            In 2.6.0.

            Show
            Anthony Whyte added a comment - In 2.6.0.

              People

              • Assignee:
                Matthew Jones
                Reporter:
                Michael Appleby
              • Votes:
                1 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Development