Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
None
-
None
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.