MySQL migrations: Multiple primary key defined when sql_generate_invisible_primary_key=ON

[PetrKaterinak]

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

#	column_name	data_type	...	is_nullable	extra
1	my_row_id	bigint unsigned	...	NO	auto_increment INVISIBLE
2	id	varchar(191)	...	NO
3	cart_data	longtext	...	NO
4	created_at	timestamp	...	YES
5	updated_at	timestamp	...	YES
6	deleted_at	timestamp	...	YES

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.

[crynobone]

Hi there,

Thanks for reporting the problem you are encountering, but it looks like this is a question which may be better suited for a support channel. We only use this issue tracker for reporting bugs with the library itself. If you have a question on how to use functionality provided by this repository you can try one of the following channels:

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC

However, this issue will not be locked and everyone is still free to discuss solutions to your problem!

Thanks.

[PetrKaterinak]

Sir, from my PoV it is bug report, not question, but well, as you wish. Thank you for reply.

[RTippin]

@crynobone I too am having the exact same issue as @PetrKaterinak . Our Azure MySQL is version v8.0.32 and laravel version is 10.26.2. Only main difference on my end is attempting to use a UUID as the PK column.

[ajnozari]

As an update this setting can be turned off in the Azure portal. Please see screenshot below, this is the appropriate fix as from what I can gather it's not a required variable for azure managed mysql servers.

[PetrKaterinak]

Hi, yes, it can be turned off, but it is not the solution. Laravel's ORM should be able to handle it.

[crynobone]

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[Sophist-UK]

IMO there are multiple "bugs" here:

1. According to the MySQL documentation the invisible primary key should only be generated when a table is created without a primary key. So either Laravel creates the table without a primary key and then tries to add a primary key (which should be considered a bug because it triggers this behaviour), or MySQL is creating one when Laravel creates a non-autoincrement primary key (which is a bug in MySQL).

   My guess is that this is Laravel creating a primary key as a separate operation. The fix would either be to create the primary index as part of the CREATE TABLE statement rather than adding it later, or to use a ALTER TABLE tablename DROP INDEX IF EXISTS 'PRIMARY'; statement before creating the new primary key.

   If it is a bug in MySQL, then it would be a judgement call whether Laravel should have a workaround or not - my vote would be not since you can turn off this setting in your instance of MySQL if you have issues.

2. IMO Azure should not have this set to on by default if it is not on by default in MySQL. It will not only be Laravel that has problems because of this.

[Sophist-UK]

An alternative solution would be for Laravel Migrations to query the value of sql_generate_invisible_primary_key, set it to off, do the migration and then set it back to its original value - on the basis that Laravel knows what it is doing and if it wants an index it will create it explicitly.

[arynaq]

Experiencing this as well, it is clearly a bug, laravel creates the table then alters it, so the effect of the auto gen primary kicks at creation point.

[mghaoui-interpulse]

What a spectacular mistake by whoever thought that sql_generate_invisible_primary_key was a good idea. Incredible.

[jdecode]

Wow - what an issue (and I faced the same one, got resolved thanks to this discussion)