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:
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: primary key

[29 Aug 2019 8:50] Przemyslaw Malkowski
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".
[29 Aug 2019 13:08] Sinisa Milivojevic
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.
[30 Aug 2019 8:08] Jean-François Gagné
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.