Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.1
-
Fix Version/s: 10.1.7
-
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
-
Labels:None
Description
The option only forces a key (any key) on not nullable columns. As I understand from MDEV-5335, it was not the idea.
MariaDB [test]> set global innodb_force_primary_key = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> create table t1 (i int not null);
ERROR 1173 (42000): This table type requires a primary key
MariaDB [test]> # works
MariaDB [test]> create table t1 (i int not null, key(i));
Query OK, 0 rows affected (0.61 sec)
MariaDB [test]> # but it's not a primary key or unique key
MariaDB [test]> insert into t1 values (1),(1);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) NOT NULL,
KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-5335 Force PK option
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Incorrect fix (hides the real bug). Real bug in sql_table.cc:
if (!(key_info->flags & HA_NULL_PART_KEY)) unique_key=1;
should be, of course
if (key->type == Key::UNIQUE && !(key_info->flags & HA_NULL_PART_KEY)) unique_key=1;
Perfect, thanks!
(good that it produces the same effect as your first fix, that is, that test cases didn't need any changes)
commit 46ad86f6a35aff59ecb68a23dba228e5796e935a
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Sat Aug 8 12:49:20 2015 +0300
MDEV-8582: innodb_force_primary_key option does not force PK or unique key
Analysis: Handler used table flag HA_REQUIRE_PRIMARY_KEY but a bug on
sql_table.cc function mysql_prepare_create_table internally marked
secondary key with NOT NULL colums as unique key and did not then
fail on requirement that table should have primary key or unique key.
commit 3307eaab68bf4818116a4fdd10b1a4f3427ea2ae
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Sat Aug 8 10:39:01 2015 +0300
MDEV-8582: innodb_force_primary_key option does not force PK or unique keyAnalysis: Handler table flag HA_REQUIRE_PRIMARY_KEY alone is not enough
to force primary or unique key, if table has at least one NOT NULL
column and secondary key for that column.
Fix: Add additional check that table really has primary key or
unique key for InnoDB terms.