[MDEV-29092] FOREIGN_KEY_CHECKS does not prevent non-copy alter from creating invalid FK structure - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
- affects-tests

Description

Setting to minor because a foreign key on a column referencing itself doesn't make much sense. But possibly a more meaningful use case can be invented based on this.

For a foreign key to self-reference a column, the table should have two keys on the column, otherwise FK creation fails:

10.3 96be3fe8
MariaDB [test]> create table t (a int, key(a), foreign key(a) references t(a)) engine=InnoDB;
ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed")
MariaDB [test]> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
\| Level \| Code \| Message \|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
\| Warning \| 150 \| Create table `test`.`t` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'foreign key(a) references t(a)) engine=InnoDB'. \|
\| Error \| 1005 \| Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed") \|
\| Warning \| 1215 \| Cannot add foreign key constraint for `t` \|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.000 sec)

Instead it has to be done like this:

MariaDB [test]> create table t (a int, key(a), key(a), foreign key(a) references t(a)) engine=InnoDB;

Query OK, 0 rows affected, 1 warning (0.068 sec)

(the warning is about duplicate keys being deprecated, but it's not important in the context of this issue).

So, we get this structure:

MariaDB [test]> show create table t \G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int(11) DEFAULT NULL,

  KEY `a` (`a`),

  KEY `a_2` (`a`),

  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.001 sec)

Now we can drop the second key, as long as the algorithm is anything but COPY, the FOREIGN_KEY_CHECKS doesn't prevent it:

MariaDB [test]> select @@foreign_key_checks;

+----------------------+

| @@foreign_key_checks |

+----------------------+

|                    1 |

+----------------------+

1 row in set (0.000 sec)

MariaDB [test]> alter table t drop key a_2;

Query OK, 0 rows affected (0.029 sec)

Records: 0  Duplicates: 0  Warnings: 0

And we get this:

MariaDB [test]> show create table t \G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int(11) DEFAULT NULL,

  KEY `a` (`a`),

  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.000 sec)

As we already know, it is an invalid structure, it can't be used to re-create the table (not even with FOREIGN_KEY_CHECKS=0, so if it happens to be in a dump, it will be broken):

MariaDB [test]> drop table if exists t;

Query OK, 0 rows affected (0.033 sec)

MariaDB [test]> set foreign_key_checks=0;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> CREATE TABLE `t` (

    ->   `a` int(11) DEFAULT NULL,

    ->   KEY `a` (`a`),

    ->   CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

MariaDB [test]> show warnings;

+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                                                                                                                                                                                |

+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning |  150 | Create  table `test`.`t` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY (`a`) REFERENCES `t` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1'. |

| Error   | 1005 | Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                                                                                                              |

| Warning | 1215 | Cannot add foreign key constraint for `t`                                                                                                                                                                                                              |

+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.000 sec)

Attachments

Issue Links

relates to

MDEV-20480 Obsolete internal parser for FK in InnoDB

Closed

MDEV-34399 ERROR 1025 (HY000): Error on rename, errno: 150 "Foreign key constraint is incorrectly formed"

Confirmed

Activity

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2022-07-12 23:44

Updated:: 2024-06-15 02:12

Resolved:: 2023-12-06 11:23

Git Integration

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

MariaDB Server