"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

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

am 22.03.2010 23:22:14 von Mitchell Maltenfort

that makes sense except why is that not a problem for flight.sql and
university.sql?

On 3/22/10, Martin Gainty 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 tabl=
e
> (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=E9ni et de confidentialit=
=E9
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugt=
e
> 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 =EAtre privil=E9gi=E9. Si vous n'=EAt=
es pas le
> destinataire pr=E9vu, nous te demandons avec bont=E9 que pour satisfaire
> informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e ou la=
copie
> de ceci est interdite. Ce message sert =E0 l'information seulement et n'a=
ura
> pas n'importe quel effet l=E9galement obligatoire. =C9tant donn=E9 que le=
s email
> peuvent facilement =EAtre sujets =E0 la manipulation, nous ne pouvons acc=
epter
> aucune responsabilit=E9 pour le contenu fourni.
>
>
>
>
>> From: mmalten@gmail.com
>> Date: Mon, 22 Mar 2010 18:00:27 -0400
>> Subject: "music.sql" with FK from "Learning MySQL" -- 1005 error?
>> To: win32@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', '', '
>> ==================== =====
=============3D
>> 100322 17:30:47 INNODB MONITOR OUTPUT
>> ==================== =====
=============3D
>> 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 >=3D 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.ht=
ml
>> 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=3Dmgainty@hotmail.=
com
>>
> =09
> ____________________________________________________________ _____
> The New Busy is not the old busy. Search, chat and e-mail from your inbox=
..
> http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID279 25::T:WLMTAGL=
:ON:WL:en-US:WM_HMP:032010_3

--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

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

am 22.03.2010 23:22:14 von Mitchell Maltenfort