Laravel Migration Error: Syntax error or access vi

Question

Migration error on Laravel 5.4 with php artisan make:auth

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter tabl e 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

Answer 1

The recommended solution is to enable innodb_large_prefix option of MySQL so you won't be getting into subsequent problems. And here is how to do that:

Open the my.ini MySQL configuration file and add the below lines under the [mysqld] line like this.

[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_file_per_table = ON

After that, save your changes and restart your MySQL service.

Rollback if you need to and then re-run your migration.

---

Just in case your problem still persists, go to your database configuration file and set

'engine' => null, to 'engine' => 'innodb row_format=dynamic'

Hope it helps!

Answer 2

The approached that work here was pass a second param with the key name (a short one):

$table->string('my_field_name')->unique(null,'key_name');

Answer 3

I am adding two sollution that work for me.

1st sollution is:

1. Open database.php file insde config dir/folder.

2. Edit 'engine' => null, to 'engine' => 'InnoDB',

   This worked for me.

2nd sollution is:

1. Open database.php file insde config dir/folder.
   2.Edit
   'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
   to
   

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

Goodluck

Answer 4

This issue is caused in Laravel 5.4 by the database version.

According to the docs (in the Index Lengths & MySQL / MariaDB section):

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider.

In other words, in <ROOT>/app/Providers/AppServiceProvider.php:

// Import Schema
use Illuminate\Support\Facades\Schema;
// ...

class AppServiceProvider extends ServiceProvider
{

public function boot()
{
    // Add the following line
    Schema::defaultStringLength(191);
}

// ...

}

But as the comment on the other answer says:

Be careful about this solution. If you index email fields for example, stored emails can only have a max length of 191 chars. This is less than the official RFC states.

So the documentation also proposes another solution:

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Answer 5

For anyone else who might run into this, my issue was that I was making a column of type string and trying to make it ->unsigned() when I meant for it to be an integer.

Answer 6

For someone who don't want to change AppServiceProvider.php. (In my opinion, it's bad idea to change AppServiceProvider.php just for migration)

You can add back the data length to the migration file under database/migrations/ as below:

create_users_table.php

$table->string('name',64);
$table->string('email',128)->unique();

create_password_resets_table.php

$table->string('email',128)->index();