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

          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