List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:September 19 2003 6:50am
Subject:Re: InnoDB Foreign Key Constraint Issue
View as plain text  
David,

----- Original Message ----- 
From: "David Griffiths" <dgriffiths@stripped>
Newsgroups: mailing.database.myodbc
Sent: Friday, September 19, 2003 4:07 AM
Subject: InnoDB Foreign Key Constraint Issue


> The following constraint is failing:
>
> ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id)
> REFERENCES address_type (address_type_id);
...
> mysql> select distinct address_type_id from address_list;
> +-----------------+
> | address_type_id |
> +-----------------+
> |             100 |
> |             101 |
> |             102 |
> |             104 |
> |             105 |
> +-----------------+
>
> And here are all the address_type.address_type_id values:
>
> mysql> select address_type_id from address_type;
> +-----------------+
> | address_type_id |
> +-----------------+
> |             101 |
> |             102 |
> |             103 |
> |             104 |
> |             105 |
> +-----------------+
>
> As you can see, there are no "null" or "0" address_list.address_type_id,
and
> the address_type_id-values in address_list are the same as the
> address_type.address_type_id values.


as we can see, there is a value 100 in address_list which does not appear in
address_type :).

...
> MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table
> ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id)
> REFERENCES address_type (address_type_id)
> Foreign key constraint fails for table benchtest/#sql-166d_1c:
> ,
>   CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES
> `address_type` (`ADDRESS_TYPE_ID`)
> Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple:
>  0: len 4; hex 80000064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;;


InnoDB sets the highest bit in positive integers. Above we have a positive
integer 0x64 == 100 in decimal.


> But in parent table benchtest/address_type, in index PRIMARY,
> the closest match we can find is record:
> RECORD: info bits 0 0: len 4; hex 80000065; asc ...e;; 1: len 6; hex


The closest match is 0x65 == 101 in decimal.


> 00000000ac16; asc ......;; 2: len 7; hex 800000002d0084; asc ....-..;; 3:
> len 8; hex 427573696e657373; asc Business;;
>
>
> Can anyone tell me what the issue might be? According to the manual,
> everything should work. It doesn't look like a data issue....
>
> David.

P.S.

Since many people are waiting for multiple tablespaces, I am posting the
status here:

"
I have now got also crash recovery working with multiple tablespaces.

I spent this day tracking a memory corruption bug, which turned out to be an
unfreed semaphore when I drop a tablespace.

There is still a simple bug that all secondary indexes get created in the
system tablespace, but that should be easy to fix. ALTER TABLE fails in an
error

030919  3:40:22  InnoDB: Error creating file ./test/#sql-15f_3.ibd.
030919  3:40:22  InnoDB: Operating system error number 17 in a file
operation.
InnoDB: See http://www.innodb.com/ibman.html for installation help.
InnoDB: Error number 17 means 'File exists'.

ALTER TABLE apparently does not work because RENAME TABLE does not work yet
with .ibd files, they do not get renamed.
"

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

Order MySQL technical support from https://order.mysql.com/


Thread
Update questionjaydrake19 Sep
  • Re: Update questionPaul DuBois19 Sep
  • InnoDB Foreign Key Constraint IssueDavid Griffiths19 Sep
Re: InnoDB Foreign Key Constraint IssueHeikki Tuuri19 Sep
Re: InnoDB Foreign Key Constraint IssueDavid Griffiths19 Sep