NPE Found when i'm using search by sk and a doubt about how to get the root page id of the secondary index?

[xiaoma20082008]

my mysql env is:

[root][db1]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

my runtime env was under intellij idea and jdk is:

openjdk version "14" 2020-03-17
OpenJDK Runtime Environment (build 14+36-1461)
OpenJDK 64-Bit Server VM (build 14+36-1461, mixed mode, sharing)

my command is here:

-i /usr/local/mysql/data/db1/tb1.ibd
-s ~/tb1.frm
-skname idx_name
-c show-all-pages

my table is here:

CREATE TABLE `tb1` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  `email` varchar(32) NOT NULL DEFAULT '',
  `address` varchar(32) NOT NULL DEFAULT '',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_email` (`email`,`id`),
  KEY `idx_age_addr` (`age`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

exception is here:

[ERROR] 2020-05-21 23:12:58,902 [main]  reader.cli.InnodbReaderBootstrap    (InnodbReaderBootstrap.java:346)    -Error occurred: Index 0 out of bounds for length 0
java.lang.IndexOutOfBoundsException: Index 0 out of bounds for length 0
    at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
    at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
    at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:248)
    at java.base/java.util.Objects.checkIndex(Objects.java:373)
    at java.base/java.util.ArrayList.get(ArrayList.java:426)
    at com.alibaba.innodb.java.reader.service.impl.RecordIterator.next(RecordIterator.java:66)
    at com.alibaba.innodb.java.reader.service.impl.RecordIterator.next(RecordIterator.java:18)
    at com.alibaba.innodb.java.reader.service.impl.DecoratedRecordIterator.next(DecoratedRecordIterator.java:50)
    at com.alibaba.innodb.java.reader.service.impl.IndexServiceImpl$2.next(IndexServiceImpl.java:381)
    at com.alibaba.innodb.java.reader.service.impl.IndexServiceImpl$2.next(IndexServiceImpl.java:377)
    at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.queryBySecondaryKey(InnodbReaderBootstrap.java:432)
    at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.main(InnodbReaderBootstrap.java:304)

and my question is how to get the root page id of a secondary index at getSkRootPageNumber Here is the code

public static long getSkRootPageNumber(TableDef tableDef, String skName, Optional<Integer> skOrdinal,
                                         Function<Long, Index> func) {
    Index rootIndex = func.apply((long) ROOT_PAGE_NUMBER);
    long fulltextKeyCount = tableDef.getSecondaryKeyMetaList().stream()
        .filter(k -> k.getType() == KeyMeta.Type.FULLTEXT_KEY).count();
    int ordinal = skOrdinal.isPresent() ? skOrdinal.get()
        : tableDef.getSecondaryKeyMetaList().stream().map(KeyMeta::getName).collect(toList()).indexOf(skName);
    long skRootPageNumber = rootIndex.getPageNumber() + fulltextKeyCount + ordinal + 1;
    log.debug("Secondary key ({}) root page number is {}, pkRootPage={}, fulltextKeyCount={}, ordinal={}",
        skName, skRootPageNumber, rootIndex.getPageNumber(), fulltextKeyCount, ordinal);
    return skRootPageNumber;
  }

but I'm Confused about long skRootPageNumber = rootIndex.getPageNumber() + fulltextKeyCount + ordinal + 1;, I think whether you should use the FsegHeader to get the secondary index's pageNo as it's documented

/**
 * As described in Page management in InnoDB space files, the index root page’s
 * FSEG header contains pointers to the file segments used by this index.
 * All other index pages’ FSEG headers are unused and zero-filled.
 * <p>
 * 20 bytes header.
 *
 * @author xu.zx
 */
@Data
public class FsegHeader {

could you explain it? Thank you !!!

[neoremind]

Good catch!

Just as the javadoc explains:

Get secondary key root page number.
Find clustered index root page and calculate sk page number based on it. Note that if table has ever been altered to add or remove indices, the secondary key root page number may be incorrect, and cause error.
MySQL will set FULLTEXT KEY, UNIQUE KEY ahead of normal KEY, but in SHOW CREATE TABLE command, the FULLTEXT KEY goes to the end, so here the workaround is to add the delta of fulltext key count.
More standard way would be to look up root page number by: SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES; before 5.7 or SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES; after 8.0

Currently sk root page number will be calculated per above. To locate the exact page number, the system tablespace is the where to find, but now the framework is not able to read system table to retrieve the information. So that's why the class name is Workaround. PR is welcomed.

BTW, The command to query by sk:

java -jar /Users/xu/IdeaProjects/innodb-java-reader-ali-github/innodb-java-reader-cli/target/innodb-java-reader-cli.jar \
  -ibd-file-path /usr/local/mysql/data/test/issue.ibd \
  -create-table-sql-file-path issue.sql \
 -showheader  -c query-by-sk  -args ">=;0;<;0" -skname idx_name

[xiaoma20082008]

Got it, Thank you!!! @neoremind

[neoremind]

@xiaoma20082008 I have delivered a new release, you can specify secondary key root page number if necessary, please refer to https://github.com/alibaba/innodb-java-reader/blob/master/docs/FAQ.md#1-how-to-specify-secondary-key-root-page-number. Any questions, feel free to let me know.

[xiaoma20082008]

LGTM! Thank you!