Project

General

Profile

Bug #754

Redmine: unable to use some unicode unicode character in comment

Added by Pierre-Louis Bonicoli 9 months ago. Updated 9 months ago.

Status:
Resolved
Priority:
Normal
Category:
Service :: Web
Start date:
2022-03-12
Due date:
% Done:

100%

Estimated time:
Patch Available:
Confirmed:
Yes
Branch:
Entity:
DuckCorp
Security:
No
Help Needed:

Description

While commenting #748#note-7, I found out that some unicode characters raise an error. For example: U+1F4E3, U+1F389.

In the following error message, I replaced these characters with U+1F4E3/U+1F389 (in order to be able to create this issue :)

Started PUT "/journals/2382" for 86.245.117.73 at 2022-03-12 04:31:38 +0000
Processing by JournalsController#update as JS
  Parameters: {"utf8"=>"✓", "journal"=>{"notes"=>"[U+1F4E3] committed commit:dc43d75d1f7e7c01d943f085120f704d2dac831d [U+1F389]\r\n\r\nThanks Loïc ㊗️❤️!", "private_notes"=>"0"}, "commit"=>"Save", "id"=>"2382"}
  Current user: pilou (id=4)
Completed 500 Internal Server Error in 21ms (ActiveRecord: 8.9ms)

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x93\xA3 c...' for column `redmine_dc`.`journals`.`notes` at row 1: UPDATE `journals` SET `notes` = '[U+1F4E3] committed commit:dc43d75d1f7e7c01d943f085120f704d2dac831d [U+1F389]\r\n\r\nThanks Loïc ㊗️❤️!' WHERE `journals`.`id` = 2382):

app/models/journal.rb:81:in `save'
app/controllers/journals_controller.rb:90:in `update'
lib/redmine/sudo_mode.rb:63:in `sudo_mode'

History

#1

Updated by Pierre-Louis Bonicoli 9 months ago

According to http://blog.andriylesyuk.com/4-byte-utf-8-problem-in-redmine-and-how-to-fix-it/ / https://www.redmine.org/issues/31921, these steps are required:
  • on Toushirou, update /etc/redmine/{md,dc}/database.yml (within redmine lxc container)
  • convert the redmine tables of redmine_dc and redmine_md databases from utf8 to utf8mb4
#2

Updated by Pierre-Louis Bonicoli 9 months ago

  • Status changed from New to In Progress
  • Assignee set to Pierre-Louis Bonicoli
#3

Updated by Pierre-Louis Bonicoli 9 months ago

📣 I used the following script (based on this script) on both redmine_dc and redmine_md:

#!/bin/bash

set -o errexit -o pipefail

DB="$1" 
CHARSET="$2" 
COLL="$3" 

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4" 
[ -n "$COLL" ] || COLL="utf8mb4_unicode_520_ci" 

set -o nounset

echo $DB
echo "ALTER DATABASE \`$DB\` CHARACTER SET = $CHARSET COLLATE = $COLL;" | mysql

echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE \`$TABLE\` ROW_FORMAT=DYNAMIC, CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "CHECK TABLE \`$TABLE\` EXTENDED;" | mysql $DB
    done
)

Then I updated /etc/redmine/{dc,md}/database.yml within the redmine LXC controller hosted on Toushirou in order to use utf8mb4 instead utf8 and I restarted the apache2.service (within the LXC container) in order to take in account this update.

It works 🎉
🎈🎈🎈🎈🎈🎈🎈🎈

#4

Updated by Pierre-Louis Bonicoli 9 months ago

  • Status changed from In Progress to Resolved
  • % Done changed from 0 to 100

Also available in: Atom PDF