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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Atlassian JIRA | Brian Cruz [Atlassian] | 1 year ago
  1. 0

    h3. 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. {noformat} 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 {noformat} The example above is caused by an email containing 4-byte UTF-8 and JIRA running on MySQL. See [JRA-21224 | https://jira.atlassian.com/browse/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` h3. Expected Behavior: JIRA Service Desk mail handler should mark this message as FAILED and continue to the next new message. h3. Workaround First, enable debug logging so that you can tell which message is causing problems. # Use [these instructions | https://confluence.atlassian.com/display/JIRAKB/Set+logging+level+for+a+package+in+JIRA] to add the {{com.atlassian.mail.incoming}} package and set it for DEBUG. # 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: {noformat} 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: ... {noformat} ** 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: * https://confluence.atlassian.com/pages/viewpage.action?pageId=728138770 Then you can proceed with deleting the offending message using the steps below: # Backup the database # Delete the message using the commands below: {noformat} delete from "AO_2C4E5C_MAILITEMCHUNK" where "MAIL_ITEM_ID" = <id from above>; delete from "AO_2C4E5C_MAILITEM" where "ID" = <id from above>; {noformat} NOTE: For Cloud customer, please inform support team to perform troubleshooting and workaround for this bug.

    Atlassian JIRA | 1 year ago | Brian Cruz [Atlassian]
    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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  2. 0

    h3. 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. {noformat} 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 {noformat} The example above is caused by an email containing 4-byte UTF-8 and JIRA running on MySQL. See [JRA-21224 | https://jira.atlassian.com/browse/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` h3. Expected Behavior: JIRA Service Desk mail handler should mark this message as FAILED and continue to the next new message. h3. Workaround First, enable debug logging so that you can tell which message is causing problems. # Use [these instructions | https://confluence.atlassian.com/display/JIRAKB/Set+logging+level+for+a+package+in+JIRA] to add the {{com.atlassian.mail.incoming}} package and set it for DEBUG. # 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: {noformat} 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: ... {noformat} ** 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: * https://confluence.atlassian.com/pages/viewpage.action?pageId=728138770 Then you can proceed with deleting the offending message using the steps below: # Backup the database # Delete the message using the commands below: {noformat} delete from "AO_2C4E5C_MAILITEMCHUNK" where "MAIL_ITEM_ID" = <id from above>; delete from "AO_2C4E5C_MAILITEM" where "ID" = <id from above>; {noformat} NOTE: For Cloud customer, please inform support team to perform troubleshooting and workaround for this bug.

    Atlassian JIRA | 1 year ago | Brian Cruz [Atlassian]
    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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  3. 0

    Unable to save data using hibernate in java

    Stack Overflow | 2 years ago | user3905559
    org.hibernate.exception.GenericJDBCException: could not execute statement
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    SQL Error: 1364 - Field 'status' doesn't have a default value

    GitHub | 2 years ago | jluc2808
    org.hibernate.exception.GenericJDBCException: could not execute statement
  6. 0

    How to insert garbage characters into mysql table via JDBC

    Stack Overflow | 4 years ago | Rickesh John
    java.sql.SQLException: Incorrect string value: '\xF1\xA3\x87\xB9I\xEF...' for column 'dataSet' at row 1
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. java.sql.SQLException

    Incorrect string value: '\xF0\x9F\x92\x9C\x0D\x0A...' for column 'MESSAGE' at row 1

    at com.mysql.jdbc.SQLError.createSQLException()
  2. MySQL jdbc
    PreparedStatement.executeUpdate
    1. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
    2. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
    3. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
    4. com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    5. com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    6. com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    7. com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    8. com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    9. com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
    10. com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
    10 frames
  3. Commons DBCP
    DelegatingPreparedStatement.executeUpdate
    1. org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    2. org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    2 frames
  4. Querydsl - SQL support
    SQLInsertClause.execute
    1. com.mysema.query.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:366)
    1 frame
  5. com.atlassian.jira
    QueryDslHelper$1.apply
    1. com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper$1.apply(QueryDslHelper.java:177)
    2. com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper$1.apply(QueryDslHelper.java:141)
    2 frames
  6. com.atlassian.pocketknife
    QueryFactoryImpl.insert
    1. com.atlassian.pocketknife.internal.querydsl.QueryFactoryImpl.insert(QueryFactoryImpl.java:161)
    1 frame
  7. com.atlassian.jira
    MailJobRunner.runJob
    1. com.atlassian.jira.internal.mail.processor.querydsl.QueryDslHelper.create(QueryDslHelper.java:140)
    2. com.atlassian.jira.internal.mail.processor.feature.audit.mailitem.MailItemAuditStore.createMailItemAudit(MailItemAuditStore.java:106)
    3. com.atlassian.jira.internal.mail.processor.feature.audit.mailitem.MailItemAuditManager.saveMailItemAudit(MailItemAuditManager.java:40)
    4. com.atlassian.jira.internal.mail.processor.feature.processor.MailHandlerWorker.saveAuditLog(MailHandlerWorker.java:143)
    5. com.atlassian.jira.internal.mail.processor.feature.processor.MailHandlerWorker.handle(MailHandlerWorker.java:95)
    6. com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorWorker.processMailItem(MailProcessorWorker.java:149)
    7. com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorWorker.process(MailProcessorWorker.java:83)
    8. com.atlassian.jira.internal.mail.processor.feature.processor.MailProcessorService.run(MailProcessorService.java:41)
    9. com.atlassian.jira.internal.mail.processor.services.MailProcessorExecutor.run(MailProcessorExecutor.java:34)
    10. com.atlassian.jira.internal.mail.processor.services.AbstractMailExecutor.execute(AbstractMailExecutor.java:48)
    11. com.atlassian.jira.internal.mail.processor.services.MailJobRunner.runJob(MailJobRunner.java:38)
    11 frames
  8. com.atlassian.scheduler
    Quartz1Job.execute
    1. com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:135)
    2. com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:101)
    3. com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:80)
    4. com.atlassian.scheduler.quartz1.Quartz1Job.execute(Quartz1Job.java:32)
    4 frames
  9. quartz
    SimpleThreadPool$WorkerThread.run
    1. org.quartz.core.JobRunShell.run(JobRunShell.java:223)
    2. org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
    2 frames