I can't believe I missed that. The insert statement is in the script - not sure why it wasn't added - not sure why I missed something so obvious.
David. ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 11:50 PM Subject: Re: InnoDB Foreign Key Constraint Issue > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
