| Bug #20881 | Bad recorders returned when Query with federated tables | ||
|---|---|---|---|
| Submitted: | 6 Jul 2006 3:19 | Modified: | 7 Aug 2006 6:53 |
| Reporter: | ming lu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Federated storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.11 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[24 Jul 2006 16:01]
Valeriy Kravchuk
Thank you for a problem report. I was not able to repeat the behaviour described with 5.1.12-BK on Linux (ChangeSet@1.2264, 2006-07-24 09:38:42+10:00, stewart@willster.(none) +1 -0):
openxs@suse:~/dbs/5.1> bin/mysql -uroot test --socket=/tmp/mysql51.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `b` (
-> `id_b` int(11) NOT NULL DEFAULT '0',
-> `id_a` int(11) DEFAULT NULL,
-> `c` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id_b`)
-> ) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1/test/b';
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `a` (
-> `id_a` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id_a`)
-> ) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1/test/a';
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `c` (
-> `id_c` int(11) NOT NULL DEFAULT '0',
-> `c` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id_c`),
-> KEY `tablec_c` (`c`)
-> ) ENGINE=innodb ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)
mysql> insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select a.id_a, b_alias.id_a, c.c, b_alias.c
-> from a,c,
-> (select id_a,c from b group by id_a) b_alias
-> where a.id_a=b_alias.id_a
-> and
-> c.c=b_alias.c
-> ;
Empty set (0.02 sec)
mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -uroot test --socket=/tmp/mysql51.sock
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select a.id_a, b_alias.id_a, c.c, b_alias.c from a,c, (select id_a,
c from b group by id_a) b_alias where a.id_a=b_alias.id_a and c.c=
b_alias.c;
Empty set (0.02 sec)
So, results are the same. Please, try to repeat with a newer sources/nightly build, or just wait for 5.1.12 to be released officially.
[7 Aug 2006 6:53]
ming lu
Sorry, maybe i make a mistake about the Server version, I think i do the test against to the mysql server 5.1.11 instead of 5.1.12 actually. Do you sure that the problem isn't exist in the version 5.1.11? By the way, when will the mysql server 5.1.12 be released ?
Description: With Release 5.1.12 beta. Federated Engine not works well. Bad recorders returned when Query with conditions of two federated tables . the same test scripts had been test on 5.0.18, and no faults with 5.0.18 . How to repeat: --step 1 --create the source database `sdb` -- and create 3 tables (a, b, c) and put some recorders into them. drop database sdb; create database sdb; use sdb; CREATE TABLE `a` ( `id_a` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id_a`) ); CREATE TABLE `b` ( `id_b` int(11) NOT NULL DEFAULT '0', `id_a` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id_b`) ); CREATE TABLE `c` ( `id_c` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id_c`), KEY `tablec_c` (`c`) ) ENGINE=innodb ROW_FORMAT=DYNAMIC ; insert a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); insert b values(1,101,1),(2,101,1),(3,101,1),(4,102,2),(5,102,2),(6,102,2),(7,103,3),(8,103,3),(9,103,3); insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3); --step 2 --create the another database `fdb` -- and create 2 federated tables for `sdb`.a `sdb`.b -- create table c same as `sdb`.c and put the same recorders into it. drop database fdb; create database fdb; use fdb; CREATE TABLE `a` ( `id_a` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id_a`) ) ENGINE=FEDERATED CONNECTION='mysql://root:1111@127.0.0.1:3306/sdb/a' ; CREATE TABLE `b` ( `id_b` int(11) NOT NULL DEFAULT '0', `id_a` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id_b`) ) ENGINE=FEDERATED CONNECTION='mysql://root:1111@127.0.0.1/sdb/b' ; CREATE TABLE `c` ( `id_c` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id_c`), KEY `tablec_c` (`c`) ) ENGINE=innodb ROW_FORMAT=DYNAMIC ; insert c values(1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3); --step 3 -- do a query with following sql on `sdb` -- the result should be empty, and it is. use sdb select a.id_a, b_alias.id_a, c.c, b_alias.c from a,c, (select id_a,c from b group by id_a) b_alias where a.id_a=b_alias.id_a and c.c=b_alias.c ; --Empty set (0.13 sec) --step 4 -- do the same query as step 3 on `fdb` -- the result should be same as `sdb`, -- the real results is: -- +------+------+---+------+ -- | id_a | id_a | c | c | -- +------+------+---+------+ -- | 0 | 101 | 1 | 1 | -- | 0 | 101 | 1 | 1 | -- | 0 | 101 | 1 | 1 | -- | 0 | 102 | 2 | 2 | -- | 0 | 102 | 2 | 2 | -- | 0 | 103 | 3 | 3 | -- | 0 | 103 | 3 | 3 | -- | 0 | 103 | 3 | 3 | -- +------+------+---+------+ -- 8 rows in set (0.11 sec) -- use fdb select a.id_a, b_alias.id_a, c.c, b_alias.c from a,c, (select id_a,c from b group by id_a) b_alias where a.id_a=b_alias.id_a and c.c=b_alias.c ; Suggested fix: no suggestion.