David, ----- Original Message ----- From: "David Griffiths" <[EMAIL PROTECTED]> 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
