Details

    • Type: Task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Fix Version/s: 10.1.0
    • Component/s: None
    • Labels:
      None

      Description

      Have an option to force PK for (innodb) tables.

      Forcing is quite easy (a poc from 2011):
      http://linsenraum.de/erkules/2011/03/this-table-type-requires-a-primary-key.html
      Why?
      RBR and so Galera would benefit from such an option.

      From my point of view a lot of DBA's would love it

        Issue Links

          Activity

          Hide
          jb-boin Jean Weisbuch added a comment -

          Another possible issue that could be faced when using tables without a PK on a TokuDB table : if you use UNCOMMITED-READ isolation level, you can hit a HA_ERROR_CRASHED error during certain operations : http://www.tokutek.com/2013/11/what-does-the-incorrect-key-file-for-table-error-mean/

          Show
          jb-boin Jean Weisbuch added a comment - Another possible issue that could be faced when using tables without a PK on a TokuDB table : if you use UNCOMMITED-READ isolation level, you can hit a HA_ERROR_CRASHED error during certain operations : http://www.tokutek.com/2013/11/what-does-the-incorrect-key-file-for-table-error-mean/
          Hide
          gokhan Gokhan Demir added a comment - - edited

          Although I like the idea very much, some of the existing database deployments using InnoDB will be affected badly. I know at least one ERP company with huge number of tables in its multi-platform database, no single table has pk, but each of them have unique indexes that consists of not-null composite columns that are chosen by the InnoDB as the implicit pk. Since innoDB keeps the table sorted according to the primary key, and also since the columns of the primary keys are added to the columns of the secondary indexes, we have marked one of the normally not null columns to accept null values, that way forcing the addition of the documented 6-byte invisible pk column. That saved us and InnoDB is now handling successfully that bad database design.

          Therefore, I am against this idea.

          Show
          gokhan Gokhan Demir added a comment - - edited Although I like the idea very much, some of the existing database deployments using InnoDB will be affected badly. I know at least one ERP company with huge number of tables in its multi-platform database, no single table has pk, but each of them have unique indexes that consists of not-null composite columns that are chosen by the InnoDB as the implicit pk. Since innoDB keeps the table sorted according to the primary key, and also since the columns of the primary keys are added to the columns of the secondary indexes, we have marked one of the normally not null columns to accept null values, that way forcing the addition of the documented 6-byte invisible pk column. That saved us and InnoDB is now handling successfully that bad database design. Therefore, I am against this idea.
          Hide
          jplindst Jan Lindström added a comment -

          Forcing primary key will affect only new tables not the old ones. But, I would like to have an option e.g. innodb_force_pk with default ON, but option to set it dynamically OFF and with that option to create tables without primary key.

          Show
          jplindst Jan Lindström added a comment - Forcing primary key will affect only new tables not the old ones. But, I would like to have an option e.g. innodb_force_pk with default ON, but option to set it dynamically OFF and with that option to create tables without primary key.
          Hide
          serg Sergei Golubchik added a comment -

          Gokhan Demir of course, the task is about adding an option, so that users could enable it if necessary. We wouldn't think about imposing this "only PK" limitation on all users and all applications.

          Show
          serg Sergei Golubchik added a comment - Gokhan Demir of course, the task is about adding an option , so that users could enable it if necessary. We wouldn't think about imposing this "only PK" limitation on all users and all applications.
          Hide
          gokhan Gokhan Demir added a comment -

          Thanks for the clarification. +1 from me!

          Show
          gokhan Gokhan Demir added a comment - Thanks for the clarification. +1 from me!
          Hide
          jplindst Jan Lindström added a comment -

          Hi,

          In this description tables created without primary key should be disabled, however in link also unique keys are mentioned. Is the idea to disable create table without primary key or should table without primary key but with unique key accepted ?

          R: Jan

          Show
          jplindst Jan Lindström added a comment - Hi, In this description tables created without primary key should be disabled, however in link also unique keys are mentioned. Is the idea to disable create table without primary key or should table without primary key but with unique key accepted ? R: Jan
          Hide
          serg Sergei Golubchik added a comment -

          unique index with nullable columns is not good enough, the table with such an index but without a primary key should not be accepted. unique index with NOT NULL columns is as good a a primary key.

          Anyway, this task is about adding HA_REQUIRE_PRIMARY_KEY flag, there's no need to do anything beyond that (and tests, of course).

          Show
          serg Sergei Golubchik added a comment - unique index with nullable columns is not good enough, the table with such an index but without a primary key should not be accepted. unique index with NOT NULL columns is as good a a primary key. Anyway, this task is about adding HA_REQUIRE_PRIMARY_KEY flag, there's no need to do anything beyond that (and tests, of course).
          Hide
          jplindst Jan Lindström added a comment -

          revno: 3984
          committer: Jan Lindström <jplindst@mariadb.org>
          branch nick: 10.1
          timestamp: Tue 2014-03-11 13:49:52 +0200
          message:
          Added multi-key unique test case.
          ------------------------------------------------------------
          revno: 3983
          committer: Jan Lindström <jplindst@mariadb.org>
          branch nick: 10.1
          timestamp: Tue 2014-03-11 13:40:29 +0200
          message:
          MDEV-5335: Force PK option. Added a new dynamic configuration variable
          innodb_force_primary_key default off. If option is true, create table without
          primary key or unique key where all keyparts are NOT NULL is not
          accepted. Instead an error message is printed. Variable value can
          be changed with set global innodb_force_primary_key = <value>.

          Setting option on by default is not a option, too many existing tests would fail.

          Show
          jplindst Jan Lindström added a comment - revno: 3984 committer: Jan Lindström <jplindst@mariadb.org> branch nick: 10.1 timestamp: Tue 2014-03-11 13:49:52 +0200 message: Added multi-key unique test case. ------------------------------------------------------------ revno: 3983 committer: Jan Lindström <jplindst@mariadb.org> branch nick: 10.1 timestamp: Tue 2014-03-11 13:40:29 +0200 message: MDEV-5335 : Force PK option. Added a new dynamic configuration variable innodb_force_primary_key default off. If option is true, create table without primary key or unique key where all keyparts are NOT NULL is not accepted. Instead an error message is printed. Variable value can be changed with set global innodb_force_primary_key = <value>. Setting option on by default is not a option, too many existing tests would fail.

            People

            • Assignee:
              jplindst Jan Lindström
              Reporter:
              erkules erkan yanar
            • Votes:
              6 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 4 hours
                4h