Bug #24369 can't create new table with Foreign Keys referencing a current table with data
Submitted: 16 Nov 2006 17:06 Modified: 20 Nov 2006 23:40
Reporter: kevin martin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.28 OS:Linux (Linux and Solaris)
Assigned to: Heikki Tuuri CPU Architecture:Any

[16 Nov 2006 17:06] kevin martin
Description:
can't create new table with Foreign Keys referencing a current table with data slready in it.  here's my create syntax:

set FOREIGN_KEY_CHECKS=0;
use db;

create table alert_fact
(
alert_guid CHAR(32) BINARY not null,
fact_id CHAR(32) BINARY not null,

fact_vendor VARCHAR(64) BINARY not null,
fact_bundle VARCHAR(32) BINARY not null,
fact_version VARCHAR(32) BINARY not null,
fact_classname VARCHAR(64) BINARY not null,

fact_value LONGBLOB,

PRIMARY KEY(alert_guid, fact_id),
FOREIGN KEY(alert_guid) REFERENCES alerts(alert_guid),
INDEX (alert_guid(8))
) TYPE = InnoDB;

create INDEX alert_fact_index ON alert_fact (alert_guid);

set FOREIGN_KEY_CHECKS=1;

Here's the alerts table setup:

mysql> describe alerts;
+---------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| alert_guid | varchar(32) | NO | PRI | NULL | |
| alert_id | varchar(255) | NO | | NULL | |
| alert_from | varchar(255) | NO | | NULL | |
| alert_about | varchar(255) | NO | | NULL | |
| alert_postit | tinyint(1) | YES | | NULL | |
| alert_closed | tinyint(1) | YES | | NULL | |
| alert_closed_time | datetime | YES | | NULL | |
| alert_last_generation | int(11) | YES | | NULL | |
| alert_security_descriptor | longblob | YES | | NULL | |
+---------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.02 sec)

The alerts table already has existing data.

I get the following error during the create:

ERROR 1005 (HY000): Can't create table './adm/alert_fact.frm' (errno: 150)

Everything I've found indicates that this error is related to the FOREIGN KEY stuff (hence the reason I "set FOREIGN_KEY_CHECKS=0;" as the first line).  The table creates fine when the referenced table for the foreign key is empty.

How to repeat:
Create the "referenced" table and add data to it.  Try to create the referencing table afterwards.

Suggested fix:
Allow the "referencing" table to be created after the fact.
[16 Nov 2006 23:27] Hartmut Holzgraefe
could you provide a working self contained SQL example (both CREATE statements and some sample INSERTs)?
[20 Nov 2006 15:02] Heikki Tuuri
Kevin,

the problem is that you have declared the guid type in one table as BINARY and in the other table not. The columns must have compatible types in the parent table and the child table.

"
heikki@127:~/mysql-5.0/client$ ./mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.29-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table alerts (alert_guid varchar(32) primary key) engine = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> create table alert_fact
    -> (
    -> alert_guid CHAR(32) BINARY not null,
    -> fact_id CHAR(32) BINARY not null,
    ->
    -> fact_vendor VARCHAR(64) BINARY not null,
    -> fact_bundle VARCHAR(32) BINARY not null,
    -> fact_version VARCHAR(32) BINARY not null,
    -> fact_classname VARCHAR(64) BINARY not null,
    ->
    -> fact_value LONGBLOB,
    ->
    -> PRIMARY KEY(alert_guid, fact_id),
    -> FOREIGN KEY(alert_guid) REFERENCES alerts(alert_guid),
    -> INDEX (alert_guid(8))
    -> ) TYPE = InnoDB;

ERROR 1005 (HY000): Can't create table './test/alert_fact.frm' (errno: 150)
mysql>
mysql> show InnoDB status\G
*************************** 1. row ***************************
Status:
=====================================
061120 17:01:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 6, OS waits 3; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
061120 17:01:28 Error in foreign key constraint of table test/alert_fact:
FOREIGN KEY(alert_guid) REFERENCES alerts(alert_guid),
INDEX (alert_guid(8))
) TYPE = InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
...
"

You can print a detailed account of the latest foreign key error with SHOW INNODB STATUS\G.

Regards,

Heikki
[20 Nov 2006 23:40] kevin martin
Yep, you're right.  The problem was that I could not figure out how to tell what the referenced field was built like.  I ultimately just dumped the schema and data for the database, dropped the database, added the new table to the schema, then rebuilt the database.  Little more work than I had hoped but it allowed me to do what I needed.