Unable to proceed with Issue submission due to UNICODE chars and SQL code in text fields or description or attachment file name
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\_%';
ANDmysql> 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:
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:
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.