| Bug #38445 | select Result abnormal when two table join and using normal index . | ||
|---|---|---|---|
| Submitted: | 30 Jul 2008 8:16 | Modified: | 20 Nov 2008 14:11 |
| Reporter: | wang xiaolin | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.45,5.0.51(GA),5.1.26 | OS: | Linux (CentOS 4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | abnormal, cluster, join, normal index, SELECT, Two tables | ||
[30 Jul 2008 8:20]
wang xiaolin
table testMM insert SQL .
Attachment: TestMM.SQL (text/plain), 101.63 KiB.
[30 Jul 2008 8:21]
wang xiaolin
test Data of table TestB .
Attachment: TestB.sql (text/plain), 34.10 KiB.
[30 Jul 2008 8:23]
wang xiaolin
ndb_cluster config
Attachment: ndbconfig.ini (application/octet-stream, text), 1.24 KiB.
[30 Jul 2008 8:26]
wang xiaolin
It is normal when only one Data Node . but not good in two Datanode,or 4 dataNode .
[31 Jul 2008 3:21]
wang xiaolin
ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=2 @10.4.6.222 (Version: 5.1.26, Nodegroup: 0, Master)
id=3 @10.4.6.223 (Version: 5.1.26, Nodegroup: 1)
id=4 @10.4.6.226 (Version: 5.1.26, Nodegroup: 2)
id=5 @10.4.6.228 (Version: 5.1.26, Nodegroup: 3)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.4.6.224 (Version: 5.1.26)
[mysqld(API)] 3 node(s)
id=6 @10.4.6.221 (Version: 5.1.26)
id=7 (not connected, accepting connect from 10.4.6.225)
id=8 @10.4.6.220 (Version: 5.1.26)
ndb_mgm>
ndb_mgm> quit
1)------------------
create two tables , note the indexes of testb_i02 .
CREATE TABLE test_b (
fj varchar(4) ,
a decimal(2,0) ,
b varchar(20) ,
c decimal(5,0) ,
f decimal(1,0) ,
PRIMARY KEY ( fj ),
KEY testb_i02 ( fj , f )
) ENGINE=ndbcluster DEFAULT CHARSET=cp932 ;
create table test_mm (
fa varchar(20),
fb varchar(8),
fc decimal(10,0),
fd decimal(1,0),
fe decimal(1,0) ,
fj varchar(4),
PRIMARY KEY (fa ) ,
KEY test_mm_i02 (fb,fc,fd, fe)
) ENGINE=ndbcluster DEFAULT CHARSET=cp932 ;
2)------------------
insert data ,please see the attanch file of insert SQL .
mysql -uroot -p123456 MAINC < test_b.SQL
mysql -uroot -p123456 MAINC < test_mm.SQL
3)----------------------
mysql> explain
-> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 ;
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-----------------------------------+
| 1 | SIMPLE | m | range | test_mm_i02 | test_mm_i02 | 25 | NULL | 10 | Using where with pushed condition |
| 1 | SIMPLE | b | eq_ref | PRIMARY,testb_i02 | PRIMARY | 10 | MAINC.m.fj | 1 | |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-----------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 ;
+----------------------+-------+------+------+------+
| fa | fc | fd | fj | f |
+----------------------+-------+------+------+------+
| 20080715CONT00009504 | 10000 | 1 | K001 | 1 |
| 20080715CONT00008901 | 10000 | 1 | M101 | 1 |
+----------------------+-------+------+------+------+
2 rows in set (0.00 sec)
it is normal ........
mysql>
mysql>
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 and b.f=1 ;
Empty set (0.01 sec)
******************************************************
************ it does not work now !!!! ************
******************************************************
mysql>
mysql> explain
-> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 and b.f=1 ;
+----+-------------+-------+-------+-------------------+-------------+---------+------------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-------------+---------+------------------+------+-----------------------------------+
| 1 | SIMPLE | m | range | test_mm_i02 | test_mm_i02 | 25 | NULL | 10 | Using where with pushed condition |
| 1 | SIMPLE | b | ref | PRIMARY,testb_i02 | testb_i02 | 12 | MAINC.m.fj,const | 1 | Using where with pushed condition |
+----+-------------+-------+-------+-------------------+-------------+---------+------------------+------+-----------------------------------+
2 rows in set (0.00 sec)
it is not work correct now !!
4)----------------------
mysql> drop index testb_i02 on test_b ;
Query OK, 317 rows affected (2.75 sec)
Records: 317 Duplicates: 0 Warnings: 0
5)-----------------------------
select again ,it is ok ..
mysql>
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 ;
+----------------------+-------+------+------+------+
| fa | fc | fd | fj | f |
+----------------------+-------+------+------+------+
| 20080715CONT00009504 | 10000 | 1 | K001 | 1 |
| 20080715CONT00008901 | 10000 | 1 | M101 | 1 |
+----------------------+-------+------+------+------+
2 rows in set (0.01 sec)
mysql>
mysql>
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 and b.f=1 ;
+----------------------+-------+------+------+------+
| fa | fc | fd | fj | f |
+----------------------+-------+------+------+------+
| 20080715CONT00008901 | 10000 | 1 | M101 | 1 |
| 20080715CONT00009504 | 10000 | 1 | K001 | 1 |
+----------------------+-------+------+------+------+
2 rows in set (0.00 sec)
6)--------------------
Change the primary key for test again .
mysql> alter table test_b drop primary key ;
Query OK, 317 rows affected (1.79 sec)
Records: 317 Duplicates: 0 Warnings: 0
mysql>
mysql> alter table test_b add primary key(fj,f) ;
Query OK, 317 rows affected (1.68 sec)
Records: 317 Duplicates: 0 Warnings: 0
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 and b.f=1 ;
+----------------------+-------+------+------+------+
| fa | fc | fd | fj | f |
+----------------------+-------+------+------+------+
| 20080715CONT00009504 | 10000 | 1 | K001 | 1 |
| 20080715CONT00008901 | 10000 | 1 | M101 | 1 |
+----------------------+-------+------+------+------+
2 rows in set (0.01 sec)
mysql> select m.fa,m.fc,m.fd,b.fj,b.f
-> from test_mm m , test_b b
-> where m.fj=b.fj and m.fb='00000025' and m.fc>0 and
-> m.fd =1 and m.fe=1 ;
+----------------------+-------+------+------+------+
| fa | fc | fd | fj | f |
+----------------------+-------+------+------+------+
| 20080715CONT00008901 | 10000 | 1 | M101 | 1 |
| 20080715CONT00009504 | 10000 | 1 | K001 | 1 |
+----------------------+-------+------+------+------+
2 rows in set (0.00 sec)
description:
I describe this bug again to make it more easy to read.
I got a wired error about join select with index....
Two table join on normal index , it will dose NOT work correctly .
but ,not index or primary index , it will work .
if there is Only one Data Node,normal index will work correctly !
[31 Jul 2008 3:25]
wang xiaolin
Test again for more easy to read .
Attachment: bug-data-38445.zip.zip (application/x-zip-compressed, text), 5.42 KiB.
[31 Jul 2008 4:09]
wang xiaolin
description: I describe this bug again to make it more easy to read. I got a wired error about join select with index.... I think it is just because of the INDEX. There are 2 tables in mysql-cluster.(pls check the table DDL files blow) I ran two SQLs. The result was surposed to be same, because there is only one more condition "b.f=1" in <SQL 2> than <SQL 1> and all the record was set "1" in column "f". ---- But I got 2 rows in SQL-1 's result and 0 rows in SQL-2 's result. ---- There are the two SQL: <SQL 1> select m.fa,m.fc,m.fd,b.fj,b.f from test_mm m, test_b b where m.fj=b.fj and m.fb='00000025' and m.fc>0 and m.fd =1 and m.fe=1; <SQL 2> select m.fa,m.fc,m.fd,b.fj,b.f from test_mm m, test_b b where m.fj=b.fj and m.fb='00000025' and m.fc>0 and m.fd =1 and m.fe=1 and b.f=1; I tried to drop the index, it can work very well. To change the normal index to primary index can get right result too. IT IS A INDEX BUG!!! And in one-Data-Node-cluster, everything is OK!! HELP ME!!!
[31 Jul 2008 5:35]
Bruce Lee
i am waiting for the solving online!!
[31 Jul 2008 21:30]
Hartmut Holzgraefe
Reproducible with MySQL 5.0 and 5.1 but not with ndb-6.2.x or 6.3.x
[31 Jul 2008 21:32]
Hartmut Holzgraefe
test case
Attachment: bug38445.test (application/octet-stream, text), 61.96 KiB.
[1 Aug 2008 1:53]
wang xiaolin
Dear Hartmut Holzgraefe : If I want use MySQL5.0.51b(GA) , which ndb version can I select ? I must use the version 5.1.23 or above , is that so ?
[1 Aug 2008 2:06]
Bruce Lee
hi,Hartmut Thank you very much for your reply. I am very new to mysql-cluster and even to mysql. And responsable for evaluating of the mysql-cluster for a Finance System. Could you give me some advice? 1. Should I choose mysql 5.0 or 5.1 with some msyql-cluster? 2. Which mysql-cluster is more stable, mysql-cluster 6.2 or 6.3 ? 3. Or am I too early to think about mysql-cluster? Thank you very much Bruce
[1 Aug 2008 8:13]
Hartmut Holzgraefe
MySQL Cluster releases are currently decoupled from main MySQL releases and are maintained on a separate branch (that gets all main line fixes merged). We plan to merge the two code lines into a single code base at some point in the future but for now Cluster needs to live on a different release schedule than main line MySQL. The current most stable cluster release is MySQL Cluster 6.2.15 which is based on MySQL 5.1.23 (don't let the 6.x version numbers fool you, these only apply to the cluster as a product and do *not* indicate which MySQL version the product is based on ... the full version string of Cluster 6.2.15 is mysql-5.1.23-ndb-6.3.15 for example and shows in --version and SELECT VERSION() output) Cluster in MySQL 5.0 is in maintainence mode now so whether the fix for this problem gets back ported depends on the risk and effort estimates for such backport. MySQL 5.1 contains cluster code but we will not support cluster on that code base and will disable the cluster engine in all official MySQL 5.1 binaries. All new cluster based projects should start with at least MySQL Cluster 6.2.15 right away as it is already way more mature than cluster was in 5.0 and as it provides some interesting new features. Both MySQL Cluster 6.2.x and 6.3.x have been declared GA, for now we are only releasing binaries for 6.2.x though while 6.3.x is only available in source. So for now i'd recommend starting with 6.2.15 unless one of the new features introduced in 6.3.x is needed. A rolling upgrade from 6.2.x to 6.3.x at a later date is always possible as unlike with previous releases we now take care of upgrade compatibility between major releases (where possible) and do not require a backup and restore on upgrades that change the first or second version number digit anymore. This discussion doesn't really belong into the bug system though so, the cluster forum on http://forums.mysql.com/list.php?25 and the public cluster mailing list http://lists.mysql.com/cluster are more suitable places ...
[1 Aug 2008 8:54]
Bruce Lee
hi,Hartmut Thank you very much. You are so kind. We will try to port our system on mysql-cluster. I hope this will be a successful case of mysql-cluster. Thank you Bruce
[20 Nov 2008 14:11]
Bernd Ocklin
This is fixed in later versions. Upgrade to ndb 6.X is recommended.
Description: Hi MySQL ser: when I run a SQL in Cluster , It return wrong record for me . This MySQL is not very complex : ----- SQL--1----- Select CONTRACT_ID,A.Status,A.Active_Flag MMActive, t.Active_Flag TestBActive from TestMM A , TestB t where A.product_Id = t.product_Id and A.amount_No_Settled > 0 and A.active_Flag = 1 and A.status = 1 and A.customer_Id ='00000025' and t.ACTIVE_FLAG=1 ; It return no record. if two data node ,it will return one record . ----- SQL--2----- select CONTRACT_ID,A.Status,A.Active_Flag MMActive, t.Active_Flag TestBActive from TestMM A , TestB t where A.product_Id = t.product_Id and A.amount_No_Settled > 0 and A.active_Flag = 1 and A.status = 1 and A.customer_Id ='00000025' CONTRACT_ID Status MMActive TestBActive -------------------- --------- ----------- -------------- 20080715CONT00009504 1 1 1 20080715CONT00008901 1 1 1 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] [Executed: 08-7-30 下午04时02分38秒 ] [Execution: 16/ms] --------------- the difference of SQL-1 and SQL-2 is "and t.ACTIVE_FLAG=1 " , but in fact it is nothing of the same result . This will take place the following situation, the explain information is : id select_type tableyype possible_keys key key_len ref rows Extra 1 SIMPLE A range TestMM_I02 TestMM_I02 25 NULL 10 Using where with pushed condition 1 SIMPLE t ref PRIMARY,TestB_02 TestB_02 12 MAINC.A.Product_ID,const 1 Using where with pushed condition id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A range TestMM_I02 TestMM_I02 25 NULL 10 Using where with pushed condition 1 SIMPLE t eq_ref PRIMARY,TestB_02 PRIMARY 10 MAINC.A.Product_ID 1 please check and confirm it . thanks very much . How to repeat: CREATE TABLE `TestB` ( `PRODUCT_ID` varchar(4) NOT NULL, `PType` decimal(2,0) NOT NULL DEFAULT '0', `LevelID` varchar(20) NOT NULL, `DISPLAYO` decimal(5,0) DEFAULT NULL, `ACTIVE_FLAG` decimal(1,0) DEFAULT NULL, PRIMARY KEY (`PRODUCT_ID`), KEY `TestB_02` (`PRODUCT_ID`,`ACTIVE_FLAG`) ) ENGINE=ndbcluster DEFAULT CHARSET=cp932 ; create table TestMM (CONTRACT_ID varchar(20),Customer_ID varchar(8),amount_No_Settled decimal(10,0),Status decimal(1,0),Product_ID varchar(4), Active_Flag decimal(1,0) , PRIMARY KEY (CONTRACT_ID ) , KEY `TestMM_I02` (Customer_ID,amount_No_Settled,Status, Active_Flag) ) ENGINE=ndbcluster DEFAULT CHARSET=cp932 ; ; the data see attached file . ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=2 @10.4.6.222 (Version: 5.1.26, Nodegroup: 0, Master) id=3 @10.4.6.223 (Version: 5.1.26, Nodegroup: 1) id=4 @10.4.6.226 (Version: 5.1.26, Nodegroup: 2) id=5 @10.4.6.228 (Version: 5.1.26, Nodegroup: 3) [ndb_mgmd(MGM)] 1 node(s) id=1 @10.4.6.224 (Version: 5.1.26) [mysqld(API)] 3 node(s) id=6 @10.4.6.221 (Version: 5.1.26) id=7 (not connected, accepting connect from 10.4.6.225) id=8 @10.4.6.220 (Version: 5.1.26) [root@mfx224 cluster]# uname -a Linux mfx224 2.6.9-67.EL #1 Fri Nov 16 12:34:13 EST 2007 i686 i686 i386 GNU/Linux