From: Lars-Erik Bjørk
Date: May 4 2009 12:40pm
Subject: Corrupt indexes?
List-Archive: http://lists.mysql.com/falcon/711
Message-Id: <49FEE22F.6030107@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 7BIT

Hi all!

Sometimes we get too many rows returned when doing LIMIT queries. F.ex 
my previous RQG run says:

SELECT * FROM `E` WHERE `int_key` < 5 returns different result when 
executed with predicate 'ORDER BY `int_key` LIMIT 1073741824' (1000 vs. 
1459 rows)

The tables has 1000 rows.

In IndexWalker::getValidatedRecord, I have added debug printouts, the 
recordNumber and the pageNumber whenever we return a  valid record. For 
the previous example, there are plenty of index pages used, but only the 
three first of them contains valid nodes.

The three first pages are page 154, 136, 213 (in that order).

All the nodes returned as valid from pages 154 and 213 match with the 
duplicate rows returned. Page 136 contains valid nodes for all 1000 
records in the table.

This leads me to believe that our index is fishy. Some of the nodes have 
two entries in the index, and these two entries are not even adjacent ...

I assume that if this is the case, we would not notice it during a 
regular index search (not using LIMIT), because this would only result 
in the same bit in the bitmap being set twice, during the scan phase. I 
tried to test this by adding the following to IndexRootPage::scanIndex

            ASSERT (!bitmap->isSet(number));  // Added this
            bitmap->set (number);

And this asserted during the test.

Does this mean anything? Or are there valid conditions where this assert 
may happen? I think this looks suspect.

Best,
Lars-Erik