[MDEV-8582] innodb_force_primary_key option does not force PK or unique key Created: 2015-08-07  Updated: 2015-08-08  Resolved: 2015-08-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1
Fix Version/s: 10.1.7

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5335 Force PK option Closed

 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)



 Comments   
Comment by Jan Lindström (Inactive) [ 2015-08-08 ]

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 key

Analysis: 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.

Comment by Jan Lindström (Inactive) [ 2015-08-08 ]

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;

Comment by Jan Lindström (Inactive) [ 2015-08-08 ]

http://lists.askmonty.org/pipermail/commits/2015-August/008245.html

Comment by Sergei Golubchik [ 2015-08-08 ]

Perfect, thanks!
(good that it produces the same effect as your first fix, that is, that test cases didn't need any changes)

Comment by Jan Lindström (Inactive) [ 2015-08-08 ]

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.

Generated at Thu Feb 08 07:28:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.