[Proposal]: IBD file offline examiner

[jlopusza-oracle]

Pre-flight Checklist

• I have searched existing GitHub issues and did not find a duplicate proposal.
• I have removed or redacted sensitive information.

Primary Contact Name

Jakub Łopuszański

Primary Contact Email

jakub.lopuszanski@oracle.com

Company / Organization

Oracle

Role

Software Engineer

Additional Authors / Contributors

No response

Component

Other

Target Release (Optional)

26.x

Roadmap Section

Performance & Observability

Related Issues / Pull Requests / References (Optional)

No response

Executive Summary

Why? Purpose

Developers and advanced users might need to inspect contents of tablespace files:

• to do post-mortem/root cause analysis of a bug
• to educate themselves about how something really works

As of today, there is no official tool to do it, and our developers rely either on ad-hoc printfs, debugger scripts, or some external tools like

• https://github.com/jeremycole/innodb_ruby
• https://github.com/KernelMaker/ibdNinja
• hex editors

These existing approaches have several downsides, like:

• not easy to deploy on the server, in particular because:
• not clear if we can trust it, and its dependencies,
• as written by hobbyst who might lack domain knowledge privy to our developers, some edge-case handling or new features support might be lacking or wrong

What? Solution

Lunette is meant to be a separate tool developed by InnoDB team, distributed as part of Community edition, as Python and JS scripts, that one can simply copy to the server and start using without any dependencies or installation.

It consists of:
1. Python server.py

A minimal Python "page server", which uses only the standard library which comes with Python 3.6+, and can be simply run with one-liner on the machine which has *.ibd files, to serve their pages.

$ python server.py --tablespace-path '/data/*.ibd' --tablespace-path '/data/*/*.ibd' --tablespace-path '/data/undo_*' --tablespace-path /data/ibdata1 --port 8080

This server also serves a simple static html+js+css files, which form the client application:

2. Browser App
A client application (hosted by the server) which can be opened in the browser to navigate and view the tablespace files

User / Developer Stories

• As a DBA who is about to file a bug to InnoDB about recently seen crash, I'd like to first better understand the details of what has happened, so that my report can be as precise as possible, without having to hand over the whole sensitive database file
• As a new hire in InnoDB Team, I'd like to see for myself how INodes of a tablespace are laid out in the header pages of a tablespace, to double-check my understanding of this area
• As a contributor who wants to propose an optimization to InnoDB index page layout, I'd like to see how the header at offset 18 is used in practice, so I can perhaps squeeze in one more bit into it
• As an InnoDB developer investigating a mysterious crash during DDL, I want to inspect the link structure of records on page 7 of space 13, to see if it perhaps had cycles, so that I can check my hypothesis about the root cause of the crash

Proposed Scope

• Listing the tablespaces available for inspection
• Listing pages within tables and their allocation state
• Listing known header fields in a page, and their values
• Listing records of known type, and showing their values/content
• Visualizing relations between fields, pages, (such as parent-child relation in B-tree)
• Decoding the page content in hex and when possible the fragments which are numeric/string in their human readable form
• Handling of non-compressed, non-encrypted tablespace files
• Perhaps handling of Undo Spaces, too

Out of Scope / Future Work

• parsing of redo logs
• inspecting in-memory/BP/live state
• handling each and every space type, page type and record type - these could be added incrementally once the most important are in place in v1
• handling compression and encryption - these could perhaps be added later

References

No response

Functional Requirements

• The tool must understand enough of the subset of InnoDB space, page and record formats, to be useful in typical cases (no need to support in place alter, compression, encryption, all of the Undo Log formats etc in v1)
• The tool should support URLs which link to specific byte range of a page in a tablespace, so that native browser navigation, history, bookmarks, sharing, just works
• The tool should focus on visual, human experience, not ascii-art/text/json (which might be added later as plugin)
• The tool must handle cases where the tablespace files are corrupted, i.e. the parser should not assume the file is intact, not torn, properly structured. This is because one of the usecases is to analyze bugs/crashes resulting from, or in, corrupted state

Non-functional Requirements

