This weblog put up elaborates on the architecture and design of the transactional data dictionary that should be a part of MySQL 8.0. Some descriptions of architecture will be implemented in later types. See MySQL 8.0 facts Dictionary: heritage and motivation.

The MySQL records Dictionary Schema

The Transactional statistics Dictionary in eight.0 has a simplified and uniform managing of dictionary informationDictionary tables and equipment tables save records and meta records mandatory through the MySQL server. The dictionary tables are designed in response to the SQL typical. The “system tables” hold meta statistics or records in the mysql schema. The dictionary tables are designed to be extendible. Note that for this reason you are going to now not discover any “future searching” fields in the table definitions. Please see WL#6379 for details on the schema definition of the information dictionary tables.

Upgrade from 8.0 and forward

The facts dictionary could have a version table. This will enable computerized improve from 8.0 and forward on information dictionary tables.

I_S as views over facts Dictionary Tables

Assistance SCHEMA is now applied as views over dictionary tables, requires no added disc accesses, no advent of temporary tables, and is subject to identical managing of persona units and collations as user tables.

The ecosystem of INFORMATION_SCHEMA an API for the records Dictionary

We will put in force a uniform API for the records dictionary. This API will then be used by using server interior code, plugin carrier API code and storage engines, throughout the SE API. This could be finished in a fashion that has low intrusiveness for the server code that entry the statistics dictionary, so code may also be refactored piecewise.

The brand new information Dictionary cache

There are lots of caches in MySQL, and these caches are not always hidden at the back of APIs. a brand new cache implementation aims to be a substitute for a lot of caches, and this new cache is hidden at the back of the records dictionary API. For now, we have not changed any old caches, however more desirable them to use the brand new cache. Going ahead we will refactor the historic caches, create proper APIs for them and adapt the code of the callers. This could simplify the code of the callers, and move the entire cache logic at the back of the API.

The brand new SE API for atomic and crashsafe DDL

We do wish to provide atomic and crashsafe DDL, and this requires alterations to the MySQL server DDL code, and the InnoDB code the place dictionary tables are saved. The MySQL server code will eliminate all implicit commits and implement clear atomic semantics for DDL statements. To permit this, the tables must be kept in a transactional storage engine, and we can use InnoDB.

With the new SE API, we're in a position to implement crashsafe DDL, because the storage of the facts dictionary is InnoDB, which inherently has transactional behaviour.

Serialized Dictionary counsel and changes to the IMPORT statement

Many users have confirmed love for the capability to copy table facts and FRM data into the records directory and have the MySQL server automatically making a choice on up these tables. This ability has also been utilized for disaster healing, the place .FRM “black belts” have been able to reconstruct the meta records in the .FRM file. In MySQL 8.0 we deliver Serialized Dictionary information for dictionary objects. For InnoDB tablespaces, this assistance is appended to the tablespace, so the meta facts and data are bundled collectively. For storage engines which are not supporting this functionality, a .SDI file should be written.

Note the unidirectional of the arrow which suggests that the SDI is a copy

For InnoDB tablespaces, a tool can be supplied to examine the SDI advice. The SDI information is JSON structure. So the identical capability of editing the SDI as clients have with .FRM data for disaster healing is provided.

One “source of reality”

We can have a global dictionary. So InnoDB will populate the InnoDB dictionary cache from the world records dictionary. We will then remove the classification of issues that prior to now change into referred to as “split brain” issue.