URGENT !! InnoDB Constraint problem !
i dont have that section in my status report (ill tried it before) here's the report *** 1. row *** Status: = 040607 10:04:41 INNODB MONITOR OUTPUT = Per second averages calculated from the last 5 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 24, signal count 24 Mutex spin waits 4, rounds 41, OS waits 0 RW-shared spins 48, OS waits 24; RW-excl spins 1, OS waits 0 TRANSACTIONS Trx id counter 0 22817 Purge done for trx's n:o 0 22815 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request 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 78 OS file reads, 157 OS file writes, 94 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 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 0, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 1345367 Log flushed up to 0 1345367 Last checkpoint at 0 1345367 0 pending log writes, 0 pending chkp writes 53 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16796660; in additional pool allocated 673536 Buffer pool size 512 Free buffers 473 Database pages 38 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 38, created 0, written 103 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool activity since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue; main thread: waiting for server activity Number of rows inserted 6, updated 0, deleted 5, read 1264 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT On 06 Jun 2004, at 16:37, Josh Trutwin wrote: On Sun, 6 Jun 2004 15:48:37 +0200 Dimitri Bosiers [EMAIL PROTECTED] wrote: snip INSERT INTO pages(hidden,name,page_number,data,is_section_root,layout_id,group_ id) VALUES(1,'nieuwe pagina',100,'',2,1,@LID); this fails with a ERROR 1216: Cannot add a child row: a foreign key constraint fails Run: SHOW INNODB STATUS; And post the results in the Latest Foreign Key Error section. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT !! InnoDB Constraint problem !
I have a problem with my innodb database wich freaks me out ,
first a description of the involving tables :
mysql describe pages ;
+-+--+--+-+-
++
| Field | Type | Null | Key | Default | Extra
|
+-+--+--+-+-
++
| id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| locked | enum('true','false') | | | false |
|
| protected | enum('true','false') | | | false |
|
| hidden | enum('true','false') | | | false |
|
| name| varchar(32) | | | |
|
| page_number | smallint(16) | | | 0 |
|
| data| blob | | | |
|
| is_section_root | enum('true','false') | | | false |
|
| layout_id | smallint(5) unsigned | | MUL | 0 |
|
| group_id| smallint(5) unsigned | | MUL | 0 |
|
+-+--+--+-+-
++
10 rows in set (0.00 sec)
mysql describe page_groups ;
+++--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
+++--+-+-
++
| id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| button | smallint(5) unsigned | | | 0 |
|
| locked | enum('true','false') | | | true|
|
| protected | enum('true','false') | | | false |
|
| hidden | enum('false','true') | | | false |
|
| name | varchar(16)| | | |
|
| lang | enum('nl','fr','en','de','es') | | | nl |
|
| variant| varchar(16)| | | |
|
| section| varchar(64)| | | |
|
| descript | tinyblob | | | |
|
| version_id | smallint(5) unsigned | | MUL | 0 |
|
+++--+-+-
++
11 rows in set (0.00 sec)
mysql describe templates ;
+---+---+--+-+-
++
| Field | Type | Null | Key | Default | Extra
|
+---+---+--+-+-
++
| id| smallint(5) unsigned | | PRI | NULL|
auto_increment |
| type | enum('layout','item') | | | layout |
|
| locked| enum('true','false') | | | true|
|
| protected | enum('true','false') | | | false |
|
| name | varchar(64) | | | nieuw |
|
| descript | tinyblob | | | |
|
| templ | blob | | | |
|
+---+---+--+-+-
++
7 rows in set (0.00 sec)
now i insert a new page_group:
mysql INSERT into
page_groups(button,locked,protected,hidden,name,variant,section,lang,des
cript,version_id) values(0,2,2,2,'new en','b2c','nieuwe
sectie','en','',1) ;
this goes fine
so i store the insert id
mysql set @LID = LAST_INSERT_ID() ;
goes fine too ;
then i want to use @LID as constraint-key to insert a new page ;
INSERT INTO
pages(hidden,name,page_number,data,is_section_root,layout_id,group_id)
VALUES(1,'nieuwe pagina',100,'',2,1,@LID);
this fails with a ERROR 1216: Cannot add a child row: a foreign key
constraint fails
the weird thing is that both the primaries templates:1(layout_id)
and page_groups:@LID(group_id) exist .
Replacing @LID by is numeric value gives the same error althoug if i
choose an other existing row in page_groups then all works fine
I'm in auto-commit mode and suspect that something does not get updated
but what ?? an index ??
How does this come and how can i fix it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
