How do I alter a table in Laravel 8.x?

Question

I'm rather new to Laravel; I dabbled with it a bit in the spring but now I'm back and trying to understand it better, particularly how Laravel and Vue work together. In that spirit, I'm trying to do this [tutorial][1] but I'm having trouble with my php artisan migrate in Step 4.

This is the error I'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `failed_jobs` add unique `failed_jobs_uuid_unique`(`uuid`))

I think this is a big clue that I need to change the definition of the failed_jobs table to include an additional unique index on the uuid column; they want me to call the index failed_jobs_uuid_unique. The problem is that I don't know who to write that. I can't find an example anywhere, aside from one [here][2]. I've gone into 2019_08_19_000000_create_failed_jobs_table.php and tried to imitate the example for my circumstances. I came up with this as my revised up() method:

public function up()
{
    Schema::create('failed_jobs', function (Blueprint $table) {
        $table->id();
        $table->string('uuid')->unique();
        $table->text('connection');
        $table->text('queue');
        $table->longText('payload');
        $table->longText('exception');
        $table->timestamp('failed_at')->useCurrent();
    });

    Schema::table('failed_jobs', function (Blueprint $table) {
        DB::statement('ALTER TABLE `failed_jobs` add unique `failed_jobs_uuid_unique`(`uuid`)');
    });
}

I've just added the second statement and left the first statement alone. This is the output I get:

    php artisan migrate
    Migration table created successfully.
    Migrating: 2014_10_12_000000_create_users_table
    Migrated:  2014_10_12_000000_create_users_table (492.69ms)
    Migrating: 2014_10_12_100000_create_password_resets_table
    Migrated:  2014_10_12_100000_create_password_resets_table (647.70ms)
    Migrating: 2019_08_19_000000_create_failed_jobs_table

   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 `failed_jobs` add unique `failed_jobs_uuid_unique`(`uuid`))

    at C:\Laravel\blog\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:\Laravel\blog\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes")

  2   C:\Laravel\blog\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOStatement::execute()

In other words, I'm getting exactly the same error as before. What do I need to do differently to solve this problem? I assume I've just mangled the syntax of the alter statement somehow but I'm really not sure what I've done wrong.

Would it be better to modify the original statement in the up() method to make the table include the additional index? The uuid column already has a unique index on it but maybe it's important that the name of the index is failed_jobs_uuid_unique; if that's the case, how would the original statement change?

  [1]: https://www.tutsmake.com/larave-vue-js-spa-crud-example-tutorial/
  [2]: https://stackoverflow.com/questions/44200696/execute-multiple-laravel-alter-table-migration-queries-in-one

What version is your MySQL? Also the tutorial you linked has nothing to do with the failed jobs table. My failed jobs migration has no UUID column. — mikeroq, Nov 29 '20 at 23:30
You have already set field to be unique in process of creating table `$table->string('uuid')->unique()`. No need for second part. — Tpojka, Nov 29 '20 at 23:33
@mikeroq - My MySQL Server version: 10.1.38-MariaDB - mariadb.org binary distribution. Yes, the tutorial isn't really relevant, I just mentioned it to give context as to how I got to where I am: php artisan migrate is blowing up. — Henry, Nov 29 '20 at 23:41
@tpojka - I see the unique index on uuid but when I ran the up() method unaltered, it didn't work. I assumed the error message was telling me that I needed to alter the table before php artisan migrate would be happy. I can't get that suggestion to work though. — Henry, Nov 29 '20 at 23:43
@Martin Zeitler - I think my fundamental problem is that I took the last half of the error message seriously as offering a good answer to the problem. I can't see why adding another unique index on the same column could help. — Henry, Nov 30 '20 at 01:08
@Martin Zeitler - I think the link in your second answer is the right answer. It reminded me that I'd seen that problem before and somehow learned that the solution was to update app/Providers/AppServiceProvider.php to include the line: \Illuminate\Support\Facades\Schema::defaultStringLength(191); before the methods. After I did that, my migrate worked fine, presumably for the reasons enumerated in the linked article. If you post that as an answer, I'll happily give you the points for it. — Henry, Nov 30 '20 at 01:12

Tpojka · Answer 1 · 2020-11-29T23:50:14.027

Try this way:

public function up()
{
    Schema::create('failed_jobs', function (Blueprint $table) {
        $table->id();
        $table->string('uuid');// remove constraint from here
        $table->text('connection');
        $table->text('queue');
        $table->longText('payload');
        $table->longText('exception');
        $table->timestamp('failed_at')->useCurrent();
    });

    Schema::table('failed_jobs', function (Blueprint $table) {
        $table->unique(['uuid'], 'failed_jobs_uuid_unique');// setting custom name for key
    });
}

public function down()
{
    Schema::disableForeignKeyConstraints();
    Schema::table('failed_jobs', function(Blueprint $table) {
        $table->dropUnique('failed_jobs_uuid_unique');
    });
    Schema::drop('failed_jobs');
    Schema::enableForeignKeyConstraints();
}

Docs. Code.

If you still getting specified key is too long error message, change column type from string to text. But also, there is uuid field type so you can also check it on migration page I linked above.

How do I alter a table in Laravel 8.x?

1 Answers1