Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-21224

MySQL databases incapabable of handling 4-byte UTF-8 Characters. JIRA should handle this gracefully

    XMLWordPrintable

    Details

    • Feedback Policy:
      We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Description

      When adding a comment that contains Unicode characters outside the BMP (e.g.- ⚔✏Ṣᴔᖲ⋂𒟤ၽ긄ְ𳨒𝴠㧁㸏㱠嫖㰾) JIRA throws the following error (Oops - an error has occurred):

      ...
      Cause:
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Action][id,10030][body,?????????????????][author,admin][level,null][updated,2010-05-04 15:04:55.046][created,2010-05-04 15:04:55.046][issue,10011][rolelevel,null][type,comment][updateauthor,admin] (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\x92\x9F\xA4\xE1\x81...' for column 'actionbody' at row 1)) 
      ...
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Action][id,10031][body,?????????????????][author,admin][level,null][updated,2010-05-04 15:07:43.156][created,2010-05-04 15:07:43.156][issue,10011][rolelevel,null][type,comment][updateauthor,admin] (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\x92\x9F\xA4\xE1\x81...' for column 'actionbody' at row 1))
      	at com.atlassian.jira.issue.comments.DefaultCommentManager.create(DefaultCommentManager.java:202)
      	at com.atlassian.jira.issue.comments.DefaultCommentManager.create(DefaultCommentManager.java:170)
      	at com.atlassian.jira.issue.comments.DefaultCommentManager.create(DefaultCommentManager.java:164)
      	at com.atlassian.jira.bc.issue.comment.DefaultCommentService.create(DefaultCommentService.java:156)
      ...
      

      It is IMPORTANT to note that this error may also crop up when the MySQL server and/or JIRA connection to it are mis-configured. In this case JIRA may also fail for characters in the Unicode BMP (see https://confluence.atlassian.com/display/JIRAKB/SQL+Exception+when+Entering%2C+Updating+or+Importing+an+Issue+in+JIRA+with+MySQL+Due+to+Encoding). In this case this issue does not apply and its just a configuration problem.

      This problem only happens in MySQL environment. MySQL has a known limitation in that it is unable to store 4 byte UTF-8 characters in MySQL before 5.5.3. I think we should:

      1. Add a startup warning admins when JIRA connects to a MySQL database with these problems.
      2. Update the documentation of MySQL database configuration to indicate this problem.
      3. Sanatising the input so that this error does not occur when connected to MySQL before 5.5.3. This will probably be much harder than it sounds because JIRA can get input data from anywhere (e.g. CSV, E-Mail, HTTP, SOAP etc). We will probably have to choose our battles.
      4. Update out documentation to indicate this is the database that gets around this problem.
      5. Update the upgrade guide once we work out an upgrade path.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              jcooi JoachimA
              Votes:
              4 Vote for this issue
              Watchers:
              25 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: