"music.sql" with FK from "Learning MySQL" -- 1005 error?

"music.sql" with FK from "Learning MySQL" -- 1005 error?

am 22.03.2010 23:00:27 von Mitchell Maltenfort

I've been mucking with the O'Reilly book "Learning MySQL" for the
obvious purpose of learning MySQL.

The book offers two versions of a program:

http://learningmysql.com/Downloads/Files/Data/SQL_files_with _foreign_key_references/music.sql
sticks at creating the track table. However, its sibling programs

http://learningmysql.com/Downloads/Files/Data/SQL_files_with _foreign_key_references/University.sql
and http://learningmysql.com/Downloads/Files/Data/SQL_files_with _foreign_key_references/Flight.sql
work fine, as does the version without foreign key references

If this helps explain it, I'm using the latest stable community MySQL
on a Dell Optiplex GX620 with 0.5 Gig RAM and Windows XP.

The error from "show innodb status" follows. Can anyone clarify what
might be causing the problem?

'InnoDB', '', '
=====================================
100322 17:30:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7, signal count 7
Mutex spin waits 0, rounds 70, OS waits 1
RW-shared spins 10, OS waits 5; RW-excl spins 1, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100322 17:29:47 Error in foreign key constraint of table music/track:
FOREIGN KEY (album_id) REFERENCES album(album_id)
):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-co nstraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 3373
Purge done for trx''s n:o < 0 3370 undo n:o < 0 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 792
MySQL thread id 2, query id 45 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (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
34 OS file reads, 109 OS file writes, 36 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 769826
Log flushed up to 0 769826
Last checkpoint at 0 769826
0 pending log writes, 0 pending chkp writes
29 log i/o''s done, 0.00 log i/o''s/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 15522286; in additional pool allocated 1389568
Dictionary memory allocated 24888
Buffer pool size 512
Free buffers 484
Database pages 27
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 24, created 3, written 79
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3936, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org