Redmine crashes on "odd" characters in text fields

Added by Joaquim Homrighausen about 2 years ago

I don't know if there's a bug/issue for this already, but it seems like Redmine has problems when I copy/paste certain text content into a description or comment field (perhaps this applies to all editable text fields), I get this in the log:

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x89 k...' for column 'description' at row 1

In this particular case, it was a smiley I didn't notice until clicking "save". Fixing it is easy of course, I just need to remove the smiley. But perhaps Redmine shouldn't crash here.

Replies (9)

RE: Redmine crashes on "odd" characters in text fields - Added by Martin Denizet (redmine.org team member) about 2 years ago

Hello,
Could you please check the encoding used in your MySQL install please?
(assuming your DB name is redmine):

SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "redmine";

It could be interesting to get the encoding of the specific column:
SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "redmine" 
  AND table_name = "issues" 
  AND column_name = "description";

(Queries found on StackOverflow)

If you created your DB using official instructions, the queries results should be utf8 or utf8mb4.
Cheers,

RE: Redmine crashes on "odd" characters in text fields - Added by Martin Denizet (redmine.org team member) about 2 years ago

Could you please try:

show variables like 'char%';

Could you also include the relevant part of your database.yml(from the config directory)?
You may have to convert to utf8mb4, let's see...
Cheers,

RE: Redmine crashes on "odd" characters in text fields - Added by Joaquim Homrighausen about 2 years ago

| 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                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |

And database.yml:

production:                                                                                                                         
  adapter: mysql2                                                                                                                   
  database: redmine                                                                                                                 
  host: localhost                                                                                                                   
  username: TheVeryCoolRedmineUser
  password: AReallySneakyPasswordHere
  encoding: utf8                                                                                                                    

RE: Redmine crashes on "odd" characters in text fields - Added by Martin Denizet (redmine.org team member) about 2 years ago

I could test(in a test environment!) that converting the DB in utf8mb4 solves the issue.
That could well mess up your DB, backup first and preferably test on a dev server first!
Using the instructions found on https://railsmachine.com/articles/2017/05/19/converting-a-rails-database-to-utf8mb4.html (better read that before converting your production DB)
I created a file db/migrate/20180418115300_encoding_convertion.rb such as:

# Found on https://railsmachine.com/articles/2017/05/19/converting-a-rails-database-to-utf8mb4.html
# All credits to these fine folks
# This could mess up your DB, backup first!
class EncodingConvertion < ActiveRecord::Migration

  def db
    ActiveRecord::Base.connection
  end

  def up
    #return if Rails.env.staging? or Rails.env.production?

    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` ROW_FORMAT=DYNAMIC CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 

      db.columns(table).each do |column|
        case column.sql_type
          when /([a-z]*)text/i
            default = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'" 
            null = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` MODIFY `#{column.name}` #{column.sql_type.upcase} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};" 
          when /varchar\(([0-9]+)\)/i
            sql_type = column.sql_type.upcase
            default = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'" 
            null = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` MODIFY `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};" 
        end
      end
    end
  end
end

Then I did:

RAILS_ENV=production bundle exec rake db:migrate

And updated the database.yml:

production:
  adapter: mysql2
  database: redmine
  host: localhost
  username: SuperDuperAwesomeRedmineUser
  password: CantBeatThis
  encoding: utf8mb4

And restarted the instance.
If you do the migration, please post here your feedback :)
Cheers,

RE: Redmine crashes on "odd" characters in text fields - Added by Joaquim Homrighausen about 2 years ago

Thanks for your efforts. I may do this at some point, when I have more time to deal with a potential clusterfork. But out of curiosity, should RM not handle this somewhat more gracefully than to go tits-up?

RE: Redmine crashes on "odd" characters in text fields - Added by Martin Denizet (redmine.org team member) about 2 years ago

Joaquim Homrighausen wrote:

Thanks for your efforts. I may do this at some point, when I have more time to deal with a potential clusterfork. But out of curiosity, should RM not handle this somewhat more gracefully than to go tits-up?

I think they prefer not to make automatic conversion because of the potential issues. I guess it could be possible to filter out 4bytes characters to prevent errors 500 but that would not be great too.
It seems it's a bit too complex to have a solution that would fit all setups.
I did more testing, my dev instance seems totally fine after the conversion (MySQL 5.7.21), although it seems that with MySQL <5.7 you could expect that new tables created would not use utf8mb4. I tested table creation on my instance and it went fine.
Cheers,

RE: Redmine crashes on "odd" characters in text fields - Added by Joaquim Homrighausen about 2 years ago

Well, perhaps it could simply return to the form/application and display an error message like:

It seems we could not save your post. Please check for missing field or odd characters in your input and try again.

Rather than just dying :)

Anyway, thanks.

(1-9/9)