Redmine crashes on "odd" characters in text fields
Added by Joaquim Homrighausen over 6 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) over 6 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 Joaquim Homrighausen over 6 years ago
utf8 is returned on both queries.
RE: Redmine crashes on "odd" characters in text fields
-
Added by Martin Denizet (redmine.org team member) over 6 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 over 6 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) over 6 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 over 6 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) over 6 years ago
Related issue #21398.
RE: Redmine crashes on "odd" characters in text fields
-
Added by Martin Denizet (redmine.org team member) over 6 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 over 6 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.