laravel 5.4 specified key was too long, why the number 191

Question

I know this problem is discussed, and solved or closed.
But what I want to ask is, why is the number 191?

Schema::defaultStringLength(191);

I tried: if 192, wrong. 191, ok. After this, I also edit database\migrations\xxx_user_table.php
Change this

$table->string('name');

to be

$table->string('name', 255);

Then run php artisan migrate, it's ok. Use some mysql tool to see the schema of user table, length of name which is varchar is actually 255, others columns are 191.

I saw these articles
@ https://laravel-news.com/laravel-5-4-key-too-long-error
@ Laravel migration: unique key is too long, even if specified
@ https://github.com/laravel/framework/issues/17508
@ https://laravel.com/docs/master/migrations#creating-indexes

And this: @ https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.

But why length 255 of name is ok?
And 192*3 = 576, smaller than 767, why 192 not ok?

Answer 1

Why is it limited to 191 characters?

The key is that Laravel 5.4 defaults to utf8mb4 so the database assumes a maximum of 4 bytes for each character.

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

The example you've cited lines up perfectly if updated for utf8mb4. Emphasis added where edited.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

• 191 * 4 = 764 (works)
• 192 * 4 = 768 (too large)

utf8mb4 introduces support for Supplementary Characters which were not part of utf8 which include among other things, emoji. For further reading on whether you need utf8mb4, you might start with this StackOverflow answer on the question.

How do I fix this?

The quickest fix, if you need to maintain the larger field size, is to change your defaults down to utf8. Laravel 5.3 and below defaulted utf8, and that would be safe to use in most cases.

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Alternatively, you can set the charset and collation on specific tables in the DB Migration when creating that table. That would look something like this:

Schema::create('monitors', function (Blueprint $table) {
    $table->charset = 'utf8';
    $table->collation = 'utf8_unicode_ci';

    $table->increments('id');
    $table->string('myString')->unique();
});

What If I need utf8mb4 and long index keys?

This StackOverflow answer should be a good starting point. It discusses setting innodb_large_prefix to allow longer key lengths.