Hello,There must have been some changes in the default interpretation respective to foreign key attributes.... If I add explicitly 'not null' to the columns host_id and admin_id in test_nkomp_admin, it works like intended:
create table test_nkomp_admin ( host_id int unsigned not null, admin_id varchar(15) not null,foreign key (host_id) references test_nkomp (host_id) on delete cascade on update cascade, foreign key (admin_id) references test_admin (admin_id) on delete cascade on update cascade
)engine=innodb; cheers gregor On 28.10.2010 17:18, misiaQ wrote:
Some more testing performed and it seems like problem with foreign key reference indexing, see below: create table test_nkomp_admin2 ( host_id int unsigned, admin_id varchar(15), foreign key (host_id) references test_nkomp (host_id) on delete cascade on update cascade )engine=innodb; create table test_nkomp_admin3 ( host_id int unsigned, admin_id varchar(15) )engine=myisam; insert into test_nkomp_admin2 (host_id,admin_id) values (1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han'); insert into test_nkomp_admin2 (host_id,admin_id) values (2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi'); insert into test_nkomp_admin2 (host_id,admin_id) values (3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2'); insert into test_nkomp_admin2 (host_id,admin_id) values (4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia'); insert into test_nkomp_admin2 (host_id,admin_id) values (5,'luke'),(5,'yoda'),(5,'anakin'); insert into test_nkomp_admin2 (host_id,admin_id) values (6,'luke'),(6,'yoda'); insert into test_nkomp_admin3 (host_id,admin_id) values (1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han'); insert into test_nkomp_admin3 (host_id,admin_id) values (2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi'); insert into test_nkomp_admin3 (host_id,admin_id) values (3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2'); insert into test_nkomp_admin3 (host_id,admin_id) values (4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia'); insert into test_nkomp_admin3 (host_id,admin_id) values (5,'luke'),(5,'yoda'),(5,'anakin'); insert into test_nkomp_admin3 (host_id,admin_id) values (6,'luke'),(6,'yoda'); mysql> select * from test_nkomp_admin where host_id=6 and admin_id="yoda" ; Empty set (0,00 sec) mysql> select * from test_nkomp_admin2 where host_id=6 and admin_id="yoda" ; +---------+----------+ | host_id | admin_id | +---------+----------+ | 6 | yoda | +---------+----------+ 1 row in set (0,00 sec) mysql> select * from test_nkomp_admin3 where host_id=6 and admin_id="yoda" ; +---------+----------+ | host_id | admin_id | +---------+----------+ | 6 | yoda | +---------+----------+ 1 row in set (0,00 sec) Regards, m -----Original Message----- From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de] Sent: Thursday, October 28, 2010 1:54 PM To: mysql@lists.mysql.com Subject: strange behavior in mysql-server 5.1.49 and 5.1.51 Hello list, I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 - maybe a fight against myself ;-) The following query works exactly as assumed in version 5.1.41 with the given testbed: select * from test_nkomp_admin where host_id=6 and admin_id="yoda"; /* version 5.1.41 ubuntu 10.04 */ mysql> select * from test_nkomp_admin where host_id=6 and admin_id="yoda" ; +---------+----------+ | host_id | admin_id | +---------+----------+ | 6 | yoda | +---------+----------+ 1 row in set (0.00 sec) whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from debian experimental,os ubuntu 10.10) the resultset is: mysql> select * from test_nkomp_admin where host_id=6 and admin_id="yoda" ; Empty set (0.00 sec) prove: select * from test_nkomp_admin where host_id=6 ; +---------+----------+ | host_id | admin_id | +---------+----------+ | 6 | luke | | 6 | yoda | +---------+----------+ 2 rows in set (0.00 sec) Could anyone prove this behavior, or can give hint what the problem might be ? /* testbed */ drop table if exists test_nkomp_admin; drop table if exists test_nkomp; drop table if exists test_admin; create table test_nkomp ( host_id int unsigned auto_increment not null primary key )engine=innodb; create table test_admin ( admin_id varchar(15) not null primary key )engine=innodb; create table test_nkomp_admin ( host_id int unsigned, admin_id varchar(15), foreign key (host_id) references test_nkomp (host_id) on delete cascade on update cascade, foreign key (admin_id) references test_admin (admin_id) on delete cascade on update cascade )engine=innodb; insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6); insert into test_admin (admin_id) values ('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han'); insert into test_nkomp_admin (host_id,admin_id) values (1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han'); insert into test_nkomp_admin (host_id,admin_id) values (2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi'); insert into test_nkomp_admin (host_id,admin_id) values (3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2'); insert into test_nkomp_admin (host_id,admin_id) values (4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia'); insert into test_nkomp_admin (host_id,admin_id) values (5,'luke'),(5,'yoda'),(5,'anakin'); insert into test_nkomp_admin (host_id,admin_id) values (6,'luke'),(6,'yoda'); cheers gregor ------------------------------------------------------ Mieszkania, domy, dzialki - najlepsze oferty! http://linkint.pl/f281e
-- Gregor Kling Abteilung ITS, Sachgebiet DVZ Fachhochschule Giessen Tel: 0641/309-1292 E-Mail: gregor.kl...@its.fh-giessen.de
smime.p7s
Description: S/MIME Cryptographic Signature
