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

        Gliffy Diagrams

          Attachments

            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