DataAccessException when trying to save changes to Service Desk ticket

[Esther Strom]

We have our service desk set up to accept emails, and recently received one in Spanish. The ticket was created with no problem, but now whenever anyone tries to edit it in any way, they get the following error:

Exception occurred: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ChangeItem][newvalue,null][field,description][oldstring,Ver mail en el navegador DESCUBRIENDO EL MUNDO DE LAS CARNES NUEVOS CURSOS PARA PROFESIONALES Nos ponemos en contacto con ustedes para informarles de la formación más próxima que tenemos disponible en la Escuela Superior de Hostelería esperando sea de su interés: DESCUBRIENDO EL MUNDO DE LAS CARNES 21, 22 y 23 de mayo. 16:30 - 20:30  Ver curso Se llevarán a cabo en las instalaciones de: ESCUELA SUPERIOR DE HOSTELERÃ�A Y TURISMO, Paseo Puerta del Ã�ngel, 5, 28011 Madrid Estos cursos pueden ser bonificados a través de los Seguros Sociales. Solicite más información: 911 92 17 18 

I've tried removing all of the special characters from the text (summary, description, and any comments), but it still will not allow me to save, and the error still shows the special characters. I've even tried completely clearing out the description and replacing it with one sentence in English, and the error still shows the Spanish text.

I'm the system admin, and we do have ScriptRunner, so if there's any way around this that way, I'm game.

ETA that this was the error message shown on the screen to the user. When I look at our logs, the error is huge (way too bit to print here), but the relevant bit seems to be this:

Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE jiraissue SET pkey=?, issuenum=?, PROJECT=?, REPORTER=?, ASSIGNEE=?, CREATOR=?, issuetype=?, SUMMARY=?, DESCRIPTION=?, ENVIRONMENT=?, PRIORITY=?, RESOLUTION=?, issuestatus=?, CREATED=?, UPDATED=?, DUEDATE=?, RESOLUTIONDATE=?, VOTES=?, WATCHES=?, TIMEORIGINALESTIMATE=?, TIMEESTIMATE=?, TIMESPENT=?, WORKFLOW_ID=?, SECURITY=?, FIXFOR=?, COMPONENT=? WHERE ID=? (Incorrect string value: '\xEF\xBF\xBDA Y...' for column 'DESCRIPTION' at row 1)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:683)
at org.ofbiz.core.entity.GenericDAO.singleUpdate(GenericDAO.java:363)
... 255 more
Caused by: java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBDA Y...' for column 'DESCRIPTION' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)

[Andy Heinzer]

This error tells us that there is at least one character that has been input into Jira, that character cannot be stored to your SQL database.   There are a couple of possible causes with this.

1. The database collation or encoding is not correct for the database type.  Since you're using MySQL, I would want to make sure that your database was setup per the instructions in Connecting JIRA applications to MySQL.  The character set should be utf8 and the collate should be utf8_bin.  
2. There is another possible problem since you're using MySQL.  There is a documented limitation that MySQL is unable to store 4-byte characters.  See https://jira.atlassian.com/browse/JRASERVER-36135 for more details.   MySQL 5.5 and 5.6 do not really have a good workaround for this, but 5.7 might be able to help since it allows for the use of the utf8mb4 encoding.

Could you let us know what version of Jira and MySQL you are using?   I think this information will better help us advise you on the best course of action here.

[Esther Strom]

I get that it's a bad character; what I can't figure out is how to get rid of it so I can save. It's obviously stored already, since emptying the field entirely doesn't allow me to save.

We're on Jira 7.1.1 and MySQL 14.14  (distrib 5.5.54) for Linux.

[Andy Heinzer]

We usually see this problem with inbound email to Jira.  If a user uses something like an emoji, the email can stay stuck in the inbox because Jira can't complete the process to store this to the database.   It's sadly very common to see with mysql databases on the backend.  

I would recommend trying to go into the specific mailboxes that Jira checks for different projects and then manually delete that message from the inbox.   This in turn should then allow Jira to continue without trying to process the message.

The downside is that this content/update is lost. 

[Esther Strom]

I'm confused. The ticket did come in from an email (via service desk), but it's already in Jira. It's been processed and saved, so obviously mysql WAS able to store the odd characters initially. The problem comes when we try to edit the ticket.

[Nic Brough -Adaptavist-]

I've run into this a few times, it's not just a problem "writing" - it's read and write.  When the emoji arrives, MySQL is actually able to write some of them into the database.  When it reads them back it doesn't hand back the right thing to the code asking for it, and then when the attempt to write a change happens, it's trying to write back even more junk.

Sadly, the only way we found to fix it were to delete the offending records completely, or migrate to postgres.

[Esther Strom]

OK, that makes sense, @Nic Brough -Adaptavist-. Migrating to postgres is not an option, and mysql generally works just fine for us. I think we've run across maybe three instances like this in almost six years.

How would I go about deleting the record? Do you mean just deleting the ticket via the Delete action, or actually mucking around in the database? If there's a relatively safe way to find this particular content, I'm not opposed to editing the record manually.

[Andy Heinzer]

You could try this process Removing invalid characters from XML backups.   It would require you to create a new XML backup of your Jira data, extract those files, and then use that utility to clean the backup of any of these offending characters.   Once that is done, you could then

1. create a new database for Jira,
2. connect Jira to this database with the config tool or by editing the dbconfig.xml
3. restarting Jira
4. Then use the import existing data option within the setup wizard of Jira to import this cleaned backup zip file.

This way the entire record isn't actually getting deleted, just the offending character in that record.

[Esther Strom]

@Andy Heinzer - thanks for the instructions; I'll keep it in mind because something like that is good to know. But three records out of almost 100,000 are affected, so this seems a bit like swatting a fly with a Cadillac :) It's overkill, and riskier than our management would be willing to go for, given that we're only talking about .003% of our data.

I guess we'll just leave the ticket open.

[Nic Brough -Adaptavist-]

Either delete works.  I found one of them in a comment, so a delete from jiraaction fixed it.  In another case, where it had landed in the description, we were able to just delete the whole issue.