that makes sense except why is that not a problem for flight.sql and university.sql?
On 3/22/10, Martin Gainty <mgai...@hotmail.com> wrote: > > FOREIGN KEY (album_id) REFERENCES album(album_id)whichever value is being > used for to populate album_id is NOT presently as a row in the album table > (and therefore not in the index) > > populate the album record BEFORE inserting / updating the table which > references album_id thru the FK > > Martin Gainty > ______________________________________________ > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte > Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht > dient lediglich dem Austausch von Informationen und entfaltet keine > rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von > E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le > destinataire prévu, nous te demandons avec bonté que pour satisfaire > informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie > de ceci est interdite. Ce message sert à l'information seulement et n'aura > pas n'importe quel effet légalement obligatoire. Étant donné que les email > peuvent facilement être sujets à la manipulation, nous ne pouvons accepter > aucune responsabilité pour le contenu fourni. > > > > >> From: mmal...@gmail.com >> Date: Mon, 22 Mar 2010 18:00:27 -0400 >> Subject: "music.sql" with FK from "Learning MySQL" -- 1005 error? >> To: wi...@lists.mysql.com; mysql@lists.mysql.com >> >> 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=mgai...@hotmail.com >> > > _________________________________________________________________ > The New Busy is not the old busy. Search, chat and e-mail from your inbox. > http://www.windowslive.com/campaign/thenewbusy?ocid=PID27925::T:WLMTAGL:ON:WL:en-US:WM_HMP:032010_3 -- Due to the recession, requests for instant gratification will be deferred until arrears in scheduled gratification have been satisfied. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
