6.15.4. Making Schema Changes

Similar to the maintenance procedure, schema changes to an underlying dataserver may need to be performed on dataservers that are not part of an active dataservice. Although many schema changes, such as the addition, removal or modification of an existing table definition will be correctly replicated to Replicas, other operations, such as creating new indexes, adding/removing columns or migrating table data between table definitions, is best performed individually on each dataserver while it has been temporarily taken out of the dataservice.

As with all maintenance operations it is advisable to have fully tested your DDL in a staging environment. In some cases, the imapct of DDL change is minimal and therefore can safely applied to the Primary node and allowing the change to be replicated down to the Replicas.

In situations where the overhead of the DDL change would cause an outage to your application through table locking, use the rolling maintenance procedure below which is specific for DDL changes.

The basic process comprises of a number of steps, these are as follows:

  1. If the DDL adds or removes columns, then enable the colnames and dropcolumn filters

  2. If the DDL adds or removes tables, and you do not want to simply apply to the Primary and allow replication to handle it, then enable the replicate filters

  3. Perform schema changes following the process summarised in the table below

  4. Optionally, remove the filters enabled in the first step

Note

The first two steps listed above are optional for Multi-Site/Active-Active topologies, the process will still work without this step however this may cause cross-site replication to go into an error state until the DDL changes have been applied to all hosts.

Therefore, within Multi-Site/Active-Active environments, instead of using the colnames and dropcolumn filters mentioned above, you may choose to temporarily stop the cross site replicators. Doing so will reduce the risk of errors in the replication flow whilst the maintenance is in progress, however you will introduce latency between clusters for the duration of the time the replicators are offline.

For Composite Active/Active topologies, it is not possible to isolate the cross-site replictions in the same way, therefore these steps are strongly advised as the resulting error state of the cross-site replicator may prevent a successful switch between Primary and Replica nodes.

Enable filters for column changes

The use of the colnames and dropcolumn filters allow you to make changes to the structure of tables, without impacting the flow of replication.

Important

During these schema changes, and whilst the filters are in place, applications MUST be forwards and backwards compatible, but MUST NOT alter data within any columns that are filtered out from replication until after the process has been completed on all hosts, and the filters disabled. Data changes to filtered columns will cause data drift and inconsistencies, resulting in potentially unexpected behaviour

  • To enable the filters, first create a file called schemachange.json in a directory accessible by the OS user that the software is running as.

    Typically, this will be the tungsten user and the default location for this file will be /opt/continuent/share

    The file needs to contain a JSON block outlining ALL the columns being added and removed from all tables affected by the changes.

    In the example below, we are removing the column, operator_code and adding operator_desc to the system_operators table and adding the column action_date to the system_actions table:

    [
        {
          "schema": "ops",
          "table": "system_operators",
          "columns": ["operator_code","operator_desc"]
        },
        {
          "schema": "ops",
          "table": "system_actions",
          "columns": ["action_date"]
        }  
      ]
  • Place your cluster into maintenance mode

    shell> cctrl
    cctrl> set policy maintenance

    Note

    If running a Composite Active/Passive or Composite Active/Active topology, issue the command at the top global level to place all clusters in maintenance, or execute individually within each cluster
  • Next, enable the filters within your configuration by adding the following two parameters to the tungsten.ini (if running in INI method) to the [defaults] section on EVERY cluster node:

    svc-extractor-filters=colnames,dropcolumn
    property=replicator.filter.dropcolumn.definitionsFile=/opt/continuent/share/schemachange.json

    Followed by tpm update to apply the changes:

  • Or, if running as a staging install:

    shell> cd staging_dir
    shell> tools/tpm update alpha \
      --svc-extractor-filters=colnames,dropcolumn \
      --property=replicator.filter.dropcolumn.definitionsFile=/opt/continuent/share/schemachange.json
  • Monitor replication to ensure there are no errors before continuing

Enable filters for adding/removing tables

