In your appserviceprovider boot method, try adding

Schema::defaultStringLength(191);

Best sure to import Illuminate\Support\Facades\Schema at the top of the service provider. There’s a note about this in the 5.4 documentation. It’s possible this is what is causing your problems.

I added to the migration itself

        Schema::defaultStringLength(191);
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

yes, I know I need to consider it on every migration but I would rather that than have it tucked away in some completely unrelated service provider

solved mine by changing the collation in the database.php file located in the config folder of my project changed it from ‘charset’ => ‘utf8mb4’, ‘collation’ => ‘utf8mb4_unicode_ci’,

to ‘charset’ => ‘utf8’, ‘collation’ => ‘utf8_unicode_ci’, apparently the ‘utf8mb4’ collation takes more characters than the ‘utf8’ collation

In case someone else ends up here first and still banging his head, the whole solution for Maria/MySQL below 10.2/5.7 is explained here: http://stackoverflow.com/questions/23786359/laravel-migration-unique-key-is-too-long-even-if-specified/39750202#39750202

For me, the one part that did the trick after correctly setting everything else and which I was still missing until I found that post, is:

In Laravel config/database.php define: ‘engine’ => ‘InnoDB ROW_FORMAT=DYNAMIC’,

@devcircus : why using 191 ?

I got this error too with MySQL 5.7.11.

As @devcircus said, the workaround on https://laravel.com/docs/5.4/migrations#indexes works.

UPDATE

According to @rbkkm, to fix this issue, you can decrease the index rather than the field size:

This lets you use the full 255 characters at the “expense” of index length, rather than getting the full index length at the expense of the varchar field length.

What @rbkkm seems to be suggesting is this:

$table->index([DB::raw('email(191)')]);
$table->unique([DB::raw('email(191)')]);

So for example, the migration of password_resets’s up() method would look like this:

Schema::create('password_resets', function (Blueprint $table) {
    $table->string('email');
    $table->string('token');
    $table->timestamp('created_at')->nullable();

    $table->unique([DB::raw('email(191)')]);
});

And for the migration of users:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email');
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();

    $table->index([DB::raw('email(191)')]);
});

I was also facing the same problem. This is for using wamp and using phpmyadmin to access mysql.

1. Go to config>database.php
2. Change ‘mysql’ => [ ‘driver’ => ‘mysql’, ‘host’ => env(‘DB_HOST’, ‘127.0.0.1’), ‘port’ => env(‘DB_PORT’, ‘3306’), ‘database’ => env(‘DB_DATABASE’, ‘forge’), ‘username’ => env(‘DB_USERNAME’, ‘forge’), ‘password’ => env(‘DB_PASSWORD’, ‘’), ‘unix_socket’ => env(‘DB_SOCKET’, ‘’), ‘charset’ => ‘utf8mb4’, ‘collation’ => ‘utf8mb4_unicode_ci’, ‘prefix’ => ‘’, ‘strict’ => true, ‘engine’ => null, ],``

with

‘mysql’ => [ ‘driver’ => ‘mysql’, ‘host’ => env(‘DB_HOST’, ‘127.0.0.1’), ‘port’ => env(‘DB_PORT’, ‘3306’), ‘database’ => env(‘DB_DATABASE’, ‘forge’), ‘username’ => env(‘DB_USERNAME’, ‘forge’), ‘password’ => env(‘DB_PASSWORD’, ‘’), ‘unix_socket’ => env(‘DB_SOCKET’, ‘’), ‘charset’ => ‘utf8’, ‘collation’ => ‘utf8_unicode_ci’, ‘prefix’ => ‘’, ‘strict’ => true, ‘engine’ => null, ],

I hope this helps.

AppServiceProvider.php It has been modified

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
        Schema::defaultStringLength(191);
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

But still is error！

λ php artisan voyager:install --with-dummy
Publishing the Voyager assets, database, and config files
Copied Directory [\vendor\tcg\voyager\publishable\assets] To [\public\vendor\tcg\voyager\assets]
Copied Directory [\vendor\tcg\voyager\publishable\database\migrations] To [\database\migrations]
Copied Directory [\vendor\tcg\voyager\publishable\database\seeds] To [\database\seeds]
Copied Directory [\vendor\tcg\voyager\publishable\demo_content] To [\storage\app\public]
Publishing complete.
Publishing complete.
Migrating the database tables into your application
Migration table created successfully.


  [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 `tran
  slations` add unique `translations_table_name_column_name_foreign_key_locale_unique`(`table_name`, `column_name`, `foreign_key`, `locale
  `))



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



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

