Unable to proceed with Issue submission due to UNICODE chars and SQL code in text fields or description or attachment file name

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Problem

JIRA does not allow user to continue "Start Progress" or "Close Issue".

  • After filling in details in the fields when creating issues, especially in description field.
  • Details have UNICODE chars and SQL code.

The following appears in the atlassian-jira.log

Caused by: org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:Issue]...
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE jiraissue...
Caused by: java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\xE2\x80\x99...' for column 'DESCRIPTION' at row 1

Diagnosis

Environment

  • Known issue for MySql Database. By default, MySql Server and Database Character set is latin

Diagnostic Steps

  • Perform below query to the MySql DB

    mysql> SHOW VARIABLES LIKE 'character\_set\_%'; 


    AND

    mysql> use <your_jiradb>;
    mysql> status; 
  • Result will show below: 

    mysql> SHOW VARIABLES LIKE 'character\_set\_%';
    +--------------------------+--------+
    | Variable_name | Value |
    +--------------------------+--------+
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    +--------------------------+--------+
    7 rows in set (0.01 sec)
    mysql> use jira;
    mysql> status;
    --------------
    mysql Ver 14.14 Distrib 5.5.42-37.1, for Linux (x86_64) using readline 5.1
    Connection id: 14518
    Current database: jira
    Current user: root@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Using delimiter: ;
    Server version: 5.5.42-37.1-log Percona Server (GPL), Release 37.1, Revision 39acee0
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Server characterset: latin1
    Db characterset: latin1
    Client characterset: utf8
    Conn. characterset: utf8
    UNIX socket: /var/lib/mysql/mysql.sock
    Uptime: 3 days 15 hours 14 min 57 sec
    --------------

Cause

Problem is caused by Server Characterset & DB Characterset is in latin1 collation. Atlassian recommends UTF-8 or Unicode encoding, and case-insensitive collation

Workaround

  • Remove the data that have been inserted using  UNICODE chars and SQL code.

  • Use {code} block for entering such data

Resolution

Change the collation to utf-8

If update is not possible, do the following steps:

(warning) Please perform the following in a staging environment first. Ensure that the database have been backed up. (warning) *

1. Create a new database and convert an empty database's connection characterset to utf-8 by referring to this article or below SQL command:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

2. Ensure all connection collation is in utf-8 using the show <table> status; SQL command. See Configuring Database Character Encoding.
3. Then, migrate all data from current database to new database.
4. Perform a re-test by entering any unicode and sql command in your description field.

Last modified on Nov 2, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.