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

Moving InnoDB tables with fk constraints from 10.6 to main causes failures

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Blocker
    • Resolution: Unresolved
    • 11.5(EOL), 11.6(EOL), 11.7(EOL), 11.8, 12.3, 13.0
    • None
    • Server, Upgrades
    • Related to install and upgrade
    • Q3/2026 Server Maintenance

    Description

      This is both as server issue and a documentation issue.

      When moving tables with foreign key definitions from 10.6 to 'main' one can get the error
      (errno: 150 "Foreign key constraint is incorrectly formed")

      In latest 10.6, create a new data directory, start the server and execute:
      CREATE TABLE t1(
      id SERIAL,
      msg VARCHAR(100) CHARACTER SET utf8mb3,
      KEY(msg))ENGINE=InnoDB;
      insert into t1 values(1,"a"),(2,"b");

      CREATE TABLE t2(
      id SERIAL,
      msg varchar(100) CHARACTER SET utf8mb3,
      msg_1 varchar(100) CHARACTER SET utf8mb3,
      INDEX (msg_1),
      INDEX (msg),
      CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
      ON DELETE CASCADE)ENGINE=InnoDB;

      insert into t2 values(1,"a","aa"),(2,"b","bb");

      Stop the 10.6 server and start a server from main on the same directory.
      run mariadb_updgrade

      inserts in t1 and t2 works as expected but:

      CREATE TABLE t2(
      id SERIAL,
      msg varchar(100) CHARACTER SET utf8mb3,
      msg_1 varchar(100) CHARACTER SET utf8mb3,
      INDEX (msg_1),
      INDEX (msg),
      CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
      ON DELETE CASCADE)ENGINE=InnoDB;

      Gives the error:
      ERROR 1005 (HY000): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")

      Same error for:

      CREATE TABLE t3(
      id SERIAL,
      msg varchar(100) CHARACTER SET utf8mb3,
      msg_1 varchar(100) CHARACTER SET utf8mb3,
      INDEX (msg_1),
      INDEX (msg),
      CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
      ON DELETE CASCADE)ENGINE=InnoDB;

      Changing the foreign key name to use fk_t2 fixes the issue

      If tables are created in main then this problem does not exists

      This issue is not documented in the documentation.
      Note that when this happens, there is no error in the error log!

      At least
      https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1800-to-1899/e1834?q=%22Foreign+key+constraint+is+incorrectly+formed
      and
      https://mariadb.com/docs/server/architecture/server-constraints/foreign-key-constraints
      should be updated.

      The later entry has a "special cases" section which is not up to date with how newer versions of InnoDB foreign key are stored (there is no ibfk anymore).

      It would be even better if mariadb_upgrade would fix the table to use the new format without ibfk. Another change would be to move to the new format when a ddl change is detected

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.