深藏若虛

解決 MySQL 使用 utf8mb4 編碼導致 Rails 資料庫遷移失敗的問題

MySQL utf8mb4 breaks ActiveRecord schema setup

當 MySQL 使用 utf8mb4 編碼,會導致 Rails 資料庫遷移失敗,並輸出此錯誤訊息的問題:

~/app $ RAILS_ENV=production rake db:migrate

== 20150226092347 DeviseCreateUsers: migrating================================
-- create_table(:users)
   -> 0.0070s
-- add_index(:users, :email, {:unique=>true})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX `index_users_on_email`  ON `users` (`email`) ~/.rvm/gems/ruby-2.2.2/gems/mysql2-0.4.2/lib/mysql2/client.rb:107:in `_query'
......

上面那段訊息的重點在於:

Mysql2::Error: Specified key was too long; max key length is 767 bytes

也就說,作為 index 的欄位,他的長度(bytes)已經超過 MySQL 規定的 767 bytes 了,所以被 MySQL 拒絕繼續進行操作。

什麼時候會採用 utf8mb4 作為 MySQL 的編碼?

在一般的情況下,其實 utf8 編碼就已經很夠用了,然而在 MySQL 6.0 前, 使用 utf8 一個字元最多只使用 3-Byte,所以他並不能表達所有 unicode 的字符。若是你的網站會使用的一些罕見字符或是有使用者是用罕見字元當做人名,就可能會不在 utf8 所支援的字符中。所以在 MySQL 5.5 時,新增了 utf8mb4 作為擴充,使最大佔用長度增為 4-byte。

或許你會以為這種情況離生活很遠,但事實上在這個行動裝置隨處可見的情況下,這種情況反而更容易發生。為什麼?因為現在手機、平板等的輸入法,都多會附上表情符號諸如 😎😀😁😂😃😄😅😆,這 8 個表符都各佔了 4-byte,也就是超過 utf8 的支援。我想讀者應該對這些符號都挺有印象的,所以這種採用 utf8mb4 作為 MySQL 的編碼的需求其實是離我們開發者越來越近。

為什麼 Rails 改用 utf8mb4 編碼就會超過長度呢?

那是因為 Rails ActiveRecord 的 :string 資料欄位,等同於 MySQL 長度為 255 的 VARCHAR 欄位類型,所以使用 utf8 時,會使用 255 * 3 = 765 (bytes)。

當編碼改成 utf8mb4 後,每個字元固定長度為 4-byte,所以大小就會有 255 * 4 = 1020 (bytes) ,而超過 MySQL 用來當作索引的鍵值長度的最大值。這也就是為什麼在資料庫 Migrate 會報錯的原因所在。

解決方案 (1)

方向

那我們該如何解決這個問題呢?這裡提供的方案僅限於目前網站在開發,並沒有擁有需要保存資料的情況下才能實行。因為這個方案是透過修改 Rails 在資料表架構上,VARCHAR 類型欄位的預設長度。若是已經有資料的網站,可能會因為修改長度比現存的資料小,導致無法執行。

承襲上章所闡述的問題原因,為了改善這個問題,我們在這裡將 VARCHAR 的長度改成鍵值長度最大值以內。由算式 767 / 4 = 191 ... 3 可得知,在這個範圍內若以 4-byte 來儲存 1 個字符,我們共可以儲存 191 個字符,最大長度就只會到 764 bytes,符合 MySQL 標準,因而解決這個問題。所以解決方向就如前面所述,要將 Rails 的 VARCHAR 類型欄位的預設長度改成 191 個字符。

方法

這個方法來自於 Github Rails/Rails 專案中的 Issue #9885 中 @nanaya 的留言。

我們在專案目錄下新增 ./config/initializers/mysqlpls.rb 這個檔案,然後將下面的程式碼貼近檔案中儲存,就會將 VARCHAR 類型欄位的預設長度從 255 個字符改成 191 個字符,解決了這個問題。

# config/initializers/mysqlpls.rb
require 'active_record/connection_adapters/abstract_mysql_adapter'

module ActiveRecord
  module ConnectionAdapters
    class AbstractMysqlAdapter
      NATIVE_DATABASE_TYPES[:string] = { :name => "varchar", :limit => 191 }
    end
  end
end

這個程式碼主要是覆蓋掉原本 Rails 在這部分的設定,也就是說我要針對 ActiveRecord module 下的 ConnectionAdapters module 中的 AbstractMysqlAdapter Class 裡的 NATIVE_DATABASE_TYPES 雜湊常數中的 :string 鍵值所代表的值做重新定義。講得這麼密麻麻,可能有點不懂,我們可以先看看原始針對這部份的定義,如下

NATIVE_DATABASE_TYPES = {
  primary_key: "int auto_increment PRIMARY KEY",
  string:      { name: "varchar", limit: 255 },
  text:        { name: "text" },
  integer:     { name: "int", limit: 4 },
  float:       { name: "float" },
  decimal:     { name: "decimal" },
  datetime:    { name: "datetime" },
  time:        { name: "time" },
  date:        { name: "date" },
  binary:      { name: "blob" },
  boolean:     { name: "tinyint", limit: 1 },
  json:        { name: "json" },
}

這部份就是將 migration 中定義的資料類型轉成 MySQL 的欄位類型,而我們就是將原本 :string 類型定義的長度做了修改並覆蓋。這樣解釋應該好懂很多。想要更深入了解的可以直接看 Rails 關於這部分的原始碼囉。

總結

本解決方案是直接縮減 Rails 在 ActvieRecord 裡 :string 資料類型中的長度,以符合 MySQL 所支援的大小。

但如前面所述,若是你已經有資料,且你無法確保現有資料中 :string 資料類型的值都在 191 以下的話,就會比較麻煩。可能就要先將結構與資料分別 dump,改完結構後,再將資料中超過長度的值做縮減,最後再重新 import 到資料庫裡。這部分可參考 XDite 關於這部分所書寫的部落格文章

解決方案 (2)

這個方案我不打算如前一個方案般詳述,主要是因為我沒有實際去實驗,而只是將網路上看到的資料做個整理、筆記而已,實際運作上狀況如何,就不敢保證了,但至少紀錄了一個方向。

方向

在 MySQL 5.5.14 時,新增了一個名為 innodb_large_prefix 的功能,啟用後可將鍵值長度擴充到 3072 bytes。而這個功能相依於 MySQL 5.5 新增的一個叫做 Barracuda 的 innodb 的檔案格式(File Formate),這個檔案格式除了支援 innodb 原有的資料列格式(Row Formats),還包括了新的資料列格式:COMPRESSED 和 DYNAMIC。

所以要解決這個問題,就是修改 MySQL 的選項,將檔案格式改成 Barracuda,並且啟用 innodb_large_prefix 這個選項去擴充鍵值長度。最後在 Rails 建立表格時,指定使用 DYNAMIC 的資料列格式即可。

方法

在進行本方法前,請先將資料庫備份,或是使用虛擬機器或是容器開一個新的資料庫伺服器作為練習、試驗

在 MySQL 的設定檔 my.conf 加入下面內容:

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

在 Rails 部分有兩個選項,擇一即可,見仁見智。有興趣的可以參考在同一篇 Issue 裡,從這篇回覆開始的討論,主要就是 @NaN1488 和 @yyyc514 兩人回覆的部分。

Rails (1)

在 migration 中的 create_table 方法加入 option 參數:

create_table :table_name, options: 'ROW_FORMAT=DYNAMIC' do |t|
  ...

Rails (2)

若是覺得要在每個有建立資料表的 migration 一一加入上述選項的參數太麻煩的話,也可以直接加入一個 MonkeyPatch。這個 patch 會幫你在建立資料表時直接幫你加入該參數。

在專案目錄下新增 ./config/initializers/ar_innodb_row_format.rb 這個檔案,並貼上程式碼:

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

總結

與前一方案是透過修改 Rails 對 string 欄位類型的預設長度達到目的,這個方案則是透過升級、修改 MySQL 的設定檔,擴大鍵值長度去修正這個錯誤。

結語

兩個方案方向不一樣,所以也沒有孰優孰劣,就看開發者當前的專案適合哪個方案了。若是沒有資料庫修改權限的,第一個方案我想就是一個挺貼切的做法,也讓專案在部署的難度降低。若是擁有資料庫伺服器管理權限,也可以使用第二個方案,直接一勞永逸囉。

附錄:Unicode 與 UTF

Unicode 是一種文字、符號在電腦科學領域的業界標準,由非營利機構 The Unicode Consortium 負責。在 1991 年 10月發布第一個版本,隔年 6 月發布 1.0.1 擴增了中日韓統一表意文字最初的 20,902 個字,之後陸陸續需編修與擴增。最近的發布是在 2015 年 6 月時的 8.0 版本,總計有 120,737 個字符。

UTF 為 Unicode Transformation Format,中文為 Unicode轉換格式,即為 Unicode 的實作方式。

UTF-8

UTF-8 的全名為 8-bit Unicode Transformation Format,即以 1 個位元組(1-byte = 8-bit)為單位,用 1 到 4 個位元組表示一個字符編碼1

由於 UTF-8 的長度是可變動的,所以 ASCII 可以作為 UTF-8 的一個子集,所以現存的 ASCII 文字不需要轉換。

utf8mb3、utf8mb4

utf8mb3utf8mb4 都是 MySQL 所支援的 Unicode 編碼實作,兩者的差別在於 utf8mb3 最大佔用長度為 3-byte,utf8mb4 則為 4-byte。所以其實兩者幾乎是一樣的,但是 utf8mb4 多了對擴充字符的支持,如本篇文章所提到的表情符號。

utf8mb4 是 MySQL 5.5 所新增的,因為原本 MySQL 5.5 的 utf8 只有支持最大佔用長度為 3-byte,所以新增這個實作作為擴充。到了 MySQL 6.0,原本的 utf8 被更名為 utf8mb3,並加入新的 utf8 編碼實作,並預設支最大佔用長度為 4-byte。2

參考資料

  1. MySQL utf8mb4 breaks ActiveRecord schema setup
  2. How to support full Unicode in MySQL databases
  3. Encoding: Living Standard
  4. UTF-8 encoding table and Unicode characters
  5. UTF-8 string length & byte counter
  6. MySQL(InnoDB) で “Index column size too large. The maximum column size is 767 bytes.” いわれるときの対策

  1. 原來的規範允許長達 6 位元組,但是在 2003 年 11 月 UTF-8 被 RFC 3629 重新規範,只能使用原來 Unicode 定義的區域,即 U+0000U+10FFFF 。 
  2. 這部分可以參照 MySQL 6.0.4 Alpha 的 Release Note。 

Information Technology , , ,