MySQL migrations: Multiple primary key defined when sql_generate_invisible_primary_key=ON #48503

Laravel Version

10.19.0

PHP Version

8.2.8

Database Driver & Version

MySQL 8.0.31 (Azure Database for MySQL)

Description

Azure Database for MySQL has the 'sql_generate_invisible_primary_key' option set to ON by default.

This issue affects all MySQL >= 8.0.30 where sql_generate_invisible_primary_key is set to ON.

When migrating table with non-standard PK (standard in my case is unsigned numeric with auto_increment), unsigned bigint autoincrement invisible column named my_row_id is automatically generated and migration crashes when trying to create second primary key on column specified in migration itself.

Steps To Reproduce

Schema::create('cart_storage', static function (Blueprint $table) {
    $table->string('id')->primary();
    $table->longtext('cart_data');
    $table->timestamps();
    $table->softDeletes();
});

creates following table

and then it tries to set 'id' as PK which causes
Syntax error or access violation: 1068 Multiple primary key defined SQL: alter table cart_storage add primary key (id))

Further Analysis

When I tried to create table directly by SQL:

CREATE TABLE `cart_storage` (
  `id` varchar(191) NOT NULL,
  `cart_data` longtext NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

It worked as expected, so after little bit of investigation i found that problem must be inside Blueprint::toSql() and MysqlGrammar::compilePrimary(), in case of enabled sql_generate_invisible_primary_key there must be different method to compile primary key than ALTER one.