• The tool must have no external dependencies to install, other than Python on the server side, and a modern browser on the client side. In particular we don't want to rely on any third party packages, libraries etc. which would have to be installed on the server because it introduces risks of various kinds
• The tool must let the user control which of the tablespaces are exposed to the client
• The server-side code has to be short and simple, so that anyone can read it in an afternoon to review what it is doing
• The client-side code ideally should use just vanilla JS, so that it is easy for anyone to contribute to it and we don't have to keep up with latest and hottest JS framework
• The client-side code should be easy to understand, and documented with references to actual InnoDB source, so that things like layout of records expected by JS parser can be double-checked by reading the relevant fragment of C++ code in InnoDB

Impact Areas

• SQL syntax or statements
• Configuration options or system variables
• Command-line options or utilities
• User-visible behavior
• Observability
• Security or privilege model
• Protocol or replication behavior
• Upgrade / downgrade compatibility
• Performance or resource usage
• Files, persistence, or metadata formats
• APIs or internal interfaces
• Testing or QA coverage needs

Summary of the Approach

A minimalistic Python server which uses just the standard Python library, to serve pages of tablespaces selected by the user and a static SPA written in html+css+js.

The SPA should be modular, with separate parsers for each page type, a module for understanding SDI, and a small DSL in which a parser can describe the AST of information found in the page, so it can be properly visualized by the UI. In particular, a parser for, say, "Blob Page" should not have to reinvent UI controls, like collapsible tree of properties, or drawing an arrow link to next sibling, or how to visualize utf8 string in one of the records - instead the parser should just describe what it found inside the page in a JSON object which uses standardized "vocabulary" of things like nested regions, key-value maps, encoding type info etc. which the App can then interpret to construct necessary UI elements on the fly.

User Interface

The idea is that the user just downloads/unpacks, the tool on the server/machine which contains the ibd files to inspect, then does something like:

$ python server.py --tablespace-path '/data/*.ibd' --tablespace-path '/data/*/*.ibd' --tablespace-path '/data/undo_*' --tablespace-path /data/ibdata1 --port 8080

and this will start a very small Python server, which serves an SPA at 8080, to which the user can connect.
It should, by default do so on loopback interface, so one can't connect from the outside (for security reasons).
A user can point the browser to localhost:8080 to inspect the file, if the files and server.py are on their own laptop, or, use an SSH tunnel to authenticate and access it in a secure way, if the data is on a remote machine.

The webpage would have elements like:

• list of tablespaces
• list of pages in the selected space
• list of records, header fields etc. of selected page
• a diagram showing a subset of the graph of relations between pages/fields
• a smart hex-viewer showing the content of the page

Configuration / Knobs

No response

Observability

No response

User Procedure

The tool would be a set of static files (python, js, css, html) probably in the utitilities/ folder of the Community edition of MySQL. As such one can either clone, or download it, and just put them on the machine which has ibd file(s) to inspect. One could first move the relevant ibd files to a dedicated machine or own laptop.
The user would then start the server as explained above, and navigate their browser to it.

Security Considerations

The Python server is (intentionally, to keep it simple and reviewable) not smart enough to understand any permissions within the db itself (such us those created via ACL SQL statements like CREATE USER, GRANT... etc.). Also, for simplicity it doesn't come with any SSL, HTTPS, authorization/credentials checking. This can be added in later versions, though. It MUST however narrow down the HTTP socket it opens to accept localhost only. It is then a matter of controlling access to this port.
Exposing all the pages of a tablespace is of course exposing the whole db.
Note however, that to use this tool, one has to already be able to execute python code on the machine which has the ibd files on it, and to have read access to them, so the tool itself does not grant more privileges to the person using it than they had.
However, if there are more users of the system, which can also access the port opened by the server, then of course that gives them access, too.

Compatibility and Behavior Changes

The tool will probably be kept in the same repository as the InnoDB code for Community edition, in utitilites/ folder, which gives us chance to make sure it is in sync with the current formats, layouts and semantics used by corresponding version of InnoDB. The tool doesn't have to understand files created by future or older versions of InnoDB, as one can always download older or newer version of the tool. However, if easy to achieve backward compatibility without sacrificing clarity of the code (which is important to ensure it really does what it should, can be reviewed, and extended by contributions) we could try to be nice and accept old format here and there just to make life easier.

Block Diagram

No response

Interface Specification

No response

Proposed Implementation Plan

No response

QA Notes

No response

[jlopusza-oracle]

Lunette-Community-Summit-Oracle.pdf
Lunette-Community-Summit-Oracle.pptx

[jfg956]

It consists of:

1. Python server.py
   [...]
2. Browser App

Above is great for usage by a "human", but dbas often write scripts: is there a way to use lunette in a fully automated way (running curl on the server, and piping to jq for querying json output) ?

IMHO, the fact that this is built as a server maker automation usage more complex than it should, and unless this is addressed, DBAs will use other tools to script. I would prefer to see this implemented as a shell tool, possibly wrapped in a server for browser usage. I could change my mind if the server support easy automation.

[HugoWenTD]

Great presentation on contributor summit! A couple of thoughts:

The tool must handle cases where the tablespace files are corrupted

• It'd be great if the tool can point out what are the corrupted pages/data when loading a corrupted idb file.
• Comparing structures of two similar ibd files may also be a helpful feature.

[jlopusza-oracle]

Please note, that a single tool can't be great for every use-case.
That being said, we will try to make it a great base for extending it to any cool direction you want, but then I think it will inevitably become a fork unless we come up with some "plugin infrastructure".

The main use-cases for this tool, listed in OP, are all human-centric, revolving around "As a human I want to make sense of this inscrutable binary file".
This means GUI, UX, clarity of presentation, responsiveness are crucial for Lunette.
The architecture intentionally avoids any full-scans. The server only reads few bytes from 0th page of each space, and the client only asks for bytes of the individual page(s) the user chooses to look at. This makes it super-fast and lightweight, while also running in very secure env (js in browser, so practically a sandbox). (I am not saying V8 or other JS engines are incapable of doing fast computations or parallel IO nowadays. The problem is a bit different: the server code has to be as simple/short as possible so you can trust to deploy it even on a production server. A full blown parallel parser in js, would not be simple to review, and will probably require a ton of dependencies, and far from clear how to run it in a sandbox. The current architecture means that any non-trivial logic lives in your own browser, and can only deal with the narrow set of data that server gave you. Trying to fit this execution model to "let's analyse full ibd file" is possible in theory, but would force you to send whole ibd file to the client, which in some scenarios is impractical or disallowed).

If you need a tool which produces text output for scripting purposes, then there are already great options:

• (innodb_ruby by Jeremy Cole
• ibdNinjaby Song Zhao

If you want something coming directly from Oracle/MySQL, then for now all we have is innochecksum, which I think is a great starting point if one wants to contribute, because at least it is written in C++ and can easily reuse existing code from InnoDB to the parsing same way it does.

I can't promise anything, but for my own purposes I use a tool which you feed the bytes of tablespace (or a fragment of it using dd) and it outputs decoded headers and info in a form which is at the same time a JSON stream (so easy to work with with jq), and columnar (so easy to work with awk, join, or sort etc.). Perhaps we could work on opensourceing it too?

But for Lunette, I am mostly interested in empowering people to stay on top of InnoDB's complexity.

Scripting, AI agents, CI, are not the main use-case.
That being said, it is rather trivial to take JS parsers from Lunette project and plug them into an adhoc vibe-coded script which does something non-trivial for you, like, say, checks if records on a given page form a cycle instead of a list.
This is not a goal in itself, but a by-product of a more important design principle, that Lunette tries to have modular architecture with clear interfaces, so that people can read its code to understand how InnoDB works - for example, if I want to understand how Blob pages are linked together, my choice are to either search the web, or dive into spaghetti C++ code of InnoDB, or read the 64 lines of BlobPageParser.js and immediately understand everything about the layout and operation. So this clarity of code, will definitely be useful to building anything on top of it, and community is more than welcome to do that, once we release Lunette as open source.