Help migrating a MySQL database from utf8_general_ci to utf8mb4_bin

dthiery · October 15, 2018, 1:40am

My MySQL database is utf8_general_ci and I need help migrating it to utf8mb4_bin.

flaburgan · October 15, 2018, 10:44am

I’m just linking the github issue in case someone needs the history of the investigation.

csammy · October 15, 2018, 1:42pm

Thank you for creating this topic. Just one last thing before we dive into the mess: I need you to verify that your indices already have a length restriction. A sample will suffice for now. In order to check this, please open your database and run

SHOW INDEX FROM diaspora_production.tags;

In the Sub_part field, there should be the number 191 for the index index_tags_on_name. Please report if that is the case.

dthiery · October 15, 2018, 2:13pm

Yes, it has 191 in the Sub_part field for index_tags_on_name.

csammy · October 15, 2018, 2:28pm

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.

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

Stop the pod you are doing this on
In the diaspora folder, execute bin/rails console
In the opening console, execute require_relative 'foo.rb'
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 ).

dthiery · October 15, 2018, 3:01pm

Ok, quick question. I made a backup copy of my database to a new one (diaspora_test). Do I need to create a backup of my entire diaspora installation to do this? If so, is it as simple as just copying the entire installation directory of my diaspora to a new directory?

flaburgan · October 15, 2018, 3:32pm

Sammy was suggesting that you test it locally. Looks like the perfect timing to introduce you to the docker image for test env if you don’t want to set up a diaspora installation locally.

dthiery · October 15, 2018, 3:37pm

Ahh…gotcha!

Also, I noticed that the fields that need changed are varchar(255) and not text. Should I just change the text to varchar(255)? Or do those fields need to be converted to text?

dthiery · October 15, 2018, 3:41pm

Never mind, I just realized this is converting them to text.

denschub · October 15, 2018, 3:58pm

Please do not do anything manual with the database. There already has been enough destruction. If it turns out the script missed something, that’s relatively easy to reproduce and fix, but that’s not true for manual actions.

You can actually use your existing diaspora installation:

Edit config/database.yml and make sure the database name in in the test section matches the name of your clone database. If the database is named diaspora_test, you should be all set already, but please double-check.
Start your Rails console with RAILS_ENV=test bin/rails console
Run ActiveRecord::Base.connection.current_database and make sure it returns the name of your testing database.
Continue with step 4 in @csammy’s reply.

Also make sure to run the commands in a tmux or something, just in case your SSH connection drops.

dthiery · October 15, 2018, 4:57pm

The only manual thing I did was the backup and restore to diaspora_ test

Ok, knowing I can use my existing installation makes this easier, else I’d probably have to wait until this weekend to get a local version setup.

Do I still need to stop the pod before doing this since it’ll be running against diaspora_test?

denschub · October 15, 2018, 5:02pm

No. As long as you verify that you are, indeed, using the test database, running it while the pod is running in production is fine. However, as your database will be doing a lot of disk I/O, there might be a performance hit. If you want to avoid that, the only option you have is to set diaspora up locally and import your database dump in your local machine.

dthiery · October 15, 2018, 5:10pm

When I run RAILS_ENV=test bin/rails console I get an error that says Namespace test not found in my diaspora.yml.

denschub · October 15, 2018, 5:11pm

That’s a warning. And as such, can be ignored.

dthiery · October 15, 2018, 5:13pm

But then it exits and leaves me at the terminal prompt. I tried that command with production to see what happened and it’s fine (exited that right away).

denschub · October 15, 2018, 5:16pm

If it exists, you’re probably getting an additional error you didn’t tell me about. So please paste the entire output.

dthiery · October 15, 2018, 5:21pm

uninitialized constant Rack::SSL (NameError)

Didn’t include that the first time because I thought there was a problem with diaspora.yml that caused that error

csammy · October 15, 2018, 5:33pm

Would it be possible for you to hop on to IRC? It would speed up this back and forth greatly

dthiery · October 15, 2018, 5:36pm

Yep, give me a second and I’ll get on there.

csammy · October 18, 2018, 9:08am

Fair Warning: This is just a summary to capture the situation and the steps we went through. Please don’t use this without getting back to us here on Discourse or on IRC, as these steps can destroy your database if anything we did not capture here is different.

Initial Situation

Symptoms:
- Impossible to post a hashtag that differs only by diacritic from an existing hashtag (in this case: dandelíon exists, dandelion is throwing errors)
  - Error Message to User - <tag> is already taken (Example Screenshot)
  - Error Message in Log - Rails: ActsAsTaggableOn::DuplicateTagError (‘<tag>’ has already been taken)
- Impossible to use unicode emoticons in posts
  - Error Message to User - Internal server error. Our bad! Sorry about that. :( Example Screenshot
Installed using a BitNami installer (for now an advised-against way to install)
Database in MySQL has character set utf8 and collation utf8_general_ci due to not being created by bin/rake db:create
All indices (except for AR tables) are already restricted in length due to the pod in question being fairly new
Change to character set utf8mb4 and collation utf8mb4_bin is required for proper functionality

Our solution for this special case

Copy following code to foo.rb in your diaspora* root 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

Copy your database to a new one named diaspora_test (or whatever is in your config/database.yml in the test section, if you changed that)
Add the following lines to the bottom of your config/diaspora.yml file:

test:
  environment:
    require_ssl: false

Start your Rails console with RAILS_ENV=test bin/rails console
Run ActiveRecord::Base.connection.current_database and make sure it returns the name of your testing database.
Run require_relative 'foo.rb'
Run SetMysqlToUnicodeMb4.new.up
This may put some load on your server as it is rewriting the entire database copy. It also gives you a rough estimate how long changing the production database will take.
If the database looks okay, close the console with quit
Stop your pod
Make a backup of your database
Start your Rails console with RAILS_ENV=production bin/rails console
Run require_relative 'foo.rb'
Run SetMysqlToUnicodeMb4.new.up
This may take about as much time as your test run.
If the database looks okay, close the console with quit
Start your pod again
Test the functionality of your pod (try a post with an emoticon or a hashtag that did not work previously)