If you are on Mysql < 5.7 or MariaDB < 10.2 then enable innodb_large_prefix this will let you have 255 fields on your database you won’t need to touch any code.

On these other versions you don’t need, it works out of the box.

Should this not be considered an open problem? I understand that it’s a documented problem, but shouldn’t we expect some level of backwards compatibility with homestead? My homestead version is almost a year old and a new deploy caused this problem for me. The above solution solved it for me.

Got this Error too with Laravel 5.4.9, Spark 4.0, Valet v2.0.3 & 10.1.16-MariaDB Homebrew. Adding Schema::defaultStringLength(191); in the AppServiceprovider boot Method worked for me in this Case.

Hi everyone, in the version of Laravel 5.6 I’ve fix the issue changing the .env file in the line

DB_HOST=127.0.0.1 to DB_HOST=localhost

Hi guys,

The 100% fix is here:

Step 1: Include Facades/Schema in AppServiceProvider.php file use Illuminate\Support\Facades\Schema;

Step 2: Edit your AppServiceProvider.php file and inside the boot method set a default string length-

public function boot()
{
    Schema::defaultStringLength(191);
}

Step 3: Drop users, password_resets, migrations tables if already created.

Step 4: Run the php artisan migrate command. Done!

Thank you so much 😃

This is a really BIG issue. Trim the column is ridiculous, u should trim the index.

“Best” Solution: Use Smaller Indexes This solution fixes the underlying issue and allows us to avoid issues caused by any customer’s particular configuration of MySQL.

On VARCHAR fields (this hasn’t been necessary on INT fields, and generally only FULLTEXT indexes are created on TEXT fields), we have the option to use smaller indexes; Up to the first 191 characters.

– Index first 20 characters of the username column ALTER TABLE users ADD INDEX username_index_name (username(20)) This lets you use the full 255 characters at the “expense” of index length, rather than getting the full index length at the expense of the varchar field length.

Depending on your use case, this solution can provide a speed boost to MySQL, as we’re not storing as much information in the indexes (making it faster for MySQL to search the index). It definitely will remove warnings and errors about the index length when using the utf8mb4 character set.

This also makes an application less susceptible to issues to using within different/unknown MySQL environments.

Because this could be managed within our code, not create an issue where customers needed to do MySQL configuration changes, and wouldn’t risk truncating “legacy” customer data, this was our most viable solution.

@devcircus Thank you!!!

Thanks @devcircus your solution solved my problem.

Please, guys. Do not post comments if it does not add anything of value to the issue thread. Every subscriber to the issue gets a useless email. Show your thanks by using the 👍 emoji like the others did.

@snapey I like Snapey’s solution

Thank you @devcircus , adding this line fixed my issue.

It should be noted however that the problem occurs on my Windows computer (if I don’t add the extra line of code in the boot() method) despite running MySQL 5.7.14 on that computer. The documentation says that there could be an issue with versions before 5.7.7, but I’m having a problem with a newer version as well.

Edit you mysql.ini file and change default-storage-engine=MYISAM to default-storage-engine=InnoDB if you dont want to change Laravel files, don’t forget to restart the mysql server afterwards.

If you preffer to change your laravel files, then open /config/database.php and in the “MYSQL Connection” replace 'engine' => null, with 'engine' => 'InnoDB',

so that it becomes like this:

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => "InnoDB",
        ],

NOTE: nevertheless, its my belief that Laravel should have a validation of the MySQL default engine, and adjust accordingly the key size, but i’m not 100% sure that this would not affect other areas of Laravel’s framework.

exemple plzzz

I use this fix:

edit the file vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php

and change null values to 191 in public function char($column, $length = null){ … }

and

public function string($column, $length = null){ … }

I have fixed this error.my god… @Easter1021 whatever thank you very much ^.^

open database.php and then

‘mysql’ => [ ‘driver’ => ‘mysql’, ‘host’ => env(‘DB_HOST’, ‘127.0.0.1’), ‘port’ => env(‘DB_PORT’, ‘3306’), ‘database’ => env(‘DB_DATABASE’, ‘forge’), ‘username’ => env(‘DB_USERNAME’, ‘forge’), ‘password’ => env(‘DB_PASSWORD’, ‘’), ‘unix_socket’ => env(‘DB_SOCKET’, ‘’), ‘charset’ => ‘utf8mb4’, ‘collation’ => ‘utf8mb4_unicode_ci’, ‘prefix’ => ‘’, ‘strict’ => true, ‘engine’ => ‘INNODB’, ],

Schema::defaultStringLength(191);

As outlined in the Migrations guide to fix this all you have to do is edit your app/Providers/AppServiceProvider.php file and inside the boot method set a default string length:

    use Illuminate\Support\Facades\Schema;
    
    public function boot()
    {
        Schema::defaultStringLength(191);
    }

first you have to delete (if you have) users table, password_resets table from the database and delete users and password_resets entries from migrations table.

To run all of your outstanding migrations, execute the migrate Artisan command:

php artisan migrate

File: config/database.php change the following FROM -> ‘charset’ => ‘utf8mb4’, ‘collation’ => ‘utf8mb4_unicode_ci’,

TO -> ‘charset’ => ‘utf8’, ‘collation’ => ‘utf8_unicode_ci’,

@medeirosjoaquim NEVER EVER change the contents of the vendor folder. One update and your code will be reverted and you will have a broken project and no idea why

I don’t really understand why people struggle so much with this issue. It ONLY affects strings that you might want to index or apply the unique rule for. For these, just set the length of the string to something sensible for the data you are storing. Do you really need your email field to be 350 characters? The second parameter in the migration after the name is the length. Just key something sensible;

$table->string('email',100)

Job done

@mblackritter thank you worked for me

My solution:

When you create your database don’t forget to specified the charset and the collation. I have this issue because my charset was charset => utf8 and my collation collation => utf8_general_ci while laravel is still using the options charset => utf8mb4 and collation => utf8mb4_unicode_ci to migrate.

So to resolve this issue just change in your config file database.php by the right charset and collation specified in your schema.

Version:

• laravel 5.4
• mariadb 10.1.22

From the docs:

Index Lengths & MySQL / MariaDB

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:

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

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.

@shyandsy read the article on servers for hackers. It explains it well. 191 is the largest string that can be used in an index when that string is in utf8mb4 format. This applies to fields that are explicitly indexed or have unique attribute.

Surprised that noone has mentioned this article https://serversforhackers.com/mysql-utf8-and-indexing. Its two years old but describes the reason for the issue.

Two takeaways from this.

1. Your hands might be tied for a solution if you have data to preserve.
2. You only need to worry about indexed or unique columns so in the standard auth migrations, the 191 length only needs to be applied to the email column.

$table->string('email',191)->unique();

@sicaps thanks for your reply. I also have that in my AppServiceProvider.php. Anyway, I manage to fix it by removing the length of the string, before one of my index is like this $table->string('name', 200) I just change it to $table->string('name') and it works.

Check that your AppServiceProvider.php It has been modified to include what is in bold:

namespace App\Providers;

use Illuminate\Support\ServiceProvider; use Illuminate\Support\Facades\Schema;

Thanks @devcircus your solution solved my problem.

Just had a fresh install, still getting this problem. Adding the Schema::defaultStringLength(191); that @devcircus was the trick.

Search the issues. This has been discussed many times.

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 users add unique users_email_unique(email))

Answer: Dir: App/Providers/AppServiceProvider.php

Add line in Top: use Illuminate\Support\Facades\Schema;

Then: public function boot() { // Schema::defaultStringLength(191); }

I think this is the answer .

https://laravel-news.com/laravel-5-4-key-too-long-error

thnx its good i find

@jamel2020 Just use @rbkkm’s solution, as I described here: https://github.com/laravel/framework/issues/17508#issuecomment-275857845.

@fernandobandeira Here you go: https://github.com/laravel/docs/pull/4075.

I can’t believe that Laravel deploys 5.5 with such a hassle about indexes on large columns, and only gives wrong solutions. I looked for a good solution for 2 weeks.

@ayushpratap Switching to the utf8 charset and collation removes proper UTF8 support (yes, this sounds backwards as all hell). utf8mb4 is the only charset that proply implements the entire set of UTF8 characters, which is why this is an issue.

Set your mysql engine to InnoDB ROW_FORMAT=DYNAMIC.