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]