CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5rails-activerecordmysql2
Kris
Kris

Reputation: 19938

How to create migrations with specific COLLATE?

I recently upgraded Ubuntu on my dev machine this included a newer version of MySQL, version 8.

As a result I had to bump the mysql2 gem due to a segfault when running migrations. It was previously pinned to < 0.5 due to ActiveRecord, at the time, not being compatible with newer versions of the mysql2 gem.

The problem is that when I run migrations the schema.rb contains create_table's with a COLLATE of utf8mb4_0900_ai_ci. This is fine locally on MySQL 8 but is not supported on our CI or production servers since they run an older version of MySQL:

 Mysql2::Error: Unknown collation: 'utf8mb4_0900_ai_ci'

Before upgrading to MySQL 8 the COLLATE was not present, just CHARSET:

-  create_table "tasks", id: :integer, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
+  create_table "tasks", id: :integer, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", force: :cascade do |t|

In my database.yml I specific the encoding as utf8mb4, is there a way to specify the COLLATE too?

Upvotes: 1

Views: 2130

Answers (2)

Abhinav Manchanda
Abhinav Manchanda

Reputation: 6641

You can update the collation:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

Upvotes: 0

Kris
Kris

Reputation: 19938

You can specify COLLATE in database.yml:

adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

You can find your existing database collation as follows:

use my_app_production;
SELECT @@character_set_database, @@collation_database;

Or a specific table:

SHOW TABLE STATUS LIKE 'users';

Incidentally when specifying the collation there is no need to bump the mysql2 gem either as the segfault goes away.

Upvotes: 3

Related Questions