List:General Discussion« Previous MessageNext Message »
From:Gregor Kling Date:October 29 2010 6:27am
Subject:Re: strange behavior in mysql-server 5.1.49 and 5.1.51
View as plain text  
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.kling@stripped]
> Sent: Thursday, October 28, 2010 1:54 PM
> To: mysql@stripped
> 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.kling@stripped


Attachment: [application/pkcs7-signature] S/MIME Cryptographic Signature smime.p7s
Thread
strange behavior in mysql-server 5.1.49 and 5.1.51gregor kling28 Oct
  • RE: strange behavior in mysql-server 5.1.49 and 5.1.51misiaQ28 Oct
    • Re: strange behavior in mysql-server 5.1.49 and 5.1.51Gregor Kling28 Oct
  • RE: strange behavior in mysql-server 5.1.49 and 5.1.51misiaQ28 Oct
    • Re: strange behavior in mysql-server 5.1.49 and 5.1.51Gregor Kling29 Oct