Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5931

sql_require_primary_key should not apply to temporary tables

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.16-7, Not 5.6.x, Not 5.7.x
    • Fix Version/s: 8.0.17-8
    • Component/s: None
    • Labels:

      Description

      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".

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  zsolt.parragi Zsolt Parragi
                  Reporter:
                  sveta.smirnova Sveta Smirnova
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 3 hours
                    3h