MySQL custom primary key creation still broken in 8.48.1

[Synchro]

• Laravel Version: 8.48.1
• PHP Version: 8.0.7
• Database Driver & Version: MySQL (Percona server) 8.0.23-14

Description:

Some old migrations have suddenly broken with a recent Laravel update.

Steps To Reproduce:

Create a new Laravel project:

composer create-project laravel/laravel laravel-example

Add this migration:

<?php

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

class CreateSystemTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up(): void
    {
        Schema::create(
            'system',
            static function (Blueprint $table) {
                $table->string('key')
                    ->charset('ascii')
                    ->collation('ascii_bin')
                    ->primary();
                $table->string('value');
            }
        );
    }

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

run php artisan migrate.

It fails with this error:

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (19.89ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (13.41ms)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (16.19ms)
Migrating: 2020_02_09_111130_create_system_table

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'character set ascii collate 'ascii_bin' not null, `value` varchar(255) not null)' at line 1 (SQL: create table `system` (`key` varchar(255) primary key character set ascii collate 'ascii_bin' not null, `value` varchar(255) not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:692
    688▕         // If an exception occurs when attempting to run a query, we'll format the error
    689▕         // message to include the bindings with SQL, which will make this exception a
    690▕         // lot more helpful to the developer instead of just the database's errors.
    691▕         catch (Exception $e) {
  ➜ 692▕             throw new QueryException(
    693▕                 $query, $this->prepareBindings($bindings), $e
    694▕             );
    695▕         }
    696▕

      +9 vendor frames
  10  database/migrations/2020_02_09_111130_create_system_table.php:25
      Illuminate\Support\Facades\Facade::__callStatic("create")

      +21 vendor frames
  32  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

The SQL it generates is incorrect:

create table `system` (`key` varchar(255) primary key character set ascii collate 'ascii_bin' not null, `value` varchar(255) not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'

I think the problem is that the clauses are in the wrong order, in particular the position of the primary key phrase. This query works fine:

create table `system` (`key` varchar(255) character set ascii collate 'ascii_bin' primary key not null, `value` varchar(255) not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'

This issue seems to have introduced after Laravel 8.47, presumably in 8.48 or 8.48.1.

[Synchro]

Hm. I just saw this issue – but I am running 8.48.1 which supposedly contains the fix.

I just double-checked this and I'm definitely getting this on a newly-generated project with 8.48.1. From my composer.lock:

        "name": "laravel/framework",
        "version": "v8.48.1",

[Synchro]

From the looks of the code in ca23daf, I guess Primary needs to go after Collate, though this ordering doesn't seem to agree with MySQL docs. But I've tried it and it works  🤷

[morloderex]

@Synchro Could it be that there is a difference between mysql 5.x and 8.x? Just throwing an idea out there..

[Synchro]

You can compare the docs on 5.7 and 8.0. 8.0 has more elements, but the ones that are in common are at least in the same order. That said, both of them show primary key before collate, yet that order evidently doesn't work.