davy_yg's avatar
Level 27

Specified key was too long; max key length is 1000 bytes

Any idea why I get this error message? It works on my localhost.

[agribisc@jkt09 public_html]$ php artisan migrate

INFO Running migrations.

2014_10_12_100000_create_password_reset_tokens_table ................................................. 3ms FAIL

Illuminate\Database\QueryException

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes(Connection: mysql, SQL: create table password_reset_tokens (email varchar(255) not null, token varchar(255)not null, created_at timestamp null, primary key (email)) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

0 likes
2 replies
LaryAI's avatar
LaryAI
AI
Best Answer
Level 58

The error you're encountering is related to the maximum key length in MySQL when using the utf8mb4 character set. The utf8mb4 character set uses up to 4 bytes per character, which can exceed the 1000-byte limit for an index in older versions of MySQL or when using certain storage engines.

Here are a few solutions to resolve this issue:

  1. Change the Character Set: If you don't need the full range of Unicode characters, you can switch to utf8, which uses up to 3 bytes per character. However, this is not recommended if you need to support emojis or other 4-byte characters.

  2. Modify the Key Length: You can specify a shorter length for the indexed column. For example, if you know that the email addresses will not exceed a certain length, you can limit the index to that length.

  3. Upgrade MySQL: If possible, upgrade to a newer version of MySQL that supports larger index sizes.

  4. Use InnoDB with Barracuda and DYNAMIC/COMPRESSED Row Format: If you're using InnoDB, ensure that the innodb_file_format is set to Barracuda and the innodb_large_prefix is enabled. This allows for larger index key prefixes.

Here's how you can modify your migration to specify a shorter index length:

Schema::create('password_reset_tokens', function (Blueprint $table) {
    $table->string('email', 191)->primary(); // Limit the email column to 191 characters
    $table->string('token', 255);
    $table->timestamp('created_at')->nullable();
});

The 191 character limit is a common workaround because 191 * 4 = 764 bytes, which is under the 1000-byte limit.

Additionally, ensure your database configuration in config/database.php is set to use utf8mb4:

'mysql' => [
    'driver' => 'mysql',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    // other configurations...
],

After making these changes, try running your migrations again.

Snapey's avatar

Point 2. is the sensible option. No one needs 255 character email addresses.

Please or to participate in this conversation.