Uploaded image for project: 'Jira Service Management Server and Data Center'
  1. Jira Service Management Server and Data Center
  2. JSDSERVER-2033

SQL Exception Prevents New Mail From Being Processed

    XMLWordPrintable

Details

    Description

      Experienced Behavior

      The JIRA Service Desk mail handler repeatedly tries to process a single message that throws an SQL Exception when inserting into `AO_2C4E5C_MAILITEMAUDIT`. This prevents processing of any new messages.

      2015-06-25 21:31:42,530 ERROR [] atlassian-scheduler-quartz1.clustered_Worker-4 anonymous     MailJobRunner Failed
      com.mysema.query.QueryException: Caught SQLException for insert into `AO_2C4E5C_MAILITEMAUDIT` (`MAIL_CHANNEL_ID`, `MAIL_ITEM_ID`, `HANDLER_NAME_KEY`, `RESULT_STATUS`, `MESSAGE`, `FROM_ADDRESS`, `MAIL_CHANNEL_NAME`, `SUBJECT`, `ISSUE_KEY`, `NO_OF_RETRY`, `CREATED_TIMESTAMP`, `UPDATED_TIMESTAMP`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      	at com.mysema.query.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:38)
      	at com.mysema.query.sql.Configuration.translate(Configuration.java:514)
      	at com.mysema.query.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:380)
      	at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper$1.apply(QueryDslHelper.java:177)
      	at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper$1.apply(QueryDslHelper.java:141)
      	at com.atlassian.pocketknife.internal.querydsl.QueryFactoryImpl.insert(QueryFactoryImpl.java:161)
      	at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.create(QueryDslHelper.java:140)
      	at com.atlassian.jira.internal.mail.processor.feature.audit.mailitem.MailItemAuditStore.createMailItemAudit(MailItemAuditStore.java:106)
      	at com.atlassian.jira.internal.mail.processor.feature.audit.mailitem.MailItemAuditManager.saveMailItemAudit(MailItemAuditManager.java:40)
      	at com.atlassian.jira.internal.mail.processor.feature.processor.MailHandlerWorker.saveAuditLog(MailHandlerWorker.java:143)
      	at com.atlassian.jira.internal.mail.processor.feature.processor.MailHandlerWorker.handle(MailHandlerWorker.java:95)
      	at com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorWorker.processMailItem(MailProcessorWorker.java:149)
      	at com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorWorker.process(MailProcessorWorker.java:83)
      	at com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorService.run(MailProcessorService.java:41)
      	at com.atlassian.jira.internal.mail.processor.services.MailProcessorExecutor.run(MailProcessorExecutor.java:34)
      	at com.atlassian.jira.internal.mail.processor.services.AbstractMailExecutor.execute(AbstractMailExecutor.java:48)
      	at com.atlassian.jira.internal.mail.processor.services.MailJobRunner.runJob(MailJobRunner.java:38)
      	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:135)
      	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:101)
      	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:80)
      	at com.atlassian.scheduler.quartz1.Quartz1Job.execute(Quartz1Job.java:32)
      	at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
      	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
      Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x9C\x0D\x0A...' for column 'MESSAGE' at row 1
      	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
      	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
      	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
      	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
      	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
      	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
      	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
      	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
      	at com.mysema.query.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:366)
      	... 20 more
      

      The example above is caused by an email containing 4-byte UTF-8 and JIRA running on MySQL. See JRA-21224 for details.

      • The offending character is in the message subject and as a result JIRA Service Desk tries to insert this character into `AO_2C4E5C_MAILITEMAUDIT`

      Expected Behavior:

      JIRA Service Desk mail handler should mark this message as FAILED and continue to the next new message.

      Workaround

      First, enable debug logging so that you can tell which message is causing problems.

      1. Use these instructions to add the com.atlassian.mail.incoming package and set it for DEBUG.
      2. After waiting a few minutes for JIRA to attempt processing mail, review the log file to find the ID of the offending message.
        • JIRA_HOME/log/atlassian-jira-incoming-mail.log
        • Look for the following:
          2015-06-25 21:31:42,483 DEBUG [] atlassian-scheduler-quartz1.clustered_Worker-4 anonymous     Found 20 mail item(s) for processing ...
          2015-06-25 21:31:42,483 DEBUG [] atlassian-scheduler-quartz1.clustered_Worker-4 anonymous     Starts processing mail item [id: 2222] ...
          2015-06-25 21:31:42,483 DEBUG [] atlassian-scheduler-quartz1.clustered_Worker-4 anonymous     Checking if mail item [id: 2222] is a mail loop ...
          ...
          2015-06-25 21:31:42,526 ERROR [] atlassian-scheduler-quartz1.clustered_Worker-4 anonymous     Exception when MailHandlerWorker handles message: 
          com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting:
          ...
          
        • In this example ID 2222 is the offending message.

      The offending message must be removed in order for the mail processor to process any further mail. If you wish to recover the email content before deletion, follow the KB below:

      Then you can proceed with deleting the offending message using the steps below:

      1. Backup the database
      2. Delete the message using the commands below:
        delete from "AO_2C4E5C_MAILITEMCHUNK" where "MAIL_ITEM_ID" = <id from above>;
        delete from "AO_2C4E5C_MAILITEM" where "ID" = <id from above>;
        

      NOTE: For Cloud customer, please inform support team to perform troubleshooting and workaround for this bug.

      Attachments

        Issue Links

          Activity

            People

              dtrinh@atlassian.com Diep Ngoc Trinh (Inactive)
              bcruz@atlassian.com Brian Cruz (Inactive)
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Backbone Issue Sync