Problem Description

The JSM mail channel stops processing further.

Diagnostic

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

• when using a postgreSQL database:

  2024-10-22 13:17:08,567+0000 ERROR [] Caesium-1-4 ServiceRunner     Exception when MailPullerWorker pulls emails: 
  com.querydsl.core.QueryException: Caught PSQLException for insert into "public"."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.5.jar:?]
  	at com.querydsl.sql.Configuration.translate(Configuration.java:459) [querydsl-4.1.4-provider-plugin-1.5.jar:?]
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:408) [querydsl-4.1.4-provider-plugin-1.5.jar:?]
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:201) [querydsl-4.1.4-provider-plugin-1.5.jar:?]
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:182) [querydsl-4.1.4-provider-plugin-1.5.jar:?]
  	at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.create(QueryDslHelper.java:79) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemStore.saveMailItemToDB(MailItemStore.java:373) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemStore.lambda$createMailItem$5(MailItemStore.java:206) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:109) [?:?]
  	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:64) [?:?]
  	at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:134) [classes/:?]
  	at sun.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) [?:?]
  	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) [?:1.8.0_291]
  	at java.lang.reflect.Method.invoke(Unknown Source) [?:1.8.0_291]
  	at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) [atlassian-plugins-core-7.1.9.jar:?]
  	at com.sun.proxy.$Proxy101.runInManagedTransaction(Unknown Source) [?:?]
  	at sun.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) [?:?]
  	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) [?:1.8.0_291]
  	at java.lang.reflect.Method.invoke(Unknown Source) [?:1.8.0_291]
  	at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:130) [atlassian-plugins-osgi-bridge-7.1.8.jar:?]
  	at com.sun.proxy.$Proxy101.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:61) [?:?]
  	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:67) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:43) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.runInTransaction(QueryDslHelper.java:53) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemStore.createMailItem(MailItemStore.java:205) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.mailitem.MailItemManager.createMailItemFromMessage(MailItemManager.java:36) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.persistMailMessage(MailPullerWorker.java:343) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.persistMessagesToDB(MailPullerWorker.java:357) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.pullEmailForConnection(MailPullerWorker.java:185) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerWorker.pullMailFromAllValidChannels(MailPullerWorker.java:107) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.feature.puller.MailPullerService.run(MailPullerService.java:33) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.services.MailPullerExecutor.run(MailPullerExecutor.java:29) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.services.AbstractMailExecutor.execute(AbstractMailExecutor.java:45) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.jira.internal.mail.processor.services.MailJobRunner.runJob(MailJobRunner.java:35) [jira-email-processor-plugin-5.12.11-REL-0001.jar:?]
  	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134) [?:?]
  	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106) [?:?]
  	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90) [?:?]
  	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:500) [?:?]
  	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:495) [?:?]
  	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:519) [?:?]
  	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:415) [?:?]
  	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66) [?:?]
  	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60) [?:?]
  	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35) [?:?]
  	at java.lang.Thread.run(Unknown Source) [?:1.8.0_291]
  Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(255)

Explanation

Subject field that carries more than 255 characters will cause the SQL injection to return error therefore hitting into a halt. This can also occur if the sender field is over 255 characters. 

Workaround

1. We can manually increase the column size as shown below:

   ALTER TABLE "AO_2C4E5C_MAILITEM" ALTER "SUBJECT" TYPE varchar(500);

   Keep in mind that this column size will be modified to its original value as soon you restart Jira or execute an upgrade so don't forget to apply the workaround again

1. You can remove the message that is exceeding the size from the Inbox