Laravel 5.4 migrated error: Specified key was too long error

created at 07-06-2021 views: 1

description

Laravel 5.4 uses utf8mb4 character encoding by default instead of the previous utf8 encoding. Therefore, the following error will appear when running php artisan migrate:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email))

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

reason of the problem

The maximum character length of utf8 encoding supported by MySql is 3 bytes. If a 4-byte wide character is encountered, an insertion exception will occur. The maximum Unicode character that can be encoded by three-byte UTF-8 is 0xffff, which is the Basic Multilingual Plane (BMP) in Unicode. Therefore, non-basic multilingual Unicode characters including Emoji expressions (Emoji is a special Unicode encoding) cannot be stored using MySql's utf8 character set.

This should be one of the reasons why Laravel 5.4 uses the 4-byte utf8mb4 character encoding. However, it should be noted that utf8mb4 character encoding is only supported after MySql version 5.5.3 (check version: select version();). If the version of MySql is too low, a version update is required.

Note: If you are upgrading from Laravel 5.3 to Laravel 5.4, you do not need to switch the character encoding.

solution

  1. Upgrade MySql version to 5.5.3 or higher.
  2. Manually configure the default string length generated by the migration command migrate, and call the Schema::defaultStringLength method in AppServiceProvider to implement the configuration:
    use Illuminate\Support\Facades\Schema;

    public function boot()
    {
       Schema::defaultStringLength(191);
    }
Please log in to leave a comment.