"Rhino" <[EMAIL PROTECTED]> wrote on 01/16/2006 10:30:33 AM:
> Can anyone help me with an InnoDB problem? > > I am trying to create a sequence of related tables using InnoDB but I'm not > having a lot of luck. The script I'm running would work fine in DB2 but the > rules are obviously different in MySQL. I'm hoping someone can tell me how > to modify my script so that it works; I suspect I have to do things in a > different sequence. > > My script fails on the creation of the third table, Rel, with: > > ERROR 1005: Can't create table './Maximal/Ref.frm' (errno: 150) > > Here is my script: > > ------------------------------------------------------------------------------------------------------------- > use tmp; > > select "Drop/create Locale" as ""; > drop table if exists Locale; > create table if not exists Locale > (locale char(7) not null, > locale_description varchar(255) not null, > primary key(locale), > ) Type=InnoDB; > > select "Display Locale" as ""; > select * from Locale; > > select "Drop/create Labels" as ""; > drop table if exists Labels; > create table if not exists Labels > (locale char(7) not null, > label_name char(20) not null, > label_organization char(20) not null, > label_title char(20) not null, > label_email char(20) not null, > label_phone char(20) not null, > label_calltime char(20) not null, > label_relationship char(20) not null, > primary key(locale), > foreign key(locale) references Locale(locale) on delete restrict, > ) Type=InnoDB; > > select "Display Labels" as ""; > select * from Labels; > > select "Drop/create Ref" as ""; > drop table if exists Ref; > create table if not exists Ref > (ref_name varchar(30) not null, > locale char(7) not null, > ref_org varchar(30) not null, > ref_title varchar(30) not null, > ref_email varchar(30) not null, > ref_phone varchar(30) not null, > ref_calltime varchar(100) not null, > primary key(ref_name, locale), > foreign key(locale) references Locale(locale) on delete restrict, > ) Type=InnoDB; > > select "Display Ref" as ""; > select * from Ref; > > select "Drop/create Ref_Rels" as ""; > drop table if exists Ref_Rels; > create table if not exists Ref_Rels > (ref_name varchar(30) not null, > locale char(7) not null, > ref_para_no smallint not null, > ref_para blob not null, > primary key(ref_name, locale, ref_para_no), > foreign key(ref_name, locale) references Ref(ref_name, locale) on delete > restrict, > ) Type=InnoDB; > > select "Display Ref_Rels" as ""; > select * from Ref_Rels; > > ------------------------------------------------------------------------------------------------------------- > > The output from the 'show innodb status' command was: > > ------------------------------------------------------------------------------------------------------------- > ===================================== > 060116 10:23:54 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 53 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 75, signal count 75 > Mutex spin waits 64, rounds 506, OS waits 0 > RW-shared spins 149, OS waits 73; RW-excl spins 4, OS waits 2 > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 060116 10:23:47 Error in foreign key constraint of table Maximal/Ref_Rels, > foreign key(ref_name, locale) references Ref(ref_name, locale) on delete > restrict, > ) Type=InnoDB. > Cannot resolve table name close to: > (ref_name, locale) on delete restrict, > ) Type=InnoDB > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 22367 > Purge done for trx's n:o < 0 22342 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 22360, not started, process no 16625, OS thread id 50446347 > MySQL thread id 3070, query id 22928 localhost rhino > show innodb status > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 474 OS file reads, 749 OS file writes, 511 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 0.11 writes/s, 0.11 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf for space 0: size 1, free list len 0, seg size 2, > 0 inserts, 0 merged recs, 0 merges > Hash table size 34679, used cells 20, node heap has 1 buffer(s) > 0.00 hash searches/s, 2.71 non-hash searches/s > --- > LOG > --- > Log sequence number 0 16595016 > Log flushed up to 0 16595016 > Last checkpoint at 0 16584516 > 0 pending log writes, 0 pending chkp writes > 350 log i/o's done, 0.16 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 18931376; in additional pool allocated 772352 > Buffer pool size 512 > Free buffers 87 > Database pages 424 > Modified db pages 18 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 421, created 3, written 1115 > 0.00 reads/s, 0.00 creates/s, 0.51 writes/s > Buffer pool hit rate 1000 / 1000 > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > Main thread process no. 20088, id 114696, state: sleeping > Number of rows inserted 209, updated 54, deleted 0, read 81812 > 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ > > ------------------------------------------------------------------------------------------------------------- > > > I did 'show create table xxx' commands for each of the four tables and got > this: > > ------------------------------------------------------------------------------------------------------------- > show create table Locale; > > | Locale | CREATE TABLE `Locale` ( > `locale` varchar(7) NOT NULL default '', > `locale_description` varchar(255) NOT NULL default '', > PRIMARY KEY (`locale`) > ) TYPE=InnoDB | > > show create table Labels; > > | Labels | CREATE TABLE `Labels` ( > `locale` char(7) NOT NULL default '', > `label_name` char(20) NOT NULL default '', > `label_organization` char(20) NOT NULL default '', > `label_title` char(20) NOT NULL default '', > `label_email` char(20) NOT NULL default '', > `label_phone` char(20) NOT NULL default '', > `label_calltime` char(20) NOT NULL default '', > `label_relationship` char(20) NOT NULL default '', > PRIMARY KEY (`locale`), > CONSTRAINT `0_369` FOREIGN KEY (`locale`) REFERENCES `Locale` (`locale`) > ) TYPE=InnoDB | > > show create table Ref; > > ERROR 1146: Table 'tmp.Ref' does not exist > > show create table Ref_Rels; > > ERROR 1146: Table 'tmp.Ref_Rels' does not exist > ------------------------------------------------------------------------------------------------------------- > > I hope you're not going to tell me that I have to modify the script so that > it doesn't create the foreign keys until after the data has been loaded; > that might keep the error from occurring but it would also let me put bad > data in the table which I would then have to fix.... > > But I'm getting ahead of myself: can someone tell me why MySQL/InnoDB is > refusing to create my Ref table? I need to figure out how to create and > populate these tables successfully. > > --- > Rhino > The quick and dirty rules to establishing foreign keys in MySQL: A) both table must be of type InnoDB (you satisfied this) B) both ends of the key must point to columns of the same data type and sign. (you satisfied this) C) both ends of the key must be the leftmost portion of an index on their respective (you did not satisfy this) D) the child table must not contain data that would trip an exception on the FK you are trying to create (N/A as you have no data yet) In your `Ref` table the locale column is NOT a leftmost member of any index. You need to manually add one additional index for just that column, MySQL does not automate the creation of the child index until 4.1.2. Try changing the definition of your REF table to this: select "Drop/create Ref" as ""; drop table if exists Ref; create table if not exists Ref (ref_name varchar(30) not null, locale char(7) not null, ref_org varchar(30) not null, ref_title varchar(30) not null, ref_email varchar(30) not null, ref_phone varchar(30) not null, ref_calltime varchar(100) not null, primary key(ref_name, locale), key(locale), foreign key(locale) references Locale(locale) on delete restrict, ) Type=InnoDB; For the complete details please refer to : http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
