[Maria-discuss] Wrong ordering in MariaDB 5.2.10
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results. Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1; EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort [Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1; *EXPLAIN Result* id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1; All data is using a length of 7 even though it is defined as varchar(64).
I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this: SELECT VERSION() #5.2.10-MariaDB DROP TABLE IF EXISTS `tableinventory`; CREATE TABLE `tableinventory` ( `StockNo` VARCHAR(64) NOT NULL DEFAULT '', `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1; INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb'); SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC; SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC; /* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10): $number --------- ddd ccc bbb aaa */ Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem? Peter (not a MP person) On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com> wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.
Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1;
EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort
[Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort
Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
After testing inserting the full data into a new table, it seems the fault is in PBXT somewhere. This is a very old table that is constantly accessed. I can only reproduce it in the existing table. Any attempt to create it in a new one fails to have the same results. Also, data appears in the range version that seems to be transactional that is rolled back and not in the table by the indexes. This is a bad sign for PBXT. Brian On 3/16/2012 11:53 AM, Peter Laursen wrote:
I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this:
SELECT VERSION() #5.2.10-MariaDB
DROP TABLE IF EXISTS `tableinventory`;
CREATE TABLE `tableinventory` ( `StockNo` VARCHAR(64) NOT NULL DEFAULT '', `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb');
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC;
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC;
/* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10):
$number --------- ddd ccc bbb aaa */
Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem?
Peter (not a MP person)
On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com <mailto:grknight@scent-team.com>> wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.
Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1;
EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort
[Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort
Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> More help : https://help.launchpad.net/ListHelp
I don't know what effect REPAIR TABLE would have on PBXT. But worth a try I think. -- Peter On Fri, Mar 16, 2012 at 17:30, Brian Evans <grknight@scent-team.com> wrote:
After testing inserting the full data into a new table, it seems the fault is in PBXT somewhere. This is a very old table that is constantly accessed. I can only reproduce it in the existing table. Any attempt to create it in a new one fails to have the same results.
Also, data appears in the range version that seems to be transactional that is rolled back and not in the table by the indexes.
This is a bad sign for PBXT.
Brian
On 3/16/2012 11:53 AM, Peter Laursen wrote:
I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this:
SELECT VERSION() #5.2.10-MariaDB
DROP TABLE IF EXISTS `tableinventory`;
CREATE TABLE `tableinventory` ( `StockNo` VARCHAR(64) NOT NULL DEFAULT '', `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb');
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC;
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC;
/* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10):
$number --------- ddd ccc bbb aaa */
Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem?
Peter (not a MP person)
On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com>wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.
Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1;
EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort
[Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort
Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Last resort would be to dump and reload the table. On Fri, Mar 16, 2012 at 19:34, Peter Laursen <peter_laursen@webyog.com>wrote:
I don't know what effect REPAIR TABLE would have on PBXT. But worth a try I think.
-- Peter
On Fri, Mar 16, 2012 at 17:30, Brian Evans <grknight@scent-team.com>wrote:
After testing inserting the full data into a new table, it seems the fault is in PBXT somewhere. This is a very old table that is constantly accessed. I can only reproduce it in the existing table. Any attempt to create it in a new one fails to have the same results.
Also, data appears in the range version that seems to be transactional that is rolled back and not in the table by the indexes.
This is a bad sign for PBXT.
Brian
On 3/16/2012 11:53 AM, Peter Laursen wrote:
I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this:
SELECT VERSION() #5.2.10-MariaDB
DROP TABLE IF EXISTS `tableinventory`;
CREATE TABLE `tableinventory` ( `StockNo` VARCHAR(64) NOT NULL DEFAULT '', `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb');
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC;
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC;
/* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10):
$number --------- ddd ccc bbb aaa */
Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem?
Peter (not a MP person)
On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com>wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.
Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1;
EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort
[Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort
Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Without any technical knowledge, it seems as if the caching of PBXT is not invalidating correctly. Since we have a box almost completely dedicated to our database, we have put variables higher than their defaults: pbxt_index_cache_size=512M pbxt_record_cache_size=512M A record that was removed nearly 24hours ago still shows up in the "range, using indexes" request and is duplicating primary keys in the results of the primary key alone. I can only attribute this to the index cache because it correctly refuses to add duplicates on insert. We were concerned for the future of PBXT as discussed on its mailing list anyway. We plan on changing to XtraDB this weekend during scheduled downtime. Brian On 3/16/2012 2:38 PM, Peter Laursen wrote:
Last resort would be to dump and reload the table.
On Fri, Mar 16, 2012 at 19:34, Peter Laursen <peter_laursen@webyog.com <mailto:peter_laursen@webyog.com>> wrote:
I don't know what effect REPAIR TABLE would have on PBXT. But worth a try I think.
-- Peter
On Fri, Mar 16, 2012 at 17:30, Brian Evans <grknight@scent-team.com <mailto:grknight@scent-team.com>> wrote:
After testing inserting the full data into a new table, it seems the fault is in PBXT somewhere. This is a very old table that is constantly accessed. I can only reproduce it in the existing table. Any attempt to create it in a new one fails to have the same results.
Also, data appears in the range version that seems to be transactional that is rolled back and not in the table by the indexes.
This is a bad sign for PBXT.
Brian
On 3/16/2012 11:53 AM, Peter Laursen wrote:
I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this:
SELECT VERSION() #5.2.10-MariaDB
DROP TABLE IF EXISTS `tableinventory`;
CREATE TABLE `tableinventory` ( `StockNo` VARCHAR(64) NOT NULL DEFAULT '', `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa'); INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb');
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC;
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC;
/* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10):
$number --------- ddd ccc bbb aaa */
Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem?
Peter (not a MP person)
On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com <mailto:grknight@scent-team.com>> wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5). The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.
Here are the queries: [Incorrect Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%' ORDER BY $number DESC LIMIT 1;
EXPLAIN Result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort
[Correct Order] SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort
Simplified Table Structure: CREATE TABLE `tableinventory` ( `StockNo` varchar(64) NOT NULL DEFAULT '', `ItemDesc` varchar(96) NOT NULL DEFAULT '', PRIMARY KEY (`StockNo`) ) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> More help : https://help.launchpad.net/ListHelp
participants (2)
-
Brian Evans -
Peter Laursen