3

I have a database table in my Laravel 8 Project to which I want to assign MyISAM. I do this with this line $table->engine = "MyISAM"; in the migration file. When I start php artisan migrate I get the following error message:

   Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too
  long; max key length is 1000 bytes (SQL: alter table `view_counters` add index
  `view_counters_countable_type_countable_id_index`(`countable_type`,
  `countable_id`))

Thats my migration file:

        Schema::create('view_counters', function (Blueprint $table) {
            $table->engine = "MyISAM"; 
            $table->id();
            $table->morphs('countable');
            $table->integer('views')->default(0);
        });

I have already read that in this case you can add Schema::defaultStringLength(191) to the boot() method of the AppServiceProvider to avoid this error.

QUESTION: I would like to know what exactly the error message means? Maybe my approach to write the database engine in the migration is wrong. Who knows more?

Max Pattern
  • 1,430
  • 7
  • 18
  • Does this answer your question? [Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/42244541/laravel-migration-error-syntax-error-or-access-violation-1071-specified-key-wa) – miken32 Jan 29 '22 at 19:15

1 Answers1

3

That error has to do with the string length. By default, laravel's creates string columns with a length of 255 using the charset/collation utf8mb4 (4-byte UTF-8 Unicode Encoding).

Simply lower the default setting in your AppServiceProvider class. (app\Providers\AppServiceProvider.php)

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191); // I think you can use 250 or 249 instead
}

I think you can use 250 (250 * 4 = 1000 <= 1000) or 249 (249 * 4 = 996 < 1000) instead of 191.

The default, 255 (255 * 4 = 1020 > 1000) goes over the limit.

The reason the documentation uses 191 is because InnoDB's index limit is at 767 (191 * 4 = 764 < 767)

More information: 8.x - Migrations - Index Lengths & MySQL / MariaDB

IGP
  • 14,160
  • 4
  • 26
  • 43
  • Thank you! It means if I assign length to every column in my table which are smaller as 255 then the error will avioded? – Max Pattern Jan 29 '22 at 16:14
  • 1
    You just need to use the `Schema::defaultStringLength` once and it's going to fix the error. In this case, the error comes from `morphs('countable')` creating a string clolumn in the background and then adding it to an index. With the new default length, it shouldn't throw you any more errors when you migrate – IGP Jan 29 '22 at 16:17