MySQL Bugs: #99490: Support temporal feature

Bug #99490	Support temporal feature
Submitted:	8 May 2020 12:56	Modified:	11 May 2020 7:58
Reporter:	xiaoyu wang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	MySQL 8.0.3 Community Server	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, Temporal Database

Description:
An OCA was signed with the name of Tencent Technology (Shenzhen) Company Limited.

Contributed by TDSQL team, Tencent Inc. Members include aaronpan@tencent.com, blueseali@tencent.com, shengye@tencent.com, williamcliu@tencent.com, xiaoyuwang@tencent.com.
Thanks duyong@ruc.edu.cn, lu-wei@ruc.edu.cn, zhanhaozhao@ruc.edu.cn, zhzm@ruc.edu.cn, shuizhiyu@ruc.edu.cn from DEKE, MOE, Renmin University of China for suggestion.

Why have we developed history data management?
TDSQL is short for Tencent Distributed SQL and has been serving finance, insurance, bank, etc., for a long term. Our team thinks history data is valuable, and all the history could and should be traceable.
At present, regarding the management of historical data in application systems, common solutions are table sharding or log, etc., which have some weaknesses:
1. Complicated application development. Log analysis tools are customized while log format differs from one application to another.
2. Separate data. History data locate in many log files or tables and are not continuous physically.
3. Not real-time. Sharding by time makes it difficult to track the past in a fine granularity and log analysis costs a lot of time.
4. Complicated data management. Generally, a system has several kinds of logs, i.e., application log, storage log, analysis log, which are in different format, and it’s not easy to handle all of them.
So, we’ve developed this temporal feature to manage history data, and it’s required to be easy to use, convenient to track the past and fine-grained, so when misoperation occurs, temporal feature could help and accelerate recovery.
Besides, we see some DBMSs providing temporal features, i.e., SQL Server 2016 Temporal Tables, Oracle Flashback, MariaDB Temporal Tables, DB2 Time Travel Query, AWS QLDB.

How do we manage history data?
MySQL/InnoDB purge is modified and the data version to be cleaned up is restored into a history table, so that all data versions could be kept.
Query procedure is modified, and a history visibility judgement is implemented for temporal query.
See attachments for details.

Attachments include:
1. Design.pdf: Illustrate designs and implementations of core functions, including meta and user data management, system running, and temporal query.
2. Manual.pdf: Introduce how to manage history data with examples, including create a temporal table, temporal query, changes on system table, data and log files, etc.

To be solved:
Introducing system column DB_END_TRX_ID results in mtr failures.

Looking forward to long-term communication and contribution.

How to repeat:
See attachments.

Design and implementation

Attachment: Design.pdf (application/pdf, text), 656.84 KiB.

User manual and examples

Attachment: Manual.pdf (application/pdf, text), 653.96 KiB.

Source code apart from mysql-test

Attachment: src.patch (application/octet-stream, text), 256.92 KiB.

Some newly introduced test files

Attachment: mysql-test.patch (application/octet-stream, text), 55.59 KiB.

Design and implementation (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Design.pdf (application/pdf, text), 656.84 KiB.

User manual and examples (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Manual.pdf (application/pdf, text), 653.96 KiB.

Source code apart from mysql-test (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: src.patch (application/octet-stream, text), 256.92 KiB.

Some newly introduced test files (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-test.patch (application/octet-stream, text), 55.59 KiB.

Hello xiaoyu wang,

Thank you for the feature request and supplying patch along with the request.

regards,
Umesh

Hi 👋🏻 are there any plans to support temporal feature from SQL:2011 in MySQL? It seems the Tencent folks did an excellent contribution that hasn't received any feedback?