Laravel 5.6: Table creation failed

Question

I'm new to Laravel and I'm trying to create tables using Schema façade. I create the migration file with command

php artisan make:migration create_products_define_standards_table --create=products_define_standards

Here's the file:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateStandardsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products_define_standards', function (Blueprint $table) {
            $table->increments('id');
            $table->string('code')->unique();
            $table->string('image');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products_define_standards');
    }
}

It really has almost the same content of the default CreateUsersTable, but when I run php artisan migrate it creates:

users' table (default)
migrations' table (default)

but not:

password_resets' table (default)
products_define_standards' table (custom)

I tried with php artisan migrate:fresh but I get the same log:

Dropped all tables successfully.
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 `users` add unique `users_email_unique`(`email`))

  at /home/whatever/whatever.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php: 664
  660:         // If an exception occurs when attempting to run a query, we'll format the error
  661:         // message to include the bindings with SQL, which will make this exception a
  662:         // lot more helpful to the developer instead of just the database's errors.
  663:         catch (Exception $e) {
  664:             throw new QueryException(
  665:                 $query, $this->prepareBindings($bindings), $e
  666:             );
  667:         }
  668: 
  669:         return $result;

  Exception trace:

  1   PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")
      /home/whatever/whatever.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php : 458

  2   PDOStatement::execute()
      /home/whatever/whatever.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php : 458

I found this answer and I run also composer dumpauto but the result is the same.

Am I missing something? Should I do any other thing to register the migration somewhere else?

score 11 · Accepted Answer · answered Feb 12 '18 at 10:52

11

Edit your AppServiceProvider.php located in app/Providers directory, and inside the boot() method set a default string length.

use Illuminate\Support\Facades\Schema;

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

answered Feb 12 '18 at 10:52

Sapnesh Naik

11,011
7
63
98

Great Sapnesh, all the migrations were perfectly run now. So the problem was that Laravel doesn't provide a default string's lenght for `VARCHAR` and MySQL thrown the error? – Brigo Feb 12 '18 at 10:59
1

@Brigo Yes, something like that. But if you want a thorough explanation see this question https://stackoverflow.com/questions/43832166/laravel-5-4-specified-key-was-too-long-why-the-number-191 – Sapnesh Naik Feb 12 '18 at 11:02
@Brigo If it helped you, please accept the answer :) – Sapnesh Naik Feb 12 '18 at 11:09
1

Sure, I was waiting for the "lock-time" to expire :) Thanks for the link to the other answer also – Brigo Feb 12 '18 at 11:12

score 1 · Answer 2 · edited Jun 06 '18 at 12:20

In version 5.6 you should edit 2 files :

First

Edit your AppServiceProvider.php located in app/Providers directory, and inside the boot() method set a default string length.

use Illuminate\Support\Facades\Schema;

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

Second

Edit your database.php located in config/database.php directory

in mysql configuration section 'engine'is null and you should replace with 'InnoDB ROW_FORMAT=DYNAMIC'

hope you enjoy.

score 0 · Answer 3 · answered Jul 24 '18 at 13:25

0

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

$table->string('code')->unique(null,'unikcode');

answered Jul 24 '18 at 13:25

Tiago Gouvêa

15,036
4
75
81

score 0 · Answer 4 · answered Sep 06 '18 at 23:07

I would like to propose a potential better solution to this problem.

You don't have to edit any files that are part of your Laravel. Instead, edit your actual database collation and engine.

I presume you're using either MySQL or MariaDB. Use phpMyAdmin, when you create your blank database, use utf8mb4_unicode_ci (utf8_unicode_ci or utf8P_general_ci may also work fine).

Next set your default database engine to InnoDB instead MyISAM (you can do this also in phpMyAdmin under the "Variables" tab, search for "engine".

The other solutions that people have proposed - i.e. editing database.php to make it use InnoDB anyways, have a very similar effect. But modern versions of MySQL/Maria should be using InnoDB out of the box anyways.

Run your migration and it would execute fine with no further modifications.

Laravel 5.6: Table creation failed

4 Answers4