MySQL Bugs: #111581: "Multiple primary key defined" when sql_generate_invisible_primary

Bug #111581	"Multiple primary key defined" when sql_generate_invisible_primary_key=ON
Submitted:	27 Jun 2023 11:07	Modified:	3 Jul 2023 9:13
Reporter:	Tinel Barb	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Multiple primary key defined, replication fail, sql_generate_invisible_primary_key

Description:
Having a number of big tables without PK and facing often replication failures, I tried to benefit from sql_generate_invisible_primary_key=ON, both on Source and Replicas, and even restarted replication with "REQUIRE_TABLE_PRIMARY_KEY_CHECK=GENERATE". With these, I expected to face less replication failures.

Despite the fact that the documentation states that:
"Whether this server adds a generated invisible primary key to any InnoDB table that is created without one." (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_generate_i...), MySQL Server:

[A] Doesn't silently drop the invisible PK when an "alter table .... add primary key" comes, resulting in "Multiple primary key defined" on query and error 1068 on replicas, aswell. This issue appears also with temporary tables when the PK is added later, after creation of the table.

[B] Is trying to create the invisible PK even if another already exists.

How to repeat:
For the [A] situation:
mysql> create temporary table tmptable1 select * from another_table;
-- OK
mysql> alter table tmptable1 add primary key (`IDPersoane`);
-- ERROR: Multiple primary key defined, ErrorNo 1068

mysql> show create table tmptable1;
# `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
# `IDPersoane` int unsigned NOT NULL DEFAULT '0',
# ....
# PRIMARY KEY (`my_row_id`)

The "joker" is the automated added invisible row `my_row_id` with automated PK set on it, WHICH IS ALRIGHT, but MySQL should silently drop its own PRIMARY KEY (`my_row_id`) allowing user to set-up own PK.

This behavior also cause a (false) compatibility issue with stored procedures working fine on previous version of MySQL (5.5, 5.7) and even on MySQL 8.0, but having a crash because the newly introduced and activated sql_generate_invisible_primary_key=ON.

Suggested fix:
if sql_generate_invisible_primary_key=ON and command "alter ... add primary key" is issued:
Silently drop the invisible row and associated PK prior executing command.

# for SQL_Thread:
if !RBR and REQUIRE_TABLE_PRIMARY_KEY_CHECK=GENERATE and command "alter ... add primary key" is issued:
Silently drop the invisible row and associated PK prior executing command.

Hi Mr. Barb,

Thank you for your bug report.

However, what you are attempting is not supported.

Luckily, our Manual contains the info on how to solve a problem.

After you have created tmptable1, you change locally (just for that session) a value for sql_generate_invisible_primary_key to OFF, drop the column that is a primary key and add the primary key of your choice.

Then you can revert that system variable to ON, if you wish.

Your request for the change of behaviour with that system variable being at ON is not possible as it would break too many applications.

Not a bug.

Hi,

This is indeed an expected behaviour.

If you already have a primary key, then adding another one is a hard mistake. A table itself does not contain info on how was it created.

You have a got a workaround that is very easy to implement, since "DROP PRIMARY KEY" is a very simple command.

MySQL can not do "silent" dropping of any keys, since that would contradict the valid SQL standard.

You are truly welcome.