🏠

Question

I tried to paste this character into a comment and I got an exception.

Technical details

Log's referral number: e841d9e6-0c08-4654-bccc-d2251dfeb5e8

Cause

Referer URL: https://jirapro.bose.com/browse/PAELLA-4498

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Action][updateauthor,jd71069][issue,148857][level,null][author,jd71069][created,2016-03-23 11:22:55.847][rolelevel,null][id,419063][type,comment][body,🏠][updated,2016-03-23 11:22:55.847] (SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xF0\x9F\x8F\xA0' for column 'actionbody' at row 1))

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Action][updateauthor,jd71069][issue,148857][level,null][author,jd71069][created,2016-03-23 11:22:55.847][rolelevel,null][id,419063][type,comment][body,🏠][updated,2016-03-23 11:22:55.847] (SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xF0\x9F\x8F\xA0' for column 'actionbody' at row 1))
	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:397)
	at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.createValue(WrappingOfBizDelegator.java:219)
	at com.atlassian.jira.entity.EntityUtils.createValue(EntityUtils.java:39)
	at com.atlassian.jira.issue.comments.DefaultCommentManager.create(DefaultCommentManager.java:254)
	at com.atlassian.jira.issue.comments.DefaultCommentManager.create(DefaultCommentManager.java:198)
	at com.atlassian.jira.bc.issue.comment.DefaultCommentService$3.apply(DefaultCommentService.java:358)
	at com.atlassian.jira.bc.issue.comment.DefaultCommentService$3.apply(DefaultCommentService.java:354)
	at com.atlassian.fugue.Option$Some.fold(Option.java:421)
	at com.atlassian.jira.bc.issue.comment.DefaultCommentService.create(DefaultCommentService.java:346)
	at com.atlassian.jira.issue.fields.CommentSystemField.createComment(CommentSystemField.java:581)
	at com.atlassian.jira.issue.fields.CommentSystemField.updateValue(CommentSystemField.java:482)
	at com.atlassian.jira.issue.managers.DefaultIssueManager.updateFieldValues(DefaultIssueManager.java:852)
	at com.atlassian.jira.issue.managers.DefaultIssueManager.updateIssue(DefaultIssueManager.java:814)
	at com.atlassian.jira.issue.managers.DefaultIssueManager.updateIssue(DefaultIssueManager.java:798)
	at com.atlassian.jira.web.action.issue.AssignIssue.doExecute(AssignIssue.java:138)
	at webwork.action.ActionSupport.execute(ActionSupport.java:165)
	at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:88)
	at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:39)
	at webwork.interceptor.NestedInterceptorChain.proceed(NestedInterceptorChain.java:31)
	at webwork.interceptor.ChainedInterceptor.intercept(ChainedInterceptor.java:16)
	at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:35)
	at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:225)
	at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:154)
	at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:87)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.atlassian.jira.web.filters.XContentTypeOptionsNoSniffFilter.doFilter(XContentTypeOptionsNoSniffFilter.java:22)
	at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
	at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:70)
	at com.atlassian.jira.tzdetect.IncludeResourcesFilter.doFilter(IncludeResourcesFilter.java:39)
	at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:78)
	at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
	at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:70)
	at com.atlassian.jira.baseurl.IncludeResourcesFilter.doFilter(IncludeResourcesFilter.java:38)
	at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
	at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:78)

crf · Answer

(Credit: http://stackoverflow.com/questions/13653712/java-sql-sqlexception-incorrect-string-value-xf0-x9f-x91-xbd-xf0-x9f)

crf · Answer

So... http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html

Apparently MySQL's UTF-8 isn't actually all of UTF-8.  It covers the Basic Multilingual Plane only and if you want the supplemental (sometimes called "Astral Plane") characters, you would have to use utf8mb4.

This is news to me, but there you have it.

mysql> create database scratch CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use scratch;
Database changed
mysql> create table asdf (value varchar(255));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into asdf values ('👽💔');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...' for column 'value' at row 1

Hrmmm...

mysql> create database scratch CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.02 sec)
mysql> set character_set_server=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_client=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> set character_set_results=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> use scratch;
Database changed
mysql> create table asdf (value varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into asdf values ('👽💔');
Query OK, 1 row affected (0.00 sec)

Since our documentation explicitly instructs you to use the "utf8" encoding, I'd say that this warrants opening a bug on http://jira.atlassian.com.  I went over there to do that for you and found somebody beat me to it: JRA-36135.  Unfortunately, I'm absolutely certain that we do not test using the "utf8mb4" encoding, and that JRA issue indicates that simply switching to it causes other problems, so I can't safely recommend that you use it, even though it otherwise appears to solve the problem.

Honestly, the best answer that I can give you is to switch to Postgres.  It is also free, generally performs better than MySQL, is better tested by Atlassian, and has fewer of these weird quirks in general.

Nic Brough -Adaptavist- · Answer

I've just run into something similar.  I am guessing you are using MySQL, and that your database collation settings are not set to utf8.  You can check by logging into the db and running: show variables where variable_name = 'character_set_database';