SQL Exception while receiving service desk ticket.

[svaningelgem]

I just got an email from a customer which triggers an SQL Exception:

(SQL Exception while executing the following:INSERT INTO jiraissue (ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xF0\x9F\x98\x8A \x0A...' for column 'DESCRIPTION' at row 1))
 at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:206)
 at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:171)
 at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:89)
 at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:562)
 at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:548)
 at org.ofbiz.core.entity.GenericValue.create(GenericValue.java:114)
 at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:296)
 ... 142 more
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:INSERT INTO jiraissue (ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xF0\x9F\x98\x8A \x0A...' for column 'DESCRIPTION' at row 1)
 at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
 at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:198)
 ... 148 more
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A \x0A...' for column 'DESCRIPTION' at row 1
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
 at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
 at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
 at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
 at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:562)
 ... 149 more

I already checked the encoding of the database (as per https://confluence.atlassian.com/jirakb/sql-exception-while-updating-issues-or-importing-data-in-jira-applications-with-mysql-due-to-encoding-134480131.html) and that looks all fine (utf8_bin).

What can I additionally do to fix this, or is this something in the JIRA core that needs to be updated? 

EDIT: This is likely the piece of data that is violating the UTF8-standards:

--0000000000004b89c9057fa68092
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


Thank you and have a nice day =F0=9F=98=8A

[Andy Heinzer]

Hi Steven,

Given your error message I can tell that this is a problem that is specific to using a MySQL database with Jira.  Specifically mysql databases can't store 4 byte characters, such as emojis.  This limitation is noted in our documentation, please see Connecting Jira applications to Mysql: Known issues.  Given the text in the error message, it looks like this is the case here too.

Please see the KB JIRA does not work with Emoji / 4 byte characters  and the bug ticket https://jira.atlassian.com/browse/JRASERVER-36135

There are lots of different possible solutions here, from using a different database type like postgres with Jira OR to using the utf8mb4 encoding for the MySQL database (which can then allow that database type to store those characters).  Please note that in order the use the utf8mb4 encoding, you will need to use a version of MySQL 5.7 or higher. 

Also Jira doesn't officially yet have a released version that officially supports that specific encoding (which is why the official documentation does not mention this yet), but this is something that is planned to be in Jira 8 and higher as of right now according to that bug ticket I linked.  That said, you might be able to make this utf8mb4 encoding work in the current Jira Server versions.  Just to reiterate, at the moment this would be a work-around that isn't officially supported by Atlassian just yet.