maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

Last resort would be to dump and reload the table.

On Fri, Mar 16, 2012 at 19:34, Peter Laursen <peter_laursen@xxxxxxxxxx>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@xxxxxxxxxxxxxx>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@xxxxxxxxxxxxxx>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@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>