Problem Description

The SD incoming mail stops processing further.

Diagnostic

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

• when using a postgreSQL database:

  2019-06-27 13:32:10,133 ERROR [] Caesium-1-1 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.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:?]
  Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(255)
  	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135) [postgresql-9.4.1212.jar:9.4.1212]
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:368) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  	... 45 more
  

• when using a MSSQL database:

  2019-08-12 01:19:03,850 ERROR [] Caesium-1-4 ServiceRunner     Exception when MailPullerWorker pulls emails: 
  com.querydsl.core.QueryException: Caught SQLServerException for insert into "dbo"."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:?]
  
  ...
  
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
  	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2930) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:471) [mssql-jdbc-7.2.1.jre8.jar:?]
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:368) [querydsl-4.1.4-provider-plugin-1.0.jar:?]
  	... 45 more
  

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