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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.