Issue creation via email fails if email subject exceeds 255 bytes causing Mail handler to stop processing

Problem Description

Incoming emails with subjects exceeding 255 bytes (default AO_2C4E5C_MAILITEM.SUBJECT column length) will cause the MailPullerWorker to fail and the Mail Handler to stop processing further incoming mails.

Diagnostic

On the incoming mail logs, we can see the following error:

• When using Oracle:

  2020-09-07 07:44:30,254+0300 ERROR [] Caesium-1-3 ServiceRunner Exception when MailPullerWorker pulls emails:
  com.querydsl.core.QueryException: Caught SQLException for insert into "AO_2C4E5C_MAILITEM" ("MAIL_CONNECTION_ID", "STATUS", "CREATED_TIMESTAMP", "UPDATED_TIMESTAMP", "FROM_ADDRESS", "SUBJECT") values (?, ?, ?, ?, ?, ?)
  at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  at com.querydsl.sql.Configuration.translate(Configuration.java:459) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:408) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:201) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:182) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.create(QueryDslHelper.java:79) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemStore.lambda$createMailItem$5(MailItemStore.java:222) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87) [?:?]
  at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43) [?:?]
  at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:131) [classes/:?]
  at sun.reflect.GeneratedMethodAccessor484.invoke(Unknown Source) [?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_265]
  at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_265]
  at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) [atlassian-plugins-core-5.2.3-96cf760eb.jar:?]
  at com.sun.proxy.$Proxy339.runInManagedTransaction(Unknown Source) [?:?]
  at sun.reflect.GeneratedMethodAccessor484.invoke(Unknown Source) [?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_265]
  at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_265]
  at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:131) [atlassian-plugins-osgi-bridge-5.2.3-96cf760eb.jar:?]
  at com.sun.proxy.$Proxy339.runInManagedTransaction(Unknown Source) [?:?]
  at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.runInStartedOrExistingTransaction(JiraHostConnectionAccessor.java:130) [?:?]
  at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:60) [?:?]
  at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:40) [?:?]
  at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:67) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:43) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.pocketknife.api.querydsl.DatabaseAccessor.runInTransaction(DatabaseAccessor.java:134) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.runInTransaction(QueryDslHelper.java:53) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemStore.createMailItem(MailItemStore.java:221) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemManager.createMailItemFromMessage(MailItemManager.java:36) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.persistMailMessage(MailPullerWorker.java:208) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.pullEmailForConnection(MailPullerWorker.java:112) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.pullMailFromAllValidChannels(MailPullerWorker.java:75) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerService.run(MailPullerService.java:33) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.services.MailPullerExecutor.run(MailPullerExecutor.java:29) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.services.AbstractMailExecutor.execute(AbstractMailExecutor.java:45) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.jira.internal.mail.processor.services.MailJobRunner.runJob(MailJobRunner.java:35) [jira-email-processor-plugin-4.5.6-REL-0008.jar:?]
  at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134) [atlassian-scheduler-core-3.0.0.jar:?]
  at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106) [atlassian-scheduler-core-3.0.0.jar:?]
  at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90) [atlassian-scheduler-core-3.0.0.jar:?]
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:430) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:454) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:382) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35) [atlassian-scheduler-caesium-3.0.2.jar:?]
  at java.lang.Thread.run(Thread.java:748) [?:1.8.0_265]
  Caused by: java.sql.SQLException: ORA-12899: value too large for column "JIRA"."AO_2C4E5C_MAILITEM"."SUBJECT" (actual: 274, maximum: 255)
  

Explanation

Executing the query below should return the default value for the column SUBJECT: VARCHAR(255)

select data_type, data_length 
from user_tab_columns
where table_name = 'AO_2C4E5C_MAILITEM';

When pulling the email, the subject field that carries more than 255 bytes will cause the SQL injection to return error therefore hitting into a halt.

Workaround

You can remove the email with the subject exceeding the size from the Inbox and the mail handler should resume the normal processing again.