Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35493

innodb_force_primary_key blocks temporary tables (without primary key) from being created

    XMLWordPrintable

Details

    Description

      There is a useful feature in InnoDB to ensure that created tables have a primary key:
      innodb_force_primary_key. This is useful, as tables without a primary key will possibly cause issues in galera clusters and with asynchronous replication there might be similar issues.

      When enabling this, CREATE TEMPORARY TABLE AS SELECT statements are no longer possible:

      MariaDB [dba]> create temporary table hello_ctas as select * FROM dba.hello;
      ERROR 1173 (42000): This table type requires a primary key
      

      creating temporary tables is also blocked:

      MariaDB [dba]> create temporary table hello (id int unsigned not null) ;
      ERROR 1173 (42000): This table type requires a primary key
      

      Even when I ask the server to create the temporary tables using MyISAM or ARIA:

      MariaDB [dba]> create temporary table hello (id int unsigned not null) engine=aria;
      ERROR 1173 (42000): This table type requires a primary key
      MariaDB [dba]> create temporary table hello (id int unsigned not null) engine=MyISAM;
      ERROR 1173 (42000): This table type requires a primary key
      

      It does not make sense to enforce this when row based replication is enabled as this means temporary tables are not replicated. Enforcing a primary key to an explicit temporary table in that case has as far as I know no benefits for galera or for asynchronous replication.

      Please change the effect of the setting to not apply to temporary tables when binlog_format=ROW.

      Attachments

        Activity

          People

            Unassigned Unassigned
            michaeldg Michaël de groot
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.