Cách sửa lỗi Specified key was too long với phiên bản Laravel 5.4+

Chung Tran -

Khi bạn kết nối Laravel app (phiên bản 5.4 trở lên) của bạn với MySQL phiên bản dưới 5.7 bạn có thể sẽ gặp phải lỗi Syntax error or access violation: 1071 Specified key was to long: max key length is 767 bytes và sau đó là kèm theo 1 lệnh SQL add index tương tự như ảnh dưới.
với các

Tôi đã gặp lỗi này khi setup dự án của mình trên Codeship(một dịch vụ CI/CD trên Cloud) và thắc mắc là tại sao trên máy local của mình không bị, còn Codeship lại bị ?
Thử Google Search lỗi này và hầu hết sẽ đưa ra 1 cách fix nhanh gọn trong 1 nốt nhạc bằng cách sửa app/Providers/AppServiceProvider.php như dưới đây.

use Illuminate\Support\Facades\Schema; //Import Schema

function boot()
{
    Schema::defaultStringLength(191); //Solved by increasing StringLength
}

Cơ mà vấn đề là tôi có thắc mắc:

  1. Như vậy thì tôi sẽ bị giới hạn độ dài mặc định của các trường String trong dự án từ 255 xuống còn có 191 ký tự ?
  2. Còn có cách fix nào tốt hơn không?

Tôi đã thử kiểm tra phiên bản mysql của mình trên terminal bằng lệnh mysql --version

Và Codeship thì đang dùng Mysql 5.6 theo như thông tin ở Codeship Official Doc của họ.
và thử lại bằng cách add lệnh mysql --version vào phần setup commands của project trên Codeship để kiểm chứng lại, thì đúng là Codeship đã dùng Myql 5.6 thật.

Vấn đề là dựa vào thông báo lỗi thì tôi gặp exception khi tạo mới index cho table chứ không phải gặp vấn đề khi định nghĩa các column trong table.
Và 2 con số 1071 và 767 ở đâu ?

Kiểm tra lại kết nối mặc định của Laravel[1] trong file config/database.php phần mysql

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Có thể thấy là Laravel từ phiên bản 5.4 trở lên đã đổi collation từ utf8_unicode_ci sang 'utf8mb4_unicode_ci' và đang để engine là null nên sẽ dùng engine mặc định của Mysql là InnoDB.

Với MySQL có một số giới hạn về độ dài của index được thay đổi từ bản 5.7
Với InnoDB thay đổi từ 767 bytes ở bản 5.6 lên thành 3072 bytes ở bản 5.7

Nên với collations là utf8_unicode_ci, mỗi ký tự lưu có độ dài 3 bytes => index sẽ có độ dài : 255 * 3 = 765 < giới hạn 767 bytes của MySQL 5.6, sẽ không có hiện tượng gì sảy ra.
Còn với collations là 'utf8mb4_unicode_ci' mỗi ký tự lưu có độ dài 4 bytes => index có độ dài: 255 * 4 = 1020 bytess > giới hạn 767 bytes của MySQL 5.6 và nhỏ hơn độ dài giới hạn của 5.7 là 3072 bytes nên MySQL 5.7 không gặp vấn đề còn MySQL 5.6 thì gặp lỗi.

Vấn đề với MySQL 5.6 là bạn cần làm sao để độ dài index của bạn có độ dài nhỏ hơn giới hạn 767 bytes, và cộng đồng mạng đã có cách xử lý là set độ dài về 191 ( 767/4 = 191.75).

Nên thực tế bạn có thể giới hạn lại độ dài hoặc chuyển về sử dụng collation là utf8_unicode_ci thay vì utf8mb4_unicode_ci như mặc định của Laravel tuỳ vào nhu cầu của bạn trong trường hợp bạn không thể nâng cấp lên MySQL 5.7, trường hợp với Codeship là 1 ví dụ, sẽ khá phức tạp khi thay đổi mặc định MySQL 5.6 của Codeship bản Basic, có thể sẽ dùng cách kết nối với DB Test ở bên ngoài.

Nếu đã chốt thiết kế với khách hàng thì nên đổi về utf8_unicode_ci và lường trước 1 số vấn đề unit test gặp lỗi khi utf8_unicode_ci không lưu được một số ký tự đặc biệt.

Chú thích:

[1] Laravel thì đang để mặc định 'collation' => 'utf8mb4_unicode_ci', từ 5.4 trở lên. Bạn có thể kiểm chứng bằng cách xem source của dự án trong file config/database.php tại đây