WL#2964: System tables for table and database definitions

Affects: WorkLog-3.4   —   Status: Un-Assigned   —   Priority: Medium


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.


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
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.


- 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.