The optional use of the replicate filter allows you to add and remove tables without impacting the flow of replication.

Important

During these schema changes, and whilst the filter is in place, applications MUST be forwards and backwards compatible, but MUST NOT modify data in any new tables until after the process has been completed on all hosts, and the filters disabled. Data changes to filtered tables will cause data drift and inconsistencies, resulting in potentially unexpected behaviour.

  • Place your cluster into maintenance mode.

    shell> cctrl
    cctrl> set policy maintenance

    Note

    If running a Composite Active/Active or Composite Active/Passive topology, issue the command at the top global level to place all clusters in maintenance, or individually within each cluster.
  • Next, enable the filters within your configuration by adding the following two parameters to the tungsten.ini (if running in INI method) to the [defaults] section on EVERY cluster node.

    In this example we plan to ADD the table system_actions and REMOVE the table system_operations, both within the ops schema:

    svc-extractor-filters=replicate
    property=replicator.filter.replicate.ignore=ops.system_actions,ops.system_operations

    Followed by tpm update to apply the changes

  • Or, if running as a staging install:

    shell> cd staging_dir
    shell> tools/tpm update alpha \
      --svc-extractor-filters=replicate \
      --property=replicator.filter.replicate.ignore=ops.system_actions,ops.system_operations
  • Monitor replication to ensure there are no errors before continuing.

Apply DDL Changes

  • Follow the steps outlined in the table below to make the DDL changes to all nodes, in all clusters.

  • If filtering columns, once all the changes have been complete, edit the schemachange.json to contain an empty document:

    shell> echo "[]" > /opt/continuent/share/schemachange.json

    Then, restart the replicators:

    shell> replicator restart
  • If filtering tables, repeat the process of adding the replicate filter removing any tables from the ignore parameter that you have ADDED to your database.

  • You can optionally fully remove the filters if you wish by removing the entries from the configuration and re-running tpm update however it is also perfectly fine to leave them in place. There is a potentially small CPU overhead in very busy clusters by having the filters in place, but otherwise should not have any impact.

    It is advisable to monitor the system usage and make the decision based on your own business needs.

    Note

    Leaving the colnames filter in place will increase the size of THL on disk, therefore if disk space is limited in your environment, it would be avisable to remove these filters.

The following method assumes a schema update on the entire dataservice by modifying the schema on the Replicas first. The schema shows three datasources being updated in sequence, Replicas first, then the Primary.

Step Description Command host1 host2 host3
1 Initial state   Primary Replica Replica
2 Set the Replica host2 offline trepctl -host host2 offline Primary Offline Replica
3 Connect to dataserver for host2 and update schema

set sql_log_bin=0;

run ddl statements

Primary Offline Replica
4 Set the Replica online trepctl -host host2 online Primary Replica Replica
5 Ensure the Replica (host2) has caught up trepctl -host host2 status Primary Replica Replica
6 Set the Replica host3 offline trepctl -host host3 offline Primary Replica Offline
7 Connect to dataserver for host3 and update schema

set sql_log_bin=0;

run ddl statements

Primary Replica Offline
8 Set the Replica (host3) online trepctl -host host3 online Primary Replica Replica
9 Ensure the Replica (host3) has caught up trepctl -host host3 status Primary Replica Replica
10 Switch Primary to host2 See Section 6.5, “Switching Primary Hosts” Replica Primary Replica
11 Set the Replica host1 offline trepctl -host host1 offline Offline Primary Replica
12 Connect to dataserver for host1 and update schema

set sql_log_bin=0;

run ddl statements

Offline Primary Replica
13 Set the Replica host1 online trepctl -host host1 online Replica Primary Replica
14 Ensure the Replica (host1) has caught up trepctl -host host1 status Primary Replica Replica
15 Switch Primary back to host1 See Section 6.5, “Switching Primary Hosts” Primary Replica Replica

Note

With any schema change to a database, the database performance should be monitored to ensure that the change is not affecting the overall dataservice performance.