Sakai
  1. Sakai
  2. SAK-16463

Oracle DB write failure in Email Archive

    Details

    • Type: Bug Bug
    • Status: Closed 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)

        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: