Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel "Specified key was too long" when creating notifications table

I am using Laravels' default migration to create a notifications table.

public function up()
{
    Schema::create('notifications', function (Blueprint $table) {
        $table->uuid('id')->primary();
        $table->string('type');
        $table->morphs('notifiable');
        $table->text('data');
        $table->timestamp('read_at')->nullable();
        $table->timestamps();
    });
}

But I get an error when trying to use it:

[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 `notifications` add index `n
  otifications_notifiable_id_notifiable_type_index`(`notifiable_id`, `notifiable_type`))



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



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

Update

I changed the name of the index column to notifiable_index, but it still complaints about the length of the index key.

  [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 `notifications` add index `n
  otifiable_index`(`notifiable_id`, `notifiable_type`))



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



  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
like image 527
naneri Avatar asked Jan 11 '17 21:01

naneri


2 Answers

If you are using Laravel 5.4 and running a version of MySQL older than the 5.7.7 release. You may solve this by calling the Schema::defaultStringLength method within your AppServiceProvider class's boot method.

public function boot()
{
    Schema::defaultStringLength(191);
}
like image 149
Mahbub Avatar answered Nov 15 '22 07:11

Mahbub


What I ended up doing is I just created a manual index for the notifiable id. Here is the migration:

    Schema::create('notifications', function (Blueprint $table) {
        $table->uuid('id')->primary();
        $table->string('type');
        $table->text('data');
        $table->timestamp('read_at')->nullable();
        $table->timestamps();

        $table->unsignedInteger("notifiable_id");
        $table->string("notifiable_type");
        $table->index('notifiable_id');
    });

Instead of having the command:

 $table->morphs('notifiable');

I have 3 lines:

    $table->unsignedInteger("notifiable_id");
    $table->string("notifiable_type");
    $table->index('notifiable_id');

The downside is that the index is not combined based on type+id, it is only based on id. But because I only have User model that can be notified, that is ok for me.

like image 44
naneri Avatar answered Nov 15 '22 08:11

naneri