alibaba / innodb-java-reader Goto Github PK

When I want to integrate InnodbReaderBootstrap, I should call main method, and it wan't return anything wheather success or failed

你好，
sql: CREATE TABLE binance_wallet_address(id int(11) NOT NULL,address varchar(80),is_hot_cold int(2),PRIMARY KEY (id),KEY index_address (address)) ENGINE=InnoDB;"
ibdfile :binance_wallet_address.ibd

出现下面的错误， ibd 文件与 sql 语句是有效的。唯一的不同点，是 ibd 文件不是创建在 datadir 中的，通过 isl 连接
Index header is unreadable, only new-style compact page format is supported, please make sure the file is a valid InnoDB data file, page=Page#676291(header=FilHeader(checksum=0, pageNumber=0, prevPage=0, nextPage=0, lastModifiedLsn=0, pageType=ALLOCATED, flushLsn=0, spaceId=0)), index.header = IndexHeader(numOfDirSlots=0, heapTopPosition=0, numOfHeapRecords=0, format=REDUNDANT, firstGarbageRecOffset=0, garbageSpace=0, lastInsertPos=0, pageDirection=null, numOfInsertsInPageDirection=0, numOfRecs=0, maxTrxId=0, pageLevel=0, indexId=0)

ibd 是 mysql 8.0

[ERROR] 2022-08-22 13:17:07,777 [main] reader.cli.InnodbReaderBootstrap (InnodbReaderBootstrap.java:397) -Error occurred: Table definition not found for table tb_ec_system_user
com.alibaba.innodb.java.reader.exception.ReaderException: Table definition not found for table tb_ec_system_user
at com.alibaba.innodb.java.reader.TableReaderFactory.createTableReader(TableReaderFactory.java:97)
at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.createTableReader(InnodbReaderBootstrap.java:689)
at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.queryAll(InnodbReaderBootstrap.java:412)
at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.run(InnodbReaderBootstrap.java:322)
at com.alibaba.innodb.java.reader.cli.InnodbReaderBootstrap.main(InnodbReaderBootstrap.java:129)
JVM does not support unmapping memory-mapped files.

When dumping table data to a file field values can't be quoted. This becomes an issue when a string-like, e.g., VARCHAR, value contains chars that are used as a delimiter. When a CSV reader reads such a value it splits it into two or more false columns. Therefore a CSV reader encounters an unexpectedly higher count of columns.

public static void main(String[] args) {
String createTableSql = "CREATE TABLE tb11\n"
+ "(id int(11) NOT NULL ,\n"
+ "a bigint(20) NOT NULL,\n"
+ "b varchar(64) NOT NULL,\n"
+ "PRIMARY KEY (id),\n"
+ "KEY key_a (a))\n"
+ "ENGINE=InnoDB;";
String ibdFilePath = "/Users/zhangfeng/Desktop/dbtest/tb11.ibd";
try (TableReader reader = new TableReaderImpl(ibdFilePath, createTableSql)) {
reader.open();
List recordList = reader.queryByPageNumber(3);
for (GenericRecord record : recordList) {
Object[] values = record.getValues();
System.out.println(Arrays.asList(values));
assert record.getPrimaryKey() == record.get("id");
RecordHeader header = record.getHeader();
System.out.println("id=" + record.get("id") + ",a=" + record.get("a")+",b=" + record.get("b") +",flag="+header.getInfoFlag());
if (!record.isLeafRecord()) {
System.out.println(record.getChildPageNumber());
}
}

}

无法获取删除的记录，以及delete-marked标记

Hi, I have the following create table syntax -
CREATE TABLE innodb_java_reader_test1 ( number VARCHAR(16) NOT NULL, testnumber VARCHAR(16) NOT NULL, UNIQUE KEY (number, testnumber) );
The above syntax is stored in "~/sathishFiles/innodb_java_reader_test1.sql" file as well for use in the cli command below.

And when it is created in MySQL, it looks like this -
CREATE TABLE innodb_java_reader_test1 ( number varchar(16) NOT NULL, testnumber varchar(16) NOT NULL, UNIQUE KEY number (number,testnumber) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As we can see, the unique key name will default to the first column name.

Now when I do the
java -jar innodb-java-reader-cli/target/innodb-java-reader-cli.jar -ibd-file-path /usr/local/var/mysql/innodb_java_reader/innodb_java_reader_test1.ibd -create-table-sql-file-path ~/sathishFiles/innodb_java_reader_test1.sql -c query-all -o ~/sathishFiles/innodb_java_reader_test1

It fails with the following exception -
Caused by: java.lang.IllegalArgumentException: Key name should not be empty
It's failing because of the missing key name for the unique key in the create table syntax

As MySQL does create a name for the key if we don't specify it explicitly, can't we do the same at our end ?
So if indexName is null, then we just use the firstColumnName as indexName.
With this change, the command was able to dump the records from the table successfully.

Let me know if this makes sense.

I know this question may out of scope of this project. But I just wanna know is there possible to construct Innodb file using Java in parallel? If true, does that means it's possible to leverage some parallel engine like Spark to implement Innodb bulk insert rather than insert data using JDBC?

Mysql 8, sdi page only parse the FileHeader(InnerPage)?

CREATE TABLE md_blogs (
blog_id int(11) NOT NULL AUTO_INCREMENT,
blog_title longtext NOT NULL,
blog_identify longtext NOT NULL,
order_index int(11) NOT NULL,
member_id int(11) NOT NULL,
blog_type int(11) NOT NULL,
document_id int(11) NOT NULL,
blog_excerpt longtext NOT NULL,
blog_content longtext,
blog_release longtext,
blog_status longtext NOT NULL,
password longtext NOT NULL,
modify_time longtext NOT NULL,
modify_at int(11) NOT NULL,
create_time longtext NOT NULL,
version int(11) NOT NULL,
PRIMARY KEY (blog_id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

测试数据

INSERT INTO md_blogs VALUES (1,'使用云呼前检测网络','post-1702957479487491200',2,2,0,0,'使用云呼前检测网络\n#### 坐席网络要求\n1.每个坐席的网络带宽要求\n语音需要网络带宽：100Kbps\n\n业务需要网络带宽：100Kbps\n\n网络延时 < 100ms\n\n网络丢包率< 0.5%\n\n2.允许坐席访问如下公网服务和端口\n对于对互联网访问有控制的坐席，至少需要运行访问如下公网服务：\n\nTCP协议端口\n\n主服务入口，HTTPS协议，端口443\n\n相关服务入口，HTTPS协议，>端口443\n\n语音服务，WebSocket协议 ，端口80、9443、8098\n\nSIP和坐席控制服务，TCP协议，端口443\n\nUDP协议 开放端口：40000-50000\n\n如何测试和评估坐席与云服务之间的网络质量是否满足要求？\n坐席和云端呼叫中心的网络质
量要能够保障坐席正常接听和拨打电话，并进行相关也操作。每个电话建议100Kbps的带宽，额外100Kbps的带宽用于业务操作和查询。\n','使用云呼前检测网络\n#### 坐席网络要求\n1.每个坐席的网络带宽要求\n语音需要网络带宽：100Kbps\n\n业务需要网络带宽：100Kbps\n\n网络延时 < 100ms\n\n网络丢包率< 0.5%\n\n2.允许坐席访问如下公网服务和端口\n对于对互联网访问有控制的坐席，至少需要运行访问如下公网服务：\n\nTCP协议端口\n\n主服务入口，HTTPS协议，端口
443\n\n相关服务入口，HTTPS协议，端口443\n\n语音服务，WebSocket协议 ，端口80、9443、8098\n\nSIP和坐席控制服务，TCP协议，端口443\n\nUDP协议 开放端口：40000-50000\n\n如何测试和评估坐席与云服务之间的网络质量是否满足要
求？\n坐席和云端呼叫中心的网络质量要能够保障坐席正常接听和拨打电话，并进行相关也操作。每个电话建议100Kbps的带宽，额外100Kbps的带宽用于业务操作和查询。\n\n测试前的准备\n安装iperf3，链接：https://iperf.fr/iperf-download.php\n\n确保测试电脑和坐席上外网的防火墙60001-60010端口都是开放状态。\n\n在坐席网络执行命令：\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 300 –logfile ./5min.txt\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 600 –logfile ./10min.txt\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 900 –logfile ./15min.txt\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 1200 –logfile ./20min.txt\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 1500 –logfile ./25min.txt\n\niperf3 -c 139.196.165.160 -u -p 60001 -b 5.0M -V -t 1800 –logfile ./30min.txt。\n\n上述命令为530分钟的测试命令，您可跟自己时间情况进行选
择，建议测试持续10-20分钟。\n\n命令的含义：测试坐席与云呼之间以5M负荷情况下的网络质量,当测试完成后将生成的检侧文件发送给云呼叫中心-售后技术支持（钉钉）即可。\n\n命令参数说明\n\na. 139.196.165.160 是云呼的坐席网络>质量测试服务器。\n\nb. 60001 是云呼的坐席网络质量测试端口，可选用端口包括：60001～60010。当提示显示连接错误，替换一下端口即可。\n\nc. 5M的是坐席网络与云呼之间的测试负载，这里根据你的坐席数量来设定，每个坐席200Kbps（100给语音，100给业务），这里5M正常可以支撑25个坐席的同时访问。\n\nd. 600是测试时间600S,即10min。\n\ne. iperf3.log是将执行结果导入iperf3.log文件中。\n\n查看和解读测试结果： 看延时、丢包率是否满足要求。\n\n网络延>时 < 100ms\n\n网络丢包率< 0.5%\n\n\n\n','<div class="whole-article-wrap"><p class="line">使用云呼前检测网络

解析binlog的时候

在项目中，我找到都是叶子都在根页的例子，没有找到分页的例子。请问项目有提供这样的例子吗？

这个应该是分页之后执行的逻辑吧

想请问一下dynamic格式的怎么进行处理的~不胜感激

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