| Bug #34928 | Confusion by having Primary Key and Index | ||
|---|---|---|---|
| Submitted: | 28 Feb 2008 14:18 | Modified: | 7 Apr 2008 17:31 |
| Reporter: | Lars Johansson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.23 | OS: | Any |
| Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
[28 Feb 2008 14:18]
Lars Johansson
[28 Feb 2008 15:54]
Valeriy Kravchuk
Thak you for the problem report. Can you upload that DB3_BOM_PROD EXTENDED table? Please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for it.
[28 Feb 2008 16:16]
Lars Johansson
Hi,
Here are show create and show status displays,
i submit two status first on one repaired version and then on an unrepaired version of the table. The index lengths do not match.
I try to send the table tomorrow
show create table DB3_BOM_PROD
-> ;
| Table | Create Table
| DB3_BOM_PROD | CREATE TABLE `DB3_BOM_PROD` (
`P_PLANT` char(4) NOT NULL DEFAULT '',
`P_MATNR` char(18) NOT NULL DEFAULT '',
`P_REFCD` char(2) DEFAULT NULL,
`P_DIS_CON_IND` char(1) DEFAULT NULL,
`P_PRO_TYPE` char(1) DEFAULT NULL,
`P_SPEC_PRO` char(2) DEFAULT NULL,
`BOM_USE` char(1) NOT NULL DEFAULT '',
`ALT_BOM` char(2) NOT NULL DEFAULT '',
`P_MRP_CON` char(3) DEFAULT NULL,
`DISCON_IND` char(1) DEFAULT NULL,
`POSNR` char(4) NOT NULL DEFAULT '',
`C_PLANT` char(4) NOT NULL DEFAULT '',
`C_MATNR` char(18) NOT NULL DEFAULT '',
`C_REFCD` char(2) DEFAULT NULL,
`C_DIS_CON_IND` char(1) DEFAULT NULL,
`C_PRO_TYPE` char(1) DEFAULT NULL,
`C_SPEC_PRO` char(2) DEFAULT NULL,
`C_MRP_CON` char(3) DEFAULT NULL,
`QTY` decimal(13,3) DEFAULT NULL,
`UNIT` char(3) DEFAULT NULL,
`ITEM_CAT` char(1) DEFAULT NULL,
`REL_COST` char(1) DEFAULT NULL,
`SORT_STR` char(10) DEFAULT NULL,
`MAT_TYPE` char(4) DEFAULT NULL,
`VALID_FR` date DEFAULT NULL,
`CREATED_DATE` date DEFAULT NULL,
`CHANGED_DATE` date DEFAULT NULL,
`CREATED` datetime DEFAULT NULL,
PRIMARY KEY (`P_PLANT`,`P_MATNR`,`BOM_USE`,`ALT_BOM`,`POSNR`,`C_PLANT`,`C_MATNR`),
KEY `COMP2` (`P_PLANT`,`P_MATNR`,`ALT_BOM`,`BOM_USE`),
KEY `P_PLANT` (`P_PLANT`),
KEY `P_MATNR` (`P_MATNR`),
KEY `C_PLANT` (`C_PLANT`),
KEY `C_MATNR` (`C_MATNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
mysql> show table status LIKE 'DB3_BOM_PROD'; (from a repaired version)
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| DB3_BOM_PROD | MyISAM | 10 | Fixed | 266423 | 116 | 30905068 | 32651097298436095 | 17341440 | 0 | NULL | 2008-02-28 04:02:35 | 2008-02-28 14:51:39 | 2008-02-28 14:51:43 | latin1_swedish_ci | NULL | | |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> show table status LIKE 'DB3_BOM_PROD'; (from an unrepaired version)
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| DB3_BOM_PROD | MyISAM | 10 | Fixed | 266423 | 116 | 30905068 | 32651097298436095 | 20739072 | 0 | NULL | 2008-02-28 04:02:35 | 2008-02-28 04:03:20 | 2008-02-28 04:02:46 | latin1_swedish_ci | NULL | | |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
[28 Feb 2008 18:19]
Lars Johansson
Here is a log from last nite create of the table DB3_BOM_PR
Note this is mysql vs 5.1.22, (so this works).
080228 040235 2500 Log enabled, invoked as: ./scriptS.php schedule=dts_bom.xml logmode=warning
080228 040235 2500 Log PHP version 5.2.2, Zend version 2.2.0
080228 040235 2500 Note Current directory=/home/actaadm/dw_data_mngr/data/080228040215_dts_bom/dts_bom_prod, SQL-log=SQLLOG_0.TXT
080228 040235 2500 Note Host info 10.40.23.126 via TCP/IP
080228 040235 2500 Note mysql version 5.1.22-rc-log
080228 040235 2500 Note 10.40.23.126 via TCP/IP protocol version 10
080228 040235 2500 Note Client library vs 5.1.12-beta
080228 040235 2500 Note Default database is ACTADW
080228 040235 2500 Note Start executing sqlscript create
080228 040235 2500 Note Exec USE ACTADW;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec DROP TABLE IF EXISTS DB3_BOM_PROD;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec CREATE TABLE DB3_BOM_PROD(
PRIMARY KEY COMP1 (P_PLANT, P_MATNR, BOM_USE, ALT_BOM, POSNR, C_PLANT, C_MATNR)
,INDEX COMP2 (P_PLANT, P_MATNR, ALT_BOM ,BOM_USE)
,P_PLANT CHAR(4)
,INDEX P_PLANT (P_PLANT)
,P_MATNR CHAR(18)
,INDEX P_MATNR (P_MATNR)
,P_REFCD CHAR(2)
,P_DIS_CON_IND CHAR(1)
,P_PRO_TYPE CHAR(1)
,P_SPEC_PRO CHAR(2)
,BOM_USE CHAR(1)
,ALT_BOM CHAR(2)
,P_MRP_CON CHAR(3)
,DISCON_IND CHAR(1)
,POSNR CHAR(4)
,C_PLANT CHAR(4)
,INDEX C_PLANT (C_PLANT)
,C_MATNR CHAR(18)
,INDEX C_MATNR (C_MATNR)
,C_REFCD CHAR(2)
,C_DIS_CON_IND CHAR(1)
,C_PRO_TYPE CHAR(1)
,C_SPEC_PRO CHAR(2)
,C_MRP_CON CHAR(3)
,QTY NUMERIC (13,3)
,UNIT CHAR(3)
,ITEM_CAT CHAR(1)
,REL_COST CHAR(1)
,SORT_STR CHAR(10)
,MAT_TYPE CHAR(4)
,VALID_FR DATE
,CREATED_DATE DATE
,CHANGED_DATE DATE
,CREATED DATETIME
);
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Start executing sqlscript load
080228 040235 2500 Note Exec USE ACTADW;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec LOAD DATA LOCAL INFILE 'DTS_STPO_PROD.TXT'
REPLACE
INTO TABLE DB3_BOM_PROD
FIELDS
TERMINATED BY ';'
IGNORE 0 LINES
;
080228 040246 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040246 2500 Note SQLINFO Records: 207483 Deleted: 1 Skipped: 0 Warnings: 0 SQLSTATE=00000, ERRORNO=0
080228 040246 2500 Log disabled ./scriptS.php
[28 Feb 2008 18:23]
Lars Johansson
Hi, the infile we load to create DB3_BOM_PROD is 25mB so I do not know how to transfer it. but I send you a couple of lines. Please tell if you want more and how I should send. 1100;0101121280;;;E;CT;1;01;306;;0010;1100;0101121200;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0517530095;;;E;CT;1;01;305;;0010;1100;0515800014;;;F;;176;1.000;ST;L;X;0;ZMAT;20050101;20060109;00000000;2008-02-28 02:14:12 1100;0517530095;;;E;CT;1;01;305;;0020;1100;0517530015;;;F;;176;2.000;ST;L;X;0;ZMAT;20050101;20060109;00000000;2008-02-28 02:14:12 1100;0909100260;;;E;CT;1;01;305;;0010;1100;0909100200;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0909100260;;;E;CT;1;01;305;;0020;1100;4150166760;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0909100260;;;E;CT;1;01;305;;0030;1100;9900000301;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0909100290;;;E;CT;1;01;305;;0010;1100;0909100200;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0909100290;;;E;CT;1;01;305;;0020;1100;4150166760;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;0909100290;;;E;CT;1;01;305;;0030;1100;9900000301;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;1000000001;Z0;;X;;1;01;178;;0010;1100;0217110041;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;1000000001;Z0;;X;;1;01;178;;0020;1100;0217110042;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;1000000001;Z0;;X;;1;01;178;;0030;1100;0661100048;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;1000000001;Z0;;X;;1;01;178;;0040;1100;4175080801;;;F;;143;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;1000000001;Z0;;X;;1;01;178;;0050;1100;4175088502;;;F;;176;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12 1100;2050002813;;;E;;1;01;201;;0010;1100;0054371339;;;F;;184;0.212;KG;L;X;;ROH;20051231;20070125;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0005;1100;0335212500;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0006;1100;4250189400;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0007;1100;4250202101;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0008;1100;4250201900;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0009;1100;4250202111;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0011;1100;0663210246;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0012;1100;4250222200;;;F;;176;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12 1100;2050114272;;;E;CT;1;01;326;;0013;1100;0663900300;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
[29 Feb 2008 10:27]
Lars Johansson
Hi,
You can create the table in vs 5.1.22 and still se the garbage in vs 5.1.23.
I reproduced the bug with following script and supplied file.
CREATE TABLE DB3_BOM_PROD_LJ1( PRIMARY KEY COMP1 (P_PLANT, P_MATNR, BOM_USE, ALT_BOM, POSNR, C_PLANT, C_MATNR) ,INDEX COMP2 (P_PLANT, P_MATNR, ALT_BOM ,BOM_USE) ,P_PLANT CHAR(4) ,INDEX P_PLANT (P_PLANT) ,P_MATNR CHAR(18) ,INDEX P_MATNR (P_MATNR) ,P_REFCD CHAR(2) ,P_DIS_CON_IND CHAR(1) ,P_PRO_TYPE CHAR(1) ,P_SPEC_PRO CHAR(2) ,BOM_USE CHAR(1) ,ALT_BOM CHAR(2) ,P_MRP_CON CHAR(3) ,DISCON_IND CHAR(1) ,POSNR CHAR(4) ,C_PLANT CHAR(4) ,INDEX C_PLANT (C_PLANT) ,C_MATNR CHAR(18) ,INDEX C_MATNR (C_MATNR) ,C_REFCD CHAR(2) ,C_DIS_CON_IND CHAR(1) ,C_PRO_TYPE CHAR(1) ,C_SPEC_PRO CHAR(2) ,C_MRP_CON CHAR(3) ,QTY NUMERIC (13,3) ,UNIT CHAR(3) ,ITEM_CAT CHAR(1) ,REL_COST CHAR(1) ,SORT_STR CHAR(10) ,MAT_TYPE CHAR(4) ,VALID_FR DATE ,CREATED_DATE DATE ,CHANGED_DATE DATE ,CREATED DATETIME );
Query OK, 0 rows affected (0.01 sec)
LOAD DATA LOCAL INFILE '/home/actaadm/dw_data_mngr/data/080229040205_dts_bom/dts_bom_prod/DTS_STPO_PROD_LJ.TXT'
-> REPLACE
-> INTO TABLE DB3_BOM_PROD_LJ1
-> FIELDS
-> TERMINATED BY ';'
-> IGNORE 0 LINES
-> ;
Query OK, 3500 rows affected (0.20 sec)
Records: 3500 Deleted: 0 Skipped: 0 Warnings: 0
select distinct P_PLANT, P_MATNR,ALT_BOM,BOM_USE from DB3_BOM_PROD_LJ1 order by P_PLANT, P_MATNR,ALT_BOM,BOM_USE limit 25;
[11 Mar 2008 8:26]
Susanne Ebrecht
Lars, many thanks for reporting this bug. We can reproduce it without problems ... The bug in analyzing status because I am on the topic to figure out more details at the moment.
[11 Mar 2008 14:01]
Susanne Ebrecht
That the "SELECT" displayed the right results after "CHECK TABLE" is the result of bug #35212 I am still on my way to analyze what's wrong with "Index + Load data infile" here.
[11 Mar 2008 15:12]
Susanne Ebrecht
This bug needs a long time for analysing. The problem here is that MyISAM seems to get confused when limit is higher then 9 by having a PK and an index with the same columns but different order. I will attache a dump for reproducing. mysql> select distinct a,b,d,c from test order by a,b,d,c limit 10; Primary key is: a,b,c,d,e Index is: a,b,d,c This will only display nothing and Yen-sign when you have both: PK and Index. When you drop Index or PK it will show you the right result. It also doesn't matter if you make a "load data infile" or extended insert or a single insert statement for every row. Consider, this only occurs when limit is 10 or higher.
[11 Mar 2008 15:14]
Susanne Ebrecht
dump for reproducing
Attachment: bug34928.dump (application/octet-stream, text), 131.82 KiB.
[12 Mar 2008 13:48]
Susanne Ebrecht
Just to be sure this is a MyISAM problem. I also tested this with Innodb and Memory and both don't get confused with this indexes. They work as expected. Only MyISAM has this problem.
[20 Mar 2008 10:07]
Mattias Jonsson
Smaller dump that still shows the bug
Attachment: bug34928_small.dump (application/octet-stream, text), 4.12 KiB.
[20 Mar 2008 10:08]
Mattias Jonsson
Test case for the bug
Attachment: bug34928.test (application/octet-stream, text), 1.20 KiB.
[26 Mar 2008 15:54]
Alexey Kopytov
A shorter test case:
CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
PRIMARY KEY(a,b,c,d,e),
KEY(a,b,d,c)
);
INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
(1, 1, 2),
(1, 1, 3),
(1, 2, 1),
(1, 2, 2),
(1, 2, 3);
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
SELECT DISTINCT a, b, d, c FROM t1;
[26 Mar 2008 16:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44461 ChangeSet@1.2569, 2008-03-26 19:37:36+03:00, kaa@kaamos.(none) +3 -0 Fix for bug #34928: Confusion by having Primary Key and Index The bug is a regression introduced in 5.1 by the patch for bug28404. Under some circumstances test_if_skip_sort_order() could leave some data structures in an inconsistent state so that some parts of code could assume the selected execution strategy for GROUP BY/DISTINCT as a loose index scan (e.g. JOIN_TAB::is_using_loose_index_scan()), while the actual strategy chosen was an ordered index scan, which led to wrong data being returned. Fixed test_if_skip_sort_order() so that when changing the type for a join table, select->quick is reset not only for EXPLAIN, but for the actual join execution as well, to not confuse code that depends on its value to determine the chosen GROUP BY/DISTINCT strategy.
[31 Mar 2008 14:54]
Bugs System
Pushed into 5.1.24-rc
[2 Apr 2008 19:01]
Paul DuBois
Noted in 5.1.24 changelog. For some queries, the optimizer used an ordered index scan for GROUP BY or DISTINCT when it was supposed to use a loose index scan, leading to incorrect results. Resetting report to Patch queued waiting for push into 6.0.x.
[3 Apr 2008 13:02]
Bugs System
Pushed into 6.0.5-alpha
[7 Apr 2008 17:31]
Paul DuBois
Noted in 6.0.5 changelog.
