MySQL Bugs: #3491: alter table - wrong/incomplete error messages using foreign keys

Bug #3491	alter table - wrong/incomplete error messages using foreign keys
Submitted:	18 Apr 2004 11:12	Modified:	17 Jan 2014 10:08
Reporter:	Philip	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.18-standard	OS:	Any (i686 GNU/Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
it give the notorious error 150 not only when the detailed table index is missing but also when the field is missing(misspelled), referred table is missing or referred field is missing
Instead of giving a message like:
alter table detail add index uuu(mid1);
ERROR 1072: Key column 'mid1' doesn't exist in table
or ERROR 1146: Table 'dbtest.detail1' doesn't exist

when using creater table it is just silently ignored

How to repeat:

drop table if exists detail,master cascade;
create table master(mid int primary key) type=InnoDB;
create table detail(mid int,no int,primary key (mid,no)) type=InnoDB;

##detail.mid is already indexed
alter table detail add foreign key fk(mid) references master(mid); - works fine
here is an example how to reproduce the behavior:
mysql> alter table detail add foreign key fk(mid1) references master1(mid1);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)
mysql> alter table detail add foreign key fk(mid) references master1(mid1);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)
mysql> alter table detail add foreign key fk(mid) references master(mid1);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)
mysql> alter table detail add foreign key fk(mid1) references master1(mid);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)
mysql> alter table detail add foreign key fk(mid) references master1(mid);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)
mysql> alter table detail add foreign key fk(mid1) references master(mid);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)

drop table if exists detail,master cascade;
create table master(mid int primary key) type=InnoDB;
create table detail(mid int references master1(mid),no int,primary key (mid,no)) type=InnoDB;
show create table detail;
---------------------
detail | CREATE TABLE `detail` (
  `mid` int(11) NOT NULL default '0',
  `no` int(11) NOT NULL default '0',
  PRIMARY KEY  (`mid`,`no`)

Suggested fix:
syntax check for existance before passing hte query to the innodb engine or tewaking innnodb engine

This error message is also displayed when creating a foreign key on a field which does not already have an index. To correct the problem, create an index on the foreign key field and then create the foreign key constraint.

That one is well known issue. I personally don’t see why it is mandatory to create an index on the dependant table but as all the databases do it, it seams OK.
Wrong field or table names must be cached even before the statement is sent to the InnoDB engine…

Verified against latest 4.0 BK source.

Hi!

MySQL-4.1.2 automatically creates an index on the FOREIGN KEY if there is not a proper index specified by the user. That will remove most of those mystical errors no. 150.

But 4.1.2 will not automatically create an index on the REFERENCED key. Since the referenced key is usually the primary key of the parent table, that is not that big a problem.

Marko will also look into adding better MySQL error message numbers to 4.1.2. MySQL error message numbers of 4.0 are frozen, we cannot add new ones there. But to 4.1 we can.

Best regards,

Heikki

I use version 3.23.54 on solaris.
Creating index before foreign key doesn't work if I want to use multiple fields as a foreign key.

More precisely:
Creating the foreign key in the mentioned case fails, when the reference option ON UPDATE SET NULL is choosen.

Sorry, the wrong case is ON DELETE SET NULL.

Hi!

I am changing this bug report to a feature request:

"MySQL should return more descriptive FOREIGN KEY errors"

Of course, you can look at a very detailed error text with SHOW INNODB STATUS.

About Robert's problem: are you sure that ON DELETE SET NULL is legal? That is, are NULL values allowed in the child table?

Regards,

Heikki

The problem remains even if:
1). Indices exist in both tables.
2). Using single field foreign key.
3). Both tables are InnoDB.
4). mySQL version 3.23.58

Check the command sequence:

mysql> desc CATEGORY;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| CATEGORY_ID | int(10)      |      | PRI | 0       |       |
| NAME        | varchar(255) |      |     |         |       |
| PARENT_ID   | int(10)      | YES  | MUL | NULL    |       |
| IMG_URL     | varchar(255) | YES  |     | NULL    |       |
| VISIBLE     | int(5)       |      |     | 0       |       |
| TREE_SORT   | int(10)      | YES  |     | NULL    |       |
| LEVEL       | int(10)      | YES  |     | NULL    |       |
| HANDLER_URL | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc TD_CATEGORY;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| TD_CATEGORY_ID | int(10)      |      | PRI | 0       |       |
| NAME           | varchar(255) |      | UNI |         |       |
| CATEGORY_ID    | int(10)      | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SHOW INDEX FROM CATEGORY;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| CATEGORY |          0 | PRIMARY     |            1 | CATEGORY_ID | A         |          30 |     NULL | NULL   |         |
| CATEGORY |          1 | CATEGORY_ID |            1 | CATEGORY_ID | A         |          30 |     NULL | NULL   |         |
| CATEGORY |          1 | PARENT_ID   |            1 | PARENT_ID   | A         |          15 |     NULL | NULL   |         |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
3 rows in set (0.00 sec)

mysql> SHOW INDEX FROM TD_CATEGORY;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+
| TD_CATEGORY |          0 | PRIMARY        |            1 | TD_CATEGORY_ID | A         |           0 |     NULL | NULL   |         |
| TD_CATEGORY |          0 | NAME           |            1 | NAME           | A         |           0 |     NULL | NULL   |         |
| TD_CATEGORY |          1 | TD_CATEGORY_ID |            1 | TD_CATEGORY_ID | A         |           0 |     NULL | NULL   |         |
| TD_CATEGORY |          1 | CATEGORY_ID    |            1 | CATEGORY_ID    | A         |           0 |     NULL | NULL   |         |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)

mysql> alter table TD_CATEGORY add foreign key (CATEGORY_ID) references CATEGORY(CATEGORY_ID);
ERROR 1005: Can't create table './salestarts/#sql-655a_1278.frm' (errno: 150)

Hello,
last comment was on 25 Sep 12:31am is there any workaround for this? I hav this Problem on 4.0.22 and 4.1 when I try to create an n:m relation between two tables.

here is the sql Create script to reproduce this:

CREATE TABLE tbl2 (
  tbl2Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(tbl2Id)
)
TYPE=InnoDB;

CREATE TABLE tbl1 (
  tbl1id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(tbl1id)
)
TYPE=InnoDB;

CREATE TABLE tbl1_has_tbl2 (
  tbl1id INTEGER UNSIGNED NOT NULL,
  tbl2Id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(tbl1id, tbl2Id),
  FOREIGN KEY(tbl1id)
    REFERENCES tbl1(tbl1id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(tbl2Id)
    REFERENCES tbl2(tbl2Id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

How can I avoid this and create a simple n:m relation in MySQL?

Lorenz

The diagnostics could be improved in 4.1, but not in 4.0. I'm assigning this to Jan Lindström.
Note that you can see the latest foreign key error message by issuing the command
SHOW INNODB STATUS\G

Hi!

In 4.0.18, your script does not work because you do not have an index on tbl2Id. A combined primary key does not suffice, since tbl2Id is not the FIRST column in it.

In 4.1.8 your script does work ok, because MySQL automatically creates an index on FOREIGN KEYs.

4.1.8:

mysql> CREATE TABLE tbl2 (
    ->   tbl2Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY(tbl2Id)
    -> )
    -> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.22 sec)

mysql>
mysql> CREATE TABLE tbl1 (
    ->   tbl1id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY(tbl1id)
    -> )
    -> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql>
mysql> CREATE TABLE tbl1_has_tbl2 (
    ->   tbl1id INTEGER UNSIGNED NOT NULL,
    ->   tbl2Id INTEGER UNSIGNED NOT NULL,
    ->   PRIMARY KEY(tbl1id, tbl2Id),
    ->   FOREIGN KEY(tbl1id)
    ->     REFERENCES tbl1(tbl1id)
    ->       ON DELETE NO ACTION
    ->       ON UPDATE NO ACTION,
    ->   FOREIGN KEY(tbl2Id)
    ->     REFERENCES tbl2(tbl2Id)
    ->       ON DELETE NO ACTION
    ->       ON UPDATE NO ACTION
    -> )
    -> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql>

Regards,

Heikki

Also this error is caused by a difference between the definition of the fields involved in the foreign key. I agree that there must be a better warning message.

It seems to me that this bug was fixed in MySQL 5.6 when implementing the ALTER TABLE API (WL#5534). The missing columns will be noticed before InnoDB gets invoked, in both ALGORITHM=COPY and ALGORITHM=INPLACE.

Note that TYPE=InnoDB was deprecated a long time ago, and it does not work in MySQL 5.6 any more. The new syntax is ENGINE=InnoDB.