Redmine

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 ;-)

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).

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

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

#27361

#26386

OK, I've got a second solution ;-)

I've switched all my installations to postgresql 9.6 which solves all character set based problems, ...

Sorry for the comment, but this is in various ways the best solution.

After converting my tables to utf8mb4 and updating my database.yml accordingly, I could test that new tables created by Rails migrations were using utf8mb4, ROW_FORMAT=DYNAMIC and would not crash with emojis.
Using MySQL 5.7.21 and Redmine 3.4.5

Martin Denizet (redmine.org team member) wrote:

After converting my tables to utf8mb4 and upadting my database.yml <...>

Could you provide more details on this? Did your environment already had data? What script did you use? What updates carried out in database.yml?

Juozapis Juozapauskiksi wrote:

Martin Denizet (redmine.org team member) wrote:

After converting my tables to utf8mb4 and updating my database.yml <...>

Could you provide more details on this? Did your environment already had data? What script did you use? What updates carried out in database.yml?

1. I created a file db/migrate/20180418115300_encoding_convertion.rb (adapt the timestamp to work with your install):
   

   class EncodingConvertion < ActiveRecord::Migration
     #https://railsmachine.com/articles/2017/05/19/converting-a-rails-database-to-utf8mb4.html
     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
   
   

2. Database migration:
   

   RAILS_ENV=production bundle exec rake db:migrate
   

3. Then modified my database.yml:
   

   production:
     adapter: mysql2
     database: redmine
     host: localhost
     username: root
     password: "password" 
   -  encoding: utf8
   +  encoding: utf8mb4
   

4. And restarted the app

Credits: https://railsmachine.com/articles/2017/05/19/converting-a-rails-database-to-utf8mb4.html

This does not work for me on Redmine 4.0.1, any ideas?
Error: NameError: uninitialized constant Utf8mb4

Unfortunately the script does not work for me in Redmine 3.4.11:

/var/www/projekte/redmine# rake db:migrate RAILS_ENV=production
rake aborted!
SyntaxError: /var/www/projekte/redmine-3.4/db/migrate/20190731075300_encoding_convertion.rb:23: syntax error, unexpected tCONSTANT,
expecting keyword_end
...default.blank?) ? '' : "DEFAULT '#{column.default}'" 
...                               ^
/var/www/projekte/redmine-3.4/db/migrate/20190731075300_encoding_convertion.rb:25: syntax error, unexpected tCONSTANT, expecting keyword_end
            execute "ALTER TABLE `#{table}` MODIFY `#{c...
                          ^
/var/www/projekte/redmine-3.4/db/migrate/20190731075300_encoding_convertion.rb:25: syntax error, unexpected tCONSTANT, expecting keyword_end
... "ALTER TABLE `#{table}` MODIFY `#{column.name}` #{sql_type}...
...                               ^
/var/www/projekte/redmine-3.4/db/migrate/20190731075300_encoding_convertion.rb:30: syntax error, unexpected keyword_end, expecting end-of-input
/usr/local/rvm/gems/ruby-2.2.1/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
/usr/local/rvm/gems/ruby-2.2.1/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
/usr/local/rvm/gems/ruby-2.2.1/gems/activesupport-4.2.11.1/lib/active_support/dependencies.rb:274:in `block in require'
/usr/local/rvm/gems/ruby-2.2.1/gems/activesupport-4.2.11.1/lib/active_support/dependencies.rb:240:in `load_dependency'
/usr/local/rvm/gems/ruby-2.2.1/gems/activesupport-4.2.11.1/lib/active_support/dependencies.rb:274:in `require'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:777:in `load_migration'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:773:in `migration'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:768:in `disable_ddl_transaction'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:1076:in `use_transaction?'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:1068:in `ddl_transaction'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:1022:in `execute_migration_in_transaction'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:984:in `block in migrate'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:980:in `each'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:980:in `migrate'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:823:in `up'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/migration.rb:801:in `migrate'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/tasks/database_tasks.rb:139:in `migrate'
/usr/local/rvm/gems/ruby-2.2.1/gems/activerecord-4.2.11.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top
(required)>'
/usr/local/rvm/gems/ruby-2.2.1/gems/rake-12.3.2/exe/rake:27:in `<top (required)>'
/usr/local/rvm/gems/ruby-2.2.1/bin/ruby_executable_hooks:15:in `eval'
/usr/local/rvm/gems/ruby-2.2.1/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Did it work for someone else? What's the problem here?