Bug #103837 Suggest to add materialized view log for innodb tables
Submitted: 28 May 2021 9:32 Modified: 28 May 2021 9:53
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Logging Severity:S4 (Feature request)
Version:All, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[28 May 2021 9:32] Fangxin Flou
Description:
The oracle database can create materialized view log for a table to capture the data change, I think MySQL can also have this features.

The materialized view log is an extra table which record the row changed as following.

MLOG$_<base_table_name> (seqno bigint auto_increment, dmltype {I|U|D}, primary columns of the base table).

For insert operation, insert into MLOG$ table by values (seqno, 'I', primary key value list);

For delete operation, insert into MLOG$ table by values (seqno, 'D', primary key values list);

For non-primary key update, insert into MLOG$ table by values (seqno, 'U', primary key values list);

For Primary key update, insert into MLOG$ table with values (seqno, 'D', old primary key values list), (seqno, 'I', new primary key values list);

We can implement this by triggers, but it's not so efficient. This is a possible solution for changed data capture without binary logs.

How to repeat:
N/A

Suggested fix:
N/A
[28 May 2021 9:53] MySQL Verification Team
Hello Fangxin Flou,

Thank you for the feature request!

regards,
Umesh