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

Summary

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

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

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

  AND

  1 2 mysql> use <your_jiradb>; mysql> status;

• Result will show below:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 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

Solution

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

• Connection Character Sets and Collations.

If update is not possible, do the following steps:

⚠️ Please perform the following in a staging environment first. Ensure that the database have been backed up. ⚠️ *

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:

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.