Project

General

Profile

Actions

Defect #21398

closed

Mysql: 500 server error when submitting 4 bytes utf8 (to be saved in the 'notes' field)

Added by Deoren Moor over 8 years ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Duplicate
Affected version:

Description

Hi,

We're running multiple Redmine installations, many of them several years old and all setup as described on the RedmineInstall wiki page:

CREATE DATABASE redmine CHARACTER SET utf8;
CREATE USER 'redmine'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost';

As far as I know today is the first case where we ran into a problem submitting text content where one of us wasn't attempting to submit more content than a field would hold.

The error messages:

Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x81' for column 'notes' at row 1: INSERT INTO `journals` (`journalized_id`, `journalized_type`, `user_id`, `notes`, `created_on`) VALUES
 (35747, 'Issue', 3, '¦~_~X~A', '2015-12-01 16:14:16')
ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x81' for column 'notes' at row 1: INSERT INTO `journals` (`journalized_id`, `journalized_type`, `user_id
`, `notes`, `created_on`) VALUES (35747, 'Issue', 3, '¦~_~X~A', '2015-12-01 16:14:16')):

Details from bin/about:

Environment:
  Redmine version                3.1.2.stable.14882
  Ruby version                   1.9.3-p0 (2011-10-30) [i686-linux]
  Rails version                  4.2.4
  Environment                    production
  Database adapter               Mysql2
SCM:
  Subversion                     1.6.17
  Git                            1.7.9.5
  Filesystem
Redmine plugins:
  no plugin installed

After turning to Google it appears that the character is an emoticon described as, "Grinning Face With Smiling Eyes"and has the UTF-8 hex code of F0 9F 98 81. The character displays properly within the text field and when choosing to preview the text/formatting, but not when attempting to save to the database.

Looking at other tickets here I see several others similar to this one:

  • #20636 (not enough info to be sure it's the same)
  • #20143 (mail handler related)
  • #10772#note-7 (workaround was to convert to utf8mb4)

and the general response appears to be to use utf8mb4, but as noted on #10772#note-7 it appears that the results of that conversion resulted in data loss. This strikes me as odd since the MySQL 5.5 reference manual1 utf8mb4 is a superset of utf8:

For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

  • Is it safe to upgrade the character set for the database and tables as described on #10772#note-7?
    • i.e., will future upgrades be problematic due to a conversion of utf8 to utf8mb4 set?
  • Should Redmine be expected to filter out invalid characters that do not match the character set of the database storing the data?
  • Should utf8mb4 be used at the outset?
    • I ask because that's not noted in the guide (that I can find).

Thanks for your time.

1 http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html


Files


Related issues

Related to Redmine - Patch #19742: RedmineInstall: MySQL: collation_databaseClosed

Actions
Related to Redmine - Defect #24116: Tickets are not created if the email contains some of the special characterClosed

Actions
Related to Redmine - Feature #31921: Changes to properly support 4 byte characters (emoji) when database is MySQLClosedGo MAEDA

Actions
Actions

Also available in: Atom PDF