"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
