Help migrating a MySQL database from utf8_general_ci to utf8mb4_bin

Support Podmin Support
5

Very good. Please test the following steps with a copy of your production setup so you know it won’t destroy your pod, as this has not been extensively tested. I only did a proof-of-concept run on an empty installation on my local machine.

  1. Copy this content to a file foo.rb in your diaspora folder:
class SetMysqlToUnicodeMb4 < ActiveRecord::Migration[5.1]
  def self.up
    change_encoding('utf8mb4', 'utf8mb4_bin') if AppConfig.mysql?
  end
  
  def change_encoding(encoding, collation)
    execute "ALTER DATABASE `#{ActiveRecord::Base.connection.current_database}` CHARACTER SET #{encoding} COLLATE #{collation};"

    tables.each do |table|
      next if ["ar_internal_metadata", "schema_migrations"].include? table

      modify_text_columns = columns(table).select {|column| column.type == :text }.map {|column|
        "MODIFY `#{column.name}` TEXT #{'NOT' unless column.null } NULL#{" DEFAULT '#{column.default}'" if column.has_default?}"
      }.join(", ")

      execute "ALTER TABLE `#{table}` CONVERT TO CHARACTER SET #{encoding} COLLATE #{collation}#{", #{modify_text_columns}" unless modify_text_columns.empty?};"
    end
  end
end
  1. Stop the pod you are doing this on
  2. In the diaspora folder, execute bin/rails console
  3. In the opening console, execute require_relative 'foo.rb'
  4. Afterwards, do SetMysqlToUnicodeMb4.new.up

The last step may take some time, as the tables need to be rewritten. MySQL actually copies the entire table while changing the respective fields. Please, again, do not forget to test this beforehand in a copy of your production setup. Doing so will give you two benefits. First, you can rule out any data-destroying bugs. Second, you will have a rough estimate of the expected downtime.

If you feel like it, you’re welcome to join on our IRC channel to have more real-time support (as long as somebody is there :wink:).

1 Like