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

Oracle DB write failure in Email Archive

    Details

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

      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

          Attachments

            Issue Links

              Activity

              Hide
              arwhyte 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
              arwhyte 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
              jonespm 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
              jonespm 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
              jonespm Matthew Jones added a comment -

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

              Show
              jonespm Matthew Jones added a comment - Attaching a new patch that mentions the index issue and removes this from the global conversion script.
              Hide
              jonespm 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
              jonespm 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
              arwhyte Anthony Whyte added a comment -

              In 2.6.0.

              Show
              arwhyte Anthony Whyte added a comment - In 2.6.0.

                People

                • Assignee:
                  jonespm Matthew Jones
                  Reporter:
                  michael.appleby@yale.edu Michael Appleby
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Git Source Code