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

CREATE OR REPLACE fails to recreate same constraint name

Details

    Description

      CREATE OR REPLACE failed with "Duplicate key on write or update" when it replace an empty table with the non-anonymous constraint name

      From comment
      Test fails due to a duplicate key on SYS_FOREIGN.NAME on the non-anonymous constraint name:

      Test:

      --source include/have_innodb.inc
       
      CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
      CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB;
      CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB;
      DROP TABLE u, t;
      

      Actual result:

      At line 5: query 'CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`u` (errno: 121 "Duplicate key on write or update")
      

      Expected result:
      No errors

      Attachments

        Issue Links

          Activity

            Please squash the fix into a single commit and make sure that the commit message accurately describes and provides justification for all changes.

            It is unclear to me why the size of fk_id[] would have to be reduced by 2 bytes instead of being extended by 2 bytes. It is also unclear why dict_remove_db_name() would have to be changed.

            One of the commit messages mentions table_name, even though SYS_FOREIGN.ID (which the commit message fails to mention) stores constraint names and not table names. An added comment to row_rename_table_for_mysql() in the same commit appears to disagree with the commit message.

            Because innodb_drop_database implements some "garbage collection" of any orphaned FOREIGN KEY constraints on DROP DATABASE, it is simplest that we will retain the databasename/ prefix for all SYS_FOREIGN.ID. Some \377 bytes can be added to the start or the end of the constraint name, e.g., databasename/\377constraintname, databasename/constraintname\377, or databasename/\377\377constraintname.

            marko Marko Mäkelä added a comment - Please squash the fix into a single commit and make sure that the commit message accurately describes and provides justification for all changes. It is unclear to me why the size of fk_id[] would have to be reduced by 2 bytes instead of being extended by 2 bytes. It is also unclear why dict_remove_db_name() would have to be changed. One of the commit messages mentions table_name , even though SYS_FOREIGN.ID (which the commit message fails to mention) stores constraint names and not table names. An added comment to row_rename_table_for_mysql() in the same commit appears to disagree with the commit message. Because innodb_drop_database implements some "garbage collection" of any orphaned FOREIGN KEY constraints on DROP DATABASE , it is simplest that we will retain the databasename/ prefix for all SYS_FOREIGN.ID . Some \377 bytes can be added to the start or the end of the constraint name, e.g., databasename/\377constraintname , databasename/constraintname\377 , or databasename/\377\377constraintname .

            Please review bb-10.10-MDEV-25292

            midenok Aleksey Midenkov added a comment - Please review bb-10.10-MDEV-25292

            bb-10.11-midenok-MDEV-25292 looks OK to both me and the CI systems. Thank you!

            marko Marko Mäkelä added a comment - bb-10.11-midenok- MDEV-25292 looks OK to both me and the CI systems. Thank you!

            reopened, to reflect the fact that the commit is not present in the tree

            serg Sergei Golubchik added a comment - reopened, to reflect the fact that the commit is not present in the tree

            It was fixed for MDEV-25292, it is not main bug.

            midenok Aleksey Midenkov added a comment - It was fixed for MDEV-25292 , it is not main bug.

            People

              midenok Aleksey Midenkov
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.