WL#2964: System tables for table and database definitions
Affects: WorkLog-3.4 — Status: Un-Assigned
Summary ~~~~~~~ Add system tables for table and db information Decisions/Options regarding this WL ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2005-11-05: Monty says this needs to be coordinated with Maria etc. Description ~~~~~~~~~~~ This is the "correct" way to replicate meta-data. Currently, the DDL information is replicated statement-based event when row-based logging is used. This causes several problems: 1. Statements that are hybrids of DDL and DML statements, such as CREATE ... SELECT ..., cause problems when being replicated since they can neither be replicated statement-based nor row-based. Special solutions have been developed to handle that situation. 2. There is a need to generate a table map event to map database name + table name to an integer. This is needed to avoid having to duplicate the database name and table name in each event containing rows. To get this scheme to work correctly in the presence of queries, special solutions have been developed. Still, the number of table map event generated is significant for statements change few rows. The worklog involves: 1. Definition of tables to hold meta-data 2. For each DDL statement, code needs to be added to enter data into tables. 3. Replacing the .frm files and instead use system tables to hold the table and database data. The advantages of this solution is: 1. Since we can replicate information about table-id together with other table data, many table maps can be removed from the binary log. The table map event is still needed for boot-strapping purposes, since these events serves to reference a table by its name. 2. No need to re-parse statements on the slave side, reducing the lag of the slave. Risks ~~~~~ - Multi-source, with masters having different ideas about what id to use. For example, the same table-id could be used at two different masters to denote different tables. This can be handled by introducing an extra table at the slave side, mapping (master,table-id) to table-id, e.g.:: CREATE TABLE mysql.table_id(master CHAR(20) NOT NULL, master_tid INT NOT NULL, my_tid INT NOT NULL, PRIMARY KEY (master,master_tid)); Lars thinks that we have the same problem with named masters, so he thinks it should not be any big issue.
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.