MySQL Bugs: #96690: sql_require_primary_key should not apply to temporary tables

Bug #96690	sql_require_primary_key should not apply to temporary tables
Submitted:	29 Aug 2019 8:50	Modified:	29 Aug 2019 13:08
Reporter:	Przemyslaw Malkowski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	primary key

Description:
The main purpose of sql_require_primary_key is, as mentioned in the documentation, to avoid replication performance issues: "Enabling this variable helps avoid performance problems in row-based replication that can occur when tables have no primary key."

In the same time:
"sql_require_primary_key applies to both base tables and TEMPORARY tables, and changes to its value are replicated to slave servers."

Extending this requirement to temporary tables does not seem to make much sense, as quoting (https://dev.mysql.com/doc/refman/8.0/en/replication-features-temptables.html):
"when binlog_format is set to ROW or MIXED, statements that exclusively use temporary tables are not logged on the master, and therefore the temporary tables are not replicated. Statements that involve a mix of temporary and nontemporary tables are logged on the master only for the operations on nontemporary tables, and the operations on temporary tables are not logged. This means that there are never any temporary tables on the slave to be lost in the event of an unplanned shutdown by the slave. "

So, the main reason for the PK restriction simply does not apply to temporary tables. And by having this restriction, we no longer can do CREATE TEMPORARY TABLE ... as SELECT ... FROM ... without disabling this variable per-session. And we often simply don't want to give that privilege for changing it to the users.

How to repeat:
mysql > create temporary table tt1 as select * from test1;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Suggested fix:
Make the sql_require_primary_key variable either not apply to temporary tables or make it configurable, maybe add more options in addition to current "ON/OFF".

Hello Mr. Malkowski,

Thank you for your bug report.

I find that your report is actually asking for a new feature, so I am verifying this report as a valid feature request.

Could we know what was the original severity of this bug as reported by Przemyslaw ?  This is now hidden as it has been reclassified as S4 (Feature Request).

From my point of view, this is actually a bug, not a feature request and it should be classified as S2.  A perfectly working application would break for no reason when a temporary table does not have a Primary Key, so this is actually a big hurdle for using sql_require_primary_key, hence serious bug in the implementation of this otherwise very nice and useful feature.

I would also like to add that I think that the behaviour of sql_require_primary_key when using temporary tables is undesirable, at least when using RBR as the documentation clearly states that this will not replicate and the intention of this variable was to prevent issues with replication.

The use of temporary tables with RBR by definition will NEVER replicate anything as defined in the MySQL documentation: See "4.1.30 Replication and Temporary Tables" in the manual and the first paragraph.

Note: this required some changes in 8.0 as I reported an issue with automatic upgrades where changes to performance_schema tables triggered a failure to upgrade the server.

I think that the use of temporary tables when using RBR should be excluded from the sql_require_primary_key check given they will never be replicated as adding an extra configuration setting is more complicated to configure and this exclusion has been done already for performance_schema tables for similar reasons.

Thank you , Mr. Mudd, for your significant contribution.

I don't think this should be done. All tables should have PK's, including temp tables.

The problem is that CREATE TEMPORARY TABLE t1 SELECT * FROM t2 creates a table w/o pk. Either there should be a change there to automatically add a PK. 

However CREATE...SELECT... is a not a good practice anyway as this won't work in other cases with GTID and is mixing DDL with DML.

Thank you, Daniel.

I agree with you fully.

[reposting as i accidentally set last comment to private]
I agree with simon here, and I'm a notorious best-practice enforcer - I
just don't think this battle is worth fighting as it'd require an
immense amount of work industry-wide, a huge cost in labor, for basically no practical benefit, just to make us feel better.
Not naming names, but if you look at other vendor's mysql forks, you'll
see this is fixed - no new option, just excludes them.

Again, I agree with Daniel on this one.

There are too many installations and applications where PK is simply forgotten about.

Let us also not forget that, in case of InnoDB, if you do not create a primary key, a hidden one will be created instead, which will waste resources, but can not be used by the Optimiser.