laravel error 1071 key was too long php artisan migrate

Question

I am using Laravel and I have migration with function:

public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title')->unique();
            $table->string('slug')->unique();
            $table->string('subtitle');
            $table->integer('price');
            $table->text('description');
            $table->boolean('featured')->default(false);
            $table->timestamps();
 
        });
    }

and when i do 'php artisan migrate' on my cmd i get error

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1071 La clé est trop longue. Longueur maximale: 1000 (SQL: alter table `users` add unique `users_username_unique`(`username`))

  at C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  > 671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675|

  1   C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 La clé est trop longue. Longueur maximale: 1000")

  2   C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOStatement::execute()

I added that to my AppServiceProvider

<?php

namespace App\Providers;

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

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

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

and this is my database.php

 'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'laravel-ecommerce'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => 'InnoDB',
        
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],

and my .env looks like

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel-ecommerce
DB_USERNAME=root
DB_PASSWORD=

i already uncomment the extension ;extension=pdo_mysql in my php.ini i am using wamp server and already tried this :

php artisan cache:clear                    
php artisan config:clear                      
php artisan config:cache

when I use mariaDB instead of mysql to create my database I get this error unknown database

 Illuminate\Database\QueryException

  SQLSTATE[HY000] [1049] Base 'laravel-ecommerce' inconnue (SQL: select * from information_schema.tables where table_schema = laravel-ecommerce and table_name = migrations and table_type = 'BASE TABLE')

  at C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  > 671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675|

  1   C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
      PDOException::("SQLSTATE[HY000] [1049] Base 'laravel-ecommerce' inconnue")

  2   C:\Users\linda\lynda-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
      PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=laravel-ecommerce", "root", "", [])

score 3 · Answer 1 · edited Jul 21 '20 at 16:00

3

This issue might be occurring due to Mariadb or old version of MySQL.

Solution 1:

Edit AppServiceProvider.php file located at /app/Providers/AppServiceProvider.php and inside the boot method update the default string length:

// import builder where defaultStringLength method is defined
use Illuminate\Support\Facades\Schema;

public function boot()
{
    // Fix for MySQL < 5.7.7 and MariaDB < 10.2.2
    Schema::defaultStringLength(191); //Update defaultStringLength
}

Solution 2:

Change Mariadb to mysql, that can also fix the issue.

Solution 3:

Inside config/database.php, replace this line for mysql

'engine' => null',

with

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

Hope one of the solution works for you!!

edited Jul 21 '20 at 16:00

Marcin Orlowski

67,279
10
112
132

answered Jul 21 '20 at 13:04

Ankit Jindal

1,950
2
15
26

thanks for your reply but i got an error laravel when i do what you say like this : public function boot() { defaultStringLength; } – Hiyouri Jul 21 '20 at 13:24
So what worked for you? – Ankit Jindal Jul 21 '20 at 13:27
I have already changed to mariaDB but it gives me unknown database – Hiyouri Jul 21 '20 at 13:30
I solved the problem by changing the version of mysql I hope it will helps someone one day thank you very much – Hiyouri Jul 23 '20 at 19:05

laravel error 1071 key was too long php artisan migrate

1 Answers1