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

innodb_force_primary_key option does not force PK or unique key

Details

    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)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-5335 Force PK option

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            jplindst Jan Lindström (Inactive) added a comment -

            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.

            jplindst Jan Lindström (Inactive) added a comment - 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.
            jplindst Jan Lindström (Inactive) added a comment -

            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;

            jplindst Jan Lindström (Inactive) added a comment - 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;
            jplindst Jan Lindström (Inactive) added a comment -

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

            jplindst Jan Lindström (Inactive) added a comment - http://lists.askmonty.org/pipermail/commits/2015-August/008245.html
            serg Sergei Golubchik added a comment -

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

            serg Sergei Golubchik added a comment - Perfect, thanks! (good that it produces the same effect as your first fix, that is, that test cases didn't need any changes)
            jplindst Jan Lindström (Inactive) added a comment -

            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.

            jplindst Jan Lindström (Inactive) added a comment - 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.

            People

              jplindst Jan Lindström (Inactive)
              elenst Elena Stepanova
              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.