6.15.4. Making Schema Changes

6.15.4. Making Schema Changes
Prev	^Up	6.15. Performing Database or OS Maintenance	Next

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:

If the DDL adds or removes columns, then enable the colnames and dropcolumn filters
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
Perform schema changes following the process summarised in the table below
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.

Prev	Up	Next
6.15.3. Performing Maintenance on an Entire Dataservice	^Level	6.15.5. Upgrading or Updating your JVM

Continuent Documentation