maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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
>>
>>
>
>