Re: URGENT !! InnoDB Constraint problem !
Dimitri, please upgrade to a newer MySQL version. " Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display a detailed explanation of the latest InnoDB foreign key error in the server. " Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Dimitri Bosiers" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, June 07, 2004 12:56 PM Subject: 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: > > > > > > > >> 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] > -- 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 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: 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]
Re: URGENT !! InnoDB Constraint problem !
On Sun, 6 Jun 2004 15:48:37 +0200 Dimitri Bosiers <[EMAIL PROTECTED]> wrote: > 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]
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]
