Defect #21398

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

Added by Deoren Moor about 2 years ago. Updated 9 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution: Affected version:3.1.2

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


Related issues

Related to Redmine - Patch #19742: RedmineInstall: MySQL: collation_database New
Related to Redmine - Defect #24116: Tickets are not created if the email contains some of the... Closed

History

#1 Updated by Peter Pfläging about 2 years ago

I've got the same problem. I've tested a conversion to UTF8MB4, which ist working.

I've posted a (german) article how I converted my instance to the correct behaving.

http://www.pflaeging.net/blog/archives/938

If someones interested I translate it to english ;-)

#2 Updated by Toshi MARUYAMA about 2 years ago

  • Related to Patch #19742: RedmineInstall: MySQL: collation_database added

#3 Updated by Jean-Marc Lagacé over 1 year ago

Peter Pfläging wrote:

If someones interested I translate it to english ;-)

I'm more than interested. Google Translate did most of the work but with this issue plaguing me right now (one of my dev is trying to store emojis in our database because we are tracking social media work).

#4 Updated by Toshi MARUYAMA over 1 year ago

  • Subject changed from 500 server error when submitting invalid string values (to be saved in the 'notes' field) to 500 server error when submitting 4 byte utf8 (to be saved in the 'notes' field)

#5 Updated by Toshi MARUYAMA over 1 year ago

  • Subject changed from 500 server error when submitting 4 byte utf8 (to be saved in the 'notes' field) to Mysql: 500 server error when submitting 4 byte utf8 (to be saved in the 'notes' field)

#6 Updated by Toshi MARUYAMA about 1 year ago

  • Related to Defect #24116: Tickets are not created if the email contains some of the special character added

#8 Updated by Silvio Geller 9 months ago

Hello,

I ran into the same Probleme as Peter Pfläging. I don't know why, but many people want to use emoji in their notes. So we also run into this 500 server error. As I tested a fresh installation of Redmine with utf8mb4, it fails. So it is not possible to do this without any changes. So as Peter Pfläging I have the same questions:

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?

Best regards,
Silvio Geller

#9 Updated by Toshi MARUYAMA 9 months ago

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

#10 Updated by Peter Pfläging 9 months ago

Ok, here a short solution in English for the problem:

Everything lies in two facts:
- MySQL has to handle all tables as UTF8MB4.
- INNODB large prefix has to be defined in MySQL (or MariaDB)

You must also be sure that every new table which is created by redline is also with these parameters!

Good! Now for the tasks during installation.

You have to be sure, that MySQL or MariaDB have the correct settings. So in /etc/ or /usr/local/etc search for my.cnf and put the following in:

[Mysqld]
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_large_prefix = 1

Create DB with mysql -u root -p:

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

Putz the following Ruby code in your redline installation /opt/redmine/config/initializers/enable_urf8mb4.rb with the following content:

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters

    class AbstractMysqlAdapter
      def create_table_with_innodb_row_format(table_name, options = {})
        table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')
        create_table_without_innodb_row_format(table_name, table_options) do |td|
          yield td if block_given?
        end
      end
      alias_method_chain :create_table, :innodb_row_format
    end

  end
end

That's everything apart from a standard installation of RedMine. It worked for me from 3.0 up to 3.3.2.

Here are my original two German articles to the problem:

http://www.pflaeging.net/blog/archives/1058
http://www.pflaeging.net/blog/archives/938

Greetings

:peter

Also available in: Atom PDF