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-constraints.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=arch...@jab.org