Defect #10772
4-byte utf-8 characters
Status: | Closed | Start date: | ||
---|---|---|---|---|
Priority: | Normal | Due date: | ||
Assignee: | - | % Done: | 0% | |
Category: | Database | |||
Target version: | - | |||
Resolution: | Duplicate | Affected version: |
Description
My redmine version is 1.2.0
When I update the first name of my account, the internal error appear.
The error description below:
ActiveRecord::StatementInvalid in MyController#account
Mysql::Error: Incorrect string value: '\xF0\xA0\xAE\x9F' for column 'firstname' at row 1: UPDATE `users` SET `firstname` = '李飞
Related issues
History
#1
Updated by Etienne Massip almost 11 years ago
- Category set to Accounts / authentication
Could you please give more details according to SubmittingBugs?
#3
Updated by Etienne Massip almost 11 years ago
- Status changed from New to Closed
- Assignee deleted (
Etienne Massip) - Resolution set to Invalid
#4
Updated by Etienne Massip almost 11 years ago
- Status changed from Closed to New
- Resolution deleted (
Invalid)
Sorry, there was a bug here, your post has been truncated.
Here is the content of the mail:
The character '<problematic character>' make redmine Internal Error
My redmine version is 1.2.0
When I update the first name of my account, the internal error appear.
The error description below:
ActiveRecord::StatementInvalid in MyController#account
Mysql::Error: Incorrect string value: '\xF0\xA0\xAE\x9F' for column 'firstname' at row 1: UPDATE `users` SET `firstname` = '李飞<problematic character>', `updated_on` = '2012-04-26 10:48:31' WHERE `id` = 1
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:227:in `log'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:324:in `execute'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:265:in `update_sql'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:339:in `update_sql'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:49:in `update_without_query_dirty'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:19:in `update'
C:/redmine/vendor/rails/activerecord/lib/active_record/base.rb:2936:in `update_without_lock'
C:/redmine/vendor/rails/activerecord/lib/active_record/locking/optimistic.rb:81:in `update_without_dirty'
C:/redmine/vendor/rails/activerecord/lib/active_record/dirty.rb:146:in `update_without_timestamps'
C:/redmine/vendor/rails/activerecord/lib/active_record/timestamp.rb:64:in `update_without_callbacks'
C:/redmine/vendor/rails/activerecord/lib/active_record/callbacks.rb:282:in `update'
C:/redmine/vendor/rails/activerecord/lib/active_record/base.rb:2927:in `create_or_update_without_callbacks'
C:/redmine/vendor/rails/activerecord/lib/active_record/callbacks.rb:250:in `create_or_update'
C:/redmine/vendor/rails/activerecord/lib/active_record/base.rb:2577:in `save_without_validation'
C:/redmine/vendor/rails/activerecord/lib/active_record/validations.rb:1090:in `save_without_dirty'
C:/redmine/vendor/rails/activerecord/lib/active_record/dirty.rb:79:in `save_without_transactions'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `send'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `with_transaction_returning_status'
C:/redmine/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:182:in `transaction'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:228:in `with_transaction_returning_status'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:208:in `rollback_active_record_state!'
C:/redmine/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'
C:/redmine/app/controllers/my_controller.rb:60:in `account'
#5
Updated by Etienne Massip almost 11 years ago
- Subject changed from The character ' to The character '
Could you try with the mysql2 database adapter?
#6
Updated by Etienne Massip almost 11 years ago
-
#7
Updated by Not Relevant over 9 years ago
I encountered the same problem and wanted to let you know my findings.
My test environment:
Environment: Redmine version 2.3.1.stable Ruby version 1.9.3 (i686-linux) Rails version 3.2.14 Environment production Database adapter Mysql2
The underlying problem is the mysql database. Per default mysql databases and tables are utf8. But only up to 3 caracters:
Here is what the mysql manual states:
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters:
To solve this issue you need to convert your database and tables to utf8mb4
ALTER DATABASE redmine_default CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; ALTER TABLE attachments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE auth_sources CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE boards CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE changes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE changeset_parents CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE changesets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE changesets_issues CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE custom_fields CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE custom_fields_projects CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE custom_fields_trackers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; . . .
And change the database.yml accordingly
adapter: mysql2 database: redmine_default host: localhost port: username: redmine_default password: secret encoding: utf8mb4
Worst part about this bug is: Everything after chars 4 bytes long will be truncated without warning. That's the reason for your "Sorry, there was a bug here, your post has been truncated."
Some sanity check should be done. Not sure if that's redmine or upstream.
And no, this has nothing to do with Account or i18n, it's a general problem.
#8
Updated by Toshi MARUYAMA over 9 years ago
- Category changed from Accounts / authentication to Database
#9
Updated by Toshi MARUYAMA over 7 years ago
- Subject changed from The character ' to 4-byte utf-8 characters
#10
Updated by Toshi MARUYAMA over 7 years ago
- Related to Patch #19742: RedmineInstall: MySQL: collation_database added
#11
Updated by Toshi MARUYAMA over 7 years ago
- Duplicated by Defect #18866: MySQL: disappear after 4-Byte UTF-8 added
#12
Updated by Toshi MARUYAMA over 7 years ago
- Related to deleted (Patch #19742: RedmineInstall: MySQL: collation_database)
#13
Updated by Toshi MARUYAMA over 7 years ago
- Status changed from New to Closed
- Resolution set to Duplicate
#14
Updated by Toshi MARUYAMA over 7 years ago
- Duplicated by deleted (Defect #18866: MySQL: disappear after 4-Byte UTF-8)
#15
Updated by Toshi MARUYAMA over 7 years ago
- Duplicates Defect #18866: MySQL: disappear after 4-Byte UTF-8 added
#16
Updated by Toshi MARUYAMA over 7 years ago
- Duplicates Defect #20143: Mailhandler cannot handle 4-byte characters added
#17
Updated by Jēkabs Jānis Kalniņš about 5 years ago
Not Relevant wrote:
I encountered the same problem and wanted to let you know my findings.
My test environment:
[...]The underlying problem is the mysql database. Per default mysql databases and tables are utf8. But only up to 3 caracters:
Here is what the mysql manual states:
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters:
To solve this issue you need to convert your database and tables to utf8mb4
[...]
And change the database.yml accordingly
[...]
Worst part about this bug is: Everything after chars 4 bytes long will be truncated without warning. That's the reason for your "Sorry, there was a bug here, your post has been truncated."
Some sanity check should be done. Not sure if that's redmine or upstream.
And no, this has nothing to do with Account or i18n, it's a general problem.
Thanks this worked for me #27803
ALTER TABLE issues CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ALTER TABLE journals CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ALTER TABLE wiki_contents CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
In config/database.yml
encoding: utf8
to
encoding: utf8mb4
#18
Updated by Marius BALTEANU over 3 years ago
- Related to Feature #31921: Changes to properly support 4 byte characters (emoji) when database is MySQL added
#19
Updated by Marius BALTEANU about 2 years ago
- Related to Defect #34235: Losing data due to unsupported characters added
#20
Updated by Marius BALTEANU about 2 years ago
- Related to deleted (Defect #34235: Losing data due to unsupported characters)
#21
Updated by Marius BALTEANU about 2 years ago
- Duplicated by Defect #34235: Losing data due to unsupported